PostgreSQL Open Certification
Level: DBA-1
Exam overview
This exam validates the essential knowledge and skills required of a junior PostgreSQL DBA. A successful candidate should be able to install and configure PostgreSQL, manage database objects and security, perform backup and recovery operations, understand server architecture and concurrency fundamentals, perform routine maintenance, and monitor database activity.
Exam topics
1. Installation, Versioning & Basic Configuration
- Describe PGDG repository versus distribution repository
- Install PostgreSQL from packages
- Identify default directories, binaries and other PostgreSQL-related files
- Describe the structure and content of the data directory
- Explain the purpose and usage of tablespaces
- Configure postgresql.conf for basic server settings
- Configure pg_hba.conf for client authentication
- Understand the role of SSL/TLS certificates
- Manage the PostgreSQL service using pg_ctl and systemctl
- Use pg_isready to check server availability
- Describe the PostgreSQL release cycle
- Perform minor version updates
- Describe major upgrade methods: pg_upgrade, Dump & Restore, logical replication
- Compare different upgrade options and their trade-offs
2. Connecting to PostgreSQL, Database Objects & Basic SQL
- Connect to PostgreSQL using psql with connection strings and parameters
- Use psql meta-commands and options
- Create and manage databases
- Describe the role of schemas and search_path
- Create and manage tables
- Identify common PostgreSQL data types
- Use common PostgreSQL SQL syntax, operators and expressions, including quoting, casting, concatenation and pattern matching
- Create and manage indexes
- Create and manage views and materialized views
- Create and manage sequences
- Create and use procedures and functions
3. Roles & Privileges
- Create and manage roles
- Describe role attributes and their effects
- Explain the relationship between roles and database objects
- Grant and revoke access privileges
- Describe public schema default privileges
4. Backup & Recovery
- Differentiate between physical and logical backups
- Perform logical backups using pg_dump and pg_dumpall
- Restore databases using pg_restore and psql
- Perform physical backups using pg_basebackup
- Describe WAL archiving concepts (archive_mode, archive_command)
- Explain continuous archiving and Point-in-Time Recovery at a conceptual level
5. Server Architecture & Background Processes
- Describe the role of the postmaster process
- Describe background workers and their purpose
- Describe client backend processes
- Explain the role of shared buffers
- Describe process-specific memory areas (work_mem, maintenance_work_mem)
- Explain how dirty buffers are written to disk: checkpointer, background writer, client backend
- Describe what WAL is and why it exists
- Explain the role of the WAL buffer and WAL writer
6. MVCC & Transaction Management
- Describe Multi-Version Concurrency Control (MVCC) principles
- Explain at a basic level how PostgreSQL keeps old row versions and why this creates a need for VACUUM
- Describe transaction isolation levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
- Identify types of locks: row-level and table-level
- Monitor and debug locks using pg_locks
- Describe deadlock detection, prevention and the deadlock_timeout parameter
7. VACUUM, ANALYZE & Autovacuum
- Explain what VACUUM is and why it is needed
- Describe the effects of VACUUM on dead tuples, space reuse, table bloat and transaction ID wraparound
- Differentiate between VACUUM, ANALYZE, VACUUM (ANALYZE), VACUUM (FREEZE) and VACUUM FULL
- Describe how the autovacuum daemon works
- Identify important autovacuum parameters and their meaning
- Check VACUUM activity using pg_stat_user_tables
8. Monitoring & Logging
- Query pg_stat_activity to monitor current sessions, states and queries
- Use pg_cancel_backend() and pg_terminate_backend() to manage sessions
- Query pg_locks and pg_blocking_pids to identify lock contention
- Query pg_stat_database for database-wide statistics
- Use pg_database_size() and pg_table_size() to measure database and table size
- Query pg_stat_all_tables and pg_stat_all_indexes to monitor table and index access
- Describe the role of pg_stat_bgwriter and pg_stat_checkpointer
- Install and configure pg_stat_statements to identify slow or frequent queries
- Configure log destinations: stderr, csvlog, jsonlog, syslog
- Adjust log verbosity using log_min_messages, log_error_verbosity and log_min_duration_statement
- Configure log file storage and rotation: log_directory, log_filename, log_rotation_age, log_rotation_size
- Control which queries are logged using log_statement (none, ddl, mod, all)
- Log query execution times using log_duration
- Trace specific activities: log_connections, log_disconnections, log_autovacuum_min_duration, log_checkpoints, log_lock_waits, log_temp_files
- Customise log_line_prefix for structured log entries
- Configure lc_messages and log_timezone for log localisation
Scope boundaries
Some topics are included only at a conceptual or basic operational level. For DBA1, candidates should understand what these concepts are, why they matter, and how they affect day-to-day PostgreSQL administration. They are not expected to know low-level implementation details or advanced tuning strategies.
This applies especially to logical replication, transaction ID wraparound, VACUUM and autovacuum. Advanced VACUUM internals, advanced tuning of VACUUM cost-related settings, detailed logical replication configuration, and low-level transaction ID internals are out of scope.
