I85PREP
⌘ K
EDB PostgreSQL Professional study materials
EDB PostgreSQL Professional study resources

EDB PostgreSQL Professional: PostgreSQL Core Database Administration

EDB PostgreSQL Professional · Work through your exam, one topic at a time.

All guide sections

PostgreSQL Core Database Administration

This domain tests day-to-day operation of a PostgreSQL or EDB Postgres Advanced Server cluster: configuration and process control, roles and privileges, storage layout, and routine maintenance.

Configuration and lifecycle

  • initdb builds the cluster in PGDATA; pg_ctl start|stop|restart|reload|status controls it, or the systemd/pg_ctlcluster wrapper on packaged installs.
  • Precedence is postgresql.conf < postgresql.auto.conf (written by ALTER SYSTEM) < command-line -c settings < per-session SET. A hand edit of postgresql.conf therefore loses to an earlier ALTER SYSTEM.
  • SIGHUP (reload) re-reads postgresql.conf, pg_hba.conf and pg_ident.conf. shared_buffers, max_connections and wal_level require a restart.
  • pg_hba.conf is first-match-wins, so put narrow host lines above broad ones.

Privileges and maintenance

Roles are cluster-wide; databases, schemas and tablespaces are objects. Reading a table requires three grants in sequence: CONNECT on the database, USAGE on the schema, and the object privilege. Autovacuum must keep pace with dead tuples: bloat inflates scans and blocks index-only scans because the visibility map goes stale. VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock - prefer pg_repack online.

Worked example

An application role connects successfully but SELECT * FROM sales.orders fails with "permission denied for schema sales". Reasoning: connectivity is proven by the successful login, so pg_hba.conf is not the problem; the failure names the schema, not the table. Fix with GRANT USAGE ON SCHEMA sales TO app_rw; then GRANT SELECT ON sales.orders TO app_rw;. Finally add ALTER DEFAULT PRIVILEGES FOR ROLE sales_owner IN SCHEMA sales GRANT SELECT ON TABLES TO app_rw; so tables created next month are readable too.

Traps that cost marks

Granting only the table privilege and forgetting schema USAGE; editing postgresql.conf while an ALTER SYSTEM entry overrides it; running VACUUM FULL on a busy table; assuming a superuser is subject to row-level security; treating a reload as sufficient for parameters that need a restart.