OSPI Certified: PostgreSQL Administrator

Exam objectives

The OSPI Certified: PostgreSQL Administrator examination assesses day-one through day-two PostgreSQL administration on a live Linux server using PostgreSQL 16. Candidates work in an isolated live lab against a realistic database administration scenario. Domain weights reflect the relative emphasis of day to day operations of real database administrators.

1. 1. Instance, roles, and database lifecycle (20%)
   • Determine core instance properties required for operational handoff
   • Create and configure login roles for application and read-only workloads
   • Apply connection limits and credential policies appropriate to each role
   • Provision an application database with correct ownership and access boundaries
   • Document role configuration for audit and support teams
2. 2. Schema deployment, data access, and reporting (35%)
   • Deploy provided schema and seed data into the application database
   • Validate deployed objects and baseline row counts
   • Design and apply indexes aligned to application query patterns
   • Grant least-privilege access on existing objects for application and read-only roles
   • Establish default privileges so future objects inherit correct access automatically
   • Build reporting views that expose defined business columns
   • Produce aggregate query results used for operational reporting
   • Perform application-level data changes under an application role account
3. 3. Server configuration, authentication, and connectivity (20%)
   • Adjust server logging parameters and apply changes with minimal service disruption
   • Identify primary server and client authentication configuration locations
   • Configure host-based authentication for local TCP application access
   • Verify that an application role can connect over TCP and document connection details
4. 4. Backup, restore, maintenance, and WAL operations (25%)
   • Create logical backups in custom archive and plain SQL formats
   • Restore a backup into a separate database and confirm data integrity
   • Run routine table maintenance and interpret post-maintenance statistics
   • Collect recent server log output for troubleshooting review
   • Configure write-ahead log archiving for point-in-time recovery readiness