PostgreSQL Training Course 

- for Developers: 2-5 days

- for Administrators: 2-5 days

Note: this outline is our proposal, but the training can be tailored to your specific requirements upon prior request ahead of the proposed course date.

Why learn PostgreSQL?

PostgreSQL is the most advanced open-source database system, which is also the first database management system that implements a multi-version concurrency control (MVCC) feature. It is an enterprise-class open-source database management system and is trusted by enterprises because of its reliability, data integrity, and correctness. Using the right DBMS allows businesses to control access to a database, write data, run queries, and perform any other tasks related to database management in the most effective manner. PostgreSQL’s implementation of MVCC ensures the atomicity, consistency, isolation, and durability of its transactions, also known as ACID compliance. 

​​Course details

The agenda covers both fundamentals and advanced topics.

The final training outline will be designed depending on your particular requirements.

The practical exercises constitute a big part of the course time, besides demonstrations and theoretical presentations. Discussions and questions can be asked throughout the course.

 

Course Outline - Developers

Introduction to PostgreSQL Server

 

The Procedural Language and Dynamic SQL

 

Designing and Implementing Databases and Tables

 

Ensuring Data Integrity Through Constraints and Rules

 

Working with Indexes and Full Text Search

 

Partitioning

 

Working with Triggers, Rules and Views

 

Working with Transactions & Error Handling

 

Query Optimization

 

Foreign Data Wrappers

 

Dealing with Large Objects

 

Communicating with PostgreSQL Using LibPQ

 

Extensions

Course Outline - Administrators

Day 1

 

Introduction to PostgreSQL Server and Advanced Features

 

Postgres Client Server Architecture

Getting Started

  • Installing the PostgreSQL Server

  • Setting Environment Variables

  • Creating a Cluster

  • Running Server

 

Configuring of PostgreSQL Server

  • Connection Settings

  • Security and Authentication

  • Resource Settings

  • WAL

  • Error Reporting and Logging

  • Autovacuum

  • Runtime Statistics, etc.

 

Server Control

  • Postgres host-based access configuration

 

Day 2

 

Client and Tools

  • Introduction To PSQL

    • Commands and Parameters

  • Using graphical administration tools- pgAdmin

 

Creating and Managing Databases

  • Object Hierarchy

  • Databases and Schemas

  • Tablespaces

  • Exploring Databases

    • Locating the database server's message log

    • Locating the database's system identifier

    • Listing databases on this database server

    • How much disk space does a table use?

    • Which are my biggest tables?

    • How many rows are there in a table?

    • Quickly estimating the number of rows in a table

    • Understanding object dependencies

 

Obtaining Metadata

Transactions & Concurrency Control

 

Day 3

 

Database Administration

  • Performing actions on many tables

  • Writing a script

  • Adding/removing schemas

  • Moving objects between schemas

  • Adding/removing tablespaces s

  • Moving objects between tablespaces

  • Using materialized views

 

Table Partitioning

 

Extensions

 

  • Accessing objects in other PostgreSQL databases (postgres_fdw, dblink)

  • File_fdw, hstore, pgcrypto,etc.

 

Security

 

  • User Management

    • Superuser

    • Roles and Users

    • Groups and Access Control

    • Ownership, Etc.

  • Preventing Connections

  • Checking secure password

  • Auditing Changes

  • Encrypting Sensitive data

 

Monitoring and Diagnosis

 

  • Real-time viewing using pgAdmin

  • Checking whether a user is connected

  • Checking which queries are running

  • Checking which queries are active or blocked

  • Knowing who is blocking a query

  • Killing a specific session

  • Knowing when a table was last used

  • Usage of disk space by temporary data

  • Understanding why queries slow down

  • Producing a daily summary of log file errors

  • Analyzing the real-time performance of your queries

 

Performance and Concurrency

  • Find and Tune Slow Running Queries

  • Collecting regular statistics from pg_stat* views

  • Finding out what makes SQL slow

  • Speeding up queries without rewriting them

  • Discovering why a query is not using an index

  • Forcing a query to use an index

 

Day 4

 

Regular Maintenance

 

  • Controlling automatic database maintenance

  • Removing issues that cause bloat

  • Identifying and fixing bloated tables and indexes

  • Monitoring and tuning vacuum

  • Updating Table Statistics

  • Vacuuming

  • Re-indexing

 

Backup and Recovery

 

  • Planning backups

  • Backup Types

    • Logical

      • Pg_dump

      • Pg_dumpall

    • Physical

      • Standalone hot physical database backup

      • Hot physical backup and continuous archiving

      • PgBaseBackup

  • Restore

    • Pg_restore

    • Recovery to a point in time

    • Restore Physical Backup

    • Recovery of a dropped/damaged table

    • Recovery of a dropped/damaged database

 

Moving Data

  • Exporting/Importing Data To/From A Flat File

 

Day 5

 

Replication and Upgrades

  • Replication

    • Replication best practices

    • Streaming Replication

    • Implement Hot Standby

    • Replication Slots

    • Logical Replication

    • Using repmgr

  • Handling Switchover & Failover

  • Upgrading Best Practices

    • Upgrading - minor releases

    • Upgrading - major release (pg_upgrade)

  • Migration from Oracle to Postgres using Ora2PG