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




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



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




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

  • File_fdw, hstore, pgcrypto,etc.




  • 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