PostgreSQL Administration
Learn to configure, secure, maintain, recover, and tune PostgreSQL using a training database based on Microsoft Northwind data.
Course overview
Progress from PostgreSQL fundamentals to advanced database administration techniques during this three-day instructor-led course. Work with an instructor-provided PostgreSQL adaptation of Microsoft's Northwind sample database, managing products, customers, orders, and order details for a trading company. Start with server architecture, configuration, and access control, practise maintenance and tested backup recovery, then investigate performance, configure a standby, and plan safe upgrades. Guided exercises use isolated training servers and a larger synthetic Northwind order workload so participants can observe administration and performance behaviour without touching production systems.
Course coverage
Build a practical administration workflow from the first connection to recovery, performance analysis, and availability planning.
- 01 PostgreSQL architecture: clusters, databases, processes, and WAL
- 02 Installation, services, psql, pgAdmin, and server configuration
- 03 Northwind schemas, data loading, constraints, and sequences
- 04 Roles, privileges, pg_hba.conf, SCRAM authentication, and TLS
- 05 MVCC, transactions, locks, VACUUM, ANALYZE, and autovacuum
- 06 Logs, activity statistics, disk usage, and routine monitoring
- 07 Logical backups with pg_dump, pg_restore, and global objects
- 08 Physical backups, WAL archiving, and point-in-time recovery
- 09 EXPLAIN, pg_stat_statements, indexes, and measured tuning
- 10 Physical streaming replication, slots, lag, and standby checks
- 11 Controlled failover, recovery objectives, and availability limits
- 12 Minor updates, major upgrades, rehearsal, and operational runbooks
Participants will learn to
- Explain PostgreSQL architecture and administer a training server.
- Load and verify a PostgreSQL adaptation of Northwind data.
- Apply least-privilege roles and secure client connections.
- Monitor activity, diagnose blocking, and manage routine maintenance.
- Create logical and physical backups and verify restores.
- Recover a database to a chosen point using archived WAL.
- Measure query performance and test indexing and configuration changes.
- Configure a standby and document failover and upgrade procedures.
Three-day course outline
Day 1 - PostgreSQL foundations, configuration, and security: Understand the distinction between a PostgreSQL cluster and a database, server processes, storage, and write-ahead logging (WAL). Follow a guided installation and service-management exercise, connect with psql and pgAdmin, and inspect postgresql.conf settings, including when a reload or restart is required. Load the PostgreSQL-compatible Northwind schema and data, inspect Products, Categories, Customers, Orders, and Order Details relationships, and verify row counts, constraints, and sequence values. Create login and group roles, separate ownership from application access, and apply schema, table, sequence, and default privileges. Configure pg_hba.conf rule ordering, SCRAM password authentication, and TLS for remote connections. Labs: prepare the Northwind database and verify allowed and denied operations with reader, application, and administrator roles.
Day 2 - Maintenance, monitoring, backup, and recovery: Explore multiversion concurrency control (MVCC), transaction behaviour, blocking, and long-running transactions. Use pg_stat_activity and pg_locks to diagnose a blocked Northwind order update. Review VACUUM, ANALYZE, autovacuum, table bloat, and transaction ID wraparound prevention, then inspect logs, statistics, and disk growth. Create a custom-format logical backup with pg_dump, capture roles and other global objects separately with pg_dumpall --globals-only, and use pg_restore to verify a restore into an isolated environment. Distinguish logical dumps from physical base backups. Use pg_basebackup and continuous WAL archiving to practise point-in-time recovery (PITR), checking archive continuity and selecting a recovery target. Labs: restore Northwind and verify data and privileges; recover an isolated cluster to just before a simulated accidental order deletion and compare expected records. Relate backup retention and restore testing to recovery point and recovery time objectives (RPO/RTO).
Day 3 - Performance, replication, and advanced administration: Analyse a larger synthetic Northwind order workload with pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS), noting that EXPLAIN ANALYZE executes the statement and using safe read-only lab queries. Compare query plans before and after appropriate indexes and refreshed statistics; review measured changes to memory, connection, and checkpoint settings rather than applying universal tuning values. Configure physical streaming replication to a prepared standby, verify read-only queries, and monitor replication lag, replication slots, and retained WAL disk usage. Discuss synchronous versus asynchronous replication and potential data loss. Rehearse controlled manual failover by stopping or fencing the old primary before promoting the standby, then verify the new primary and outline how to rejoin the former server. Finish by distinguishing minor updates from major-version upgrades, reviewing pg_upgrade and dump/restore approaches, checking extension compatibility, and planning rehearsals, validation, and fallback. Final lab: document a Northwind operations runbook with tuning evidence, standby checks, recovery objectives, and failover and upgrade checklists.
Format and audience
For: New database administrators, developers supporting PostgreSQL applications, system administrators, and technical teams responsible for database reliability and performance.
Prerequisites: Some familiarity with SQL, including SELECT, filtering, joins, and relational tables. The ITTrainingNow course SQL For Beginners - Essential Query Skills, or equivalent experience, provides the recommended preparation. Comfort working with files and basic command-line commands is helpful; no prior PostgreSQL administration experience is required.
Lab setup: A computer with a modern browser, a terminal or SSH client, and instructor-approved PostgreSQL client tools including psql and pgAdmin. The instructor provides isolated Linux-based PostgreSQL training environments on a supported release, administrative lab access, and setup instructions. Windows and macOS participants can connect remotely; local virtual-machine options and network requirements are confirmed before class.
How it works: Three days of instructor-led explanations, demonstrations, and hands-on Northwind administration labs. Contact us to discuss dates and virtual or onsite delivery.
Explore the course
Request a quote for the three-day PostgreSQL Administration course and discuss your team's training needs.