PostgreSQL: operations and recovery

Objectives and progression

Independent knowledge assessment with six modules, explained practice, and fictional APS and change-management cases. Internal final assessment of 24 decisions in 60 minutes. PostgreSQL 18 technical reference; 18.6 stable at review while 19 remains beta. It is not associated with an official Associate exam. Examples were reviewed against documentation without execution on a PostgreSQL engine.

Audience: APS/L3 teams, database administrators, and technical managers responsible for change and continuity.

Prerequisites: Basic SQL, Linux and networking concepts, and transaction understanding.

300 estimated study minutes

• Diagnose access by layer and test with the actual application identity.
• Distinguish snapshots, deadlocks, and abandoned sessions before intervening.
• Use execution evidence and representative load to choose an optimization.
• Connect growth, snapshots, and maintenance before choosing an intervention.
• Choose coherent artifacts and demonstrate recovery through the application.
• Decide promotion and cutover with explicit write authority and a return plan.

Modules

1. Connections, identities, and privileges
2. Transactions, blocking, and resumption
3. Plans, indexes, and memory
4. Vacuum, configuration, and capacity
5. Backups, WAL, and usable recovery
6. Replication, upgrades, and handover

Continue learning

Databases

• SQL
• Oracle Database Administration
• Microsoft Azure Database Administrator
• Production Support L3

References and version

PostgreSQL 18 reference semantics;18.6 current stable at review

• Supported releases and upgrade policy · 2026-09-30
• Authentication rule order · 2026-09-30
• SCRAM migration · 2026-09-30
• Client TLS verification · 2026-09-30
• Database and object privileges · 2026-09-30
• Row security and bypass roles · 2026-09-30
• Future object privileges · 2026-09-30
• MVCC and transaction isolation · 2026-09-30
• Lock conflicts and deadlocks · 2026-09-30
• Statement lock and idle transaction timeouts · 2026-09-30
• Cancellation and administrative functions · 2026-09-30
• SQLSTATE classification · 2026-09-30
• Transaction command completion and protocol · 2026-09-30
• Execution plans and runtime evidence · 2026-09-30
• Concurrent index construction · 2026-09-30
• Planner estimates and statistics · 2026-09-30
• Aggregated query statistics · 2026-09-30
• Work memory and concurrent operations · 2026-09-30
• Vacuum and freezing · 2026-09-30
• Vacuum operation and locks · 2026-09-30
• Activity waits and maintenance monitoring · 2026-09-30
• Configuration precedence and reload · 2026-09-30
• Base backups and WAL recovery · 2026-09-30
• Logical single database backup · 2026-09-30
• Cluster global objects · 2026-09-30
• Physical base backup · 2026-09-30
• Backup verification limits · 2026-09-30
• Streaming replication and slots · 2026-09-30
• Replication retention configuration · 2026-09-30
• Promotion and fencing · 2026-09-30
• Logical replication exclusions · 2026-09-30
• Major upgrade modes and rollback boundaries · 2026-09-30