EDB PostgreSQL Associate study plan

Install PostgreSQL 16 both from the OS package manager and from source, and record the differences in startup and file layout

Draw the process model from memory: postmaster, backends, background writer, checkpointer, WAL writer, autovacuum launcher, and describe what each does

Locate and explain pg_hba.conf, postgresql.conf, pg_ident.conf, and the data directory layout including pg_wal and pg_xact

Provision two clusters on one host with initdb and different ports, then start and stop each with pg_ctl

Set and verify five key GUCs (shared_buffers, work_mem, max_connections, wal_level, listen_addresses) and state whether each needs a restart or reload

Create a practice schema with tables using integer, numeric, text, varchar, timestamp with time zone, date, boolean, uuid, jsonb, and array types, and justify each type choice

Write SELECT queries using WHERE, ORDER BY, LIMIT/OFFSET, DISTINCT, and CASE, and predict the output before running each

Write multi-table joins (INNER, LEFT, FULL) and self-joins, and explain the row-count difference between them on the same data

Use aggregate functions with GROUP BY and HAVING, plus filtered aggregates, to answer five business-style questions

Use INSERT ... ON CONFLICT, UPDATE ... FROM, and DELETE ... USING, and confirm affected row counts

Create tables with primary keys, foreign keys with ON DELETE behavior, CHECK constraints, UNIQUE constraints, and NOT NULL, then test each constraint with a failing statement

Build indexes of type btree, hash, GIN (on jsonb or array), and a partial and an expression index, then verify usage with EXPLAIN

Define a view, a materialized view, and a sequence, and refresh the materialized view with CONCURRENTLY

Write CTEs including a recursive CTE, and window functions with PARTITION BY and ORDER BY

Explain transaction behavior with BEGIN, COMMIT, ROLLBACK, and SAVEPOINT, demonstrating MVCC visibility across two sessions

Perform logical backups with pg_dump in plain, custom, and directory formats, and restore each with psql or pg_restore into a fresh database

Take a base backup with pg_basebackup, restore it to a new directory, and start the restored cluster

Configure continuous archiving with wal_level=replica, archive_mode, and archive_command, then confirm WAL segments land in the archive

Simulate a crash by deleting the data directory contents after a base backup, then recover to a consistent state using restore_command and a recovery target time

Explain and demonstrate the difference between crash recovery, point-in-time recovery, and a logical dump restore, and state when each is appropriate

Read EXPLAIN and EXPLAIN (ANALYZE, BUFFERS) output and identify sequential scans, index scans, nested loops, hash joins, and sort spills on a table of at least one million rows

Query pg_stat_activity, pg_stat_user_tables, pg_stat_user_indexes, and pg_locks to diagnose a long-running query and a blocked session

Tune work_mem and shared_buffers on the test instance and measure the effect on a sort-heavy query's plan and runtime

Set up autovacuum tuning and demonstrate the effect of VACUUM and ANALYZE on table bloat and planner statistics

Configure log_min_duration_statement and pg_stat_statements, then identify the top three statements by total execution time

Configure streaming replication from a primary to one standby using pg_basebackup and primary_conninfo, and verify the standby is in recovery and receiving WAL

Query pg_stat_replication and pg_stat_wal_receiver to confirm replication lag and state

Perform a manual promotion of the standby with pg_ctl promote and repoint an application connection to the new primary

Describe and compare synchronous_commit settings and their latency and durability trade-offs

Configure replication slots and explain their role in retaining WAL, including the disk-space risk of an inactive slot

Create roles and groups, grant and revoke privileges on databases, schemas, tables, and sequences, and verify with a login test for each role

Configure pg_hba.conf for local socket, host with scram-sha-256, and a rejecting rule, and test each rule

Set and verify default privileges so that new tables inherit intended grants, and demonstrate a column-level grant

Audit an instance for security posture: check for superuser accounts, weak or trust auth entries, PUBLIC schema CREATE privileges, and unencrypted connections

Review pg_stat_activity and logging settings to confirm you can attribute activity to a specific authenticated role

Re-run every earlier weekly deliverable from scratch on a clean instance to confirm retention

Complete two timed full-length practice exams and record the score per domain

Rebuild the three weakest areas into a one-page cheat sheet of commands and syntax

Rehearse the top ten most-missed facts aloud, including configuration defaults, recovery options, and replication views