Free Practice Questions for EDB PostgreSQL Professional

Exam-style questions and explanations by OpenExamPrep.

✓ No registration✓ No credit card

100+ Questions

100% Free

Loading practice questions...

Same family resources

Explore More PostgreSQL (EDB) Certifications

Continue into nearby exams from the same family. Each card keeps practice questions, study guides, flashcards, videos, and articles in one place.

PostgreSQL Associate

Practice Questions200 questions Study Guide

Exam Review

Key Facts: EDB PostgreSQL Professional Exam

~60

Exam Questions

EDB

70%

Passing Score

EDB

90 min

Exam Duration

EDB

$200

Exam Fee

EDB

2 years

Credential Validity

EDB

Advanced

Difficulty Level

EDB

EDB PostgreSQL Professional is approximately 60 multiple-choice questions in 90 minutes with a 70% passing score and a $200 USD exam fee. Covers advanced administration: partitioning (range/list/hash), streaming and logical replication, HA with Patroni, backup/recovery with Barman, PITR, EXPLAIN ANALYZE tuning, RLS, and pgaudit. Credential valid for 2 years. Associate certification or equivalent experience is recommended.

Sample EDB PostgreSQL Professional Practice Questions

Try these sample questions to review concepts for the EDB PostgreSQL Professional exam. Each question includes a detailed explanation. Start the interactive quiz above for the full 100+ question experience with AI tutoring.

1Which DDL statement creates a range-partitioned table in PostgreSQL 10+ with a default partition?

A.CREATE TABLE orders (id BIGINT, order_date DATE) PARTITION BY RANGE (order_date); CREATE TABLE orders_default PARTITION OF orders DEFAULT;

B.CREATE PARTITIONED TABLE orders BY RANGE (order_date); CREATE TABLE orders_default AS DEFAULT PARTITION;

C.ALTER TABLE orders PARTITION BY RANGE (order_date) ADD DEFAULT PARTITION orders_default;

D.CREATE TABLE orders PARTITIONED (order_date RANGE) WITH DEFAULT PARTITION orders_default;

Explanation: PostgreSQL declarative partitioning uses PARTITION BY RANGE on the parent table, then CREATE TABLE ... PARTITION OF with DEFAULT for unmatched rows (PostgreSQL 11+).

2What is the purpose of `PARTITION BY LIST` in PostgreSQL and how does it differ from RANGE partitioning?

A.LIST partitioning assigns rows based on exact value matches (discrete values); RANGE partitioning assigns rows to a contiguous range of values

B.LIST partitioning orders rows alphabetically; RANGE partitioning orders numerically

C.LIST partitioning uses hash buckets for even distribution; RANGE partitioning splits by value threshold

D.LIST and RANGE are identical except for partition pruning behavior

Explanation: LIST partitioning routes rows where the partition key matches a specific enumerated set of values (e.g., country codes). RANGE covers contiguous value intervals (e.g., date ranges).

3Which PostgreSQL command attaches an existing table as a partition of a partitioned parent?

A.ALTER TABLE orders ATTACH PARTITION orders_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')

B.ALTER TABLE orders ADD PARTITION orders_2024 RANGE ('2024-01-01', '2025-01-01')

C.CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') USING EXISTING

D.ATTACH PARTITION orders_2024 TO orders RANGE ('2024-01-01' TO '2025-01-01')

Explanation: ALTER TABLE ... ATTACH PARTITION assigns an existing table as a partition. PostgreSQL validates that all existing rows satisfy the partition constraint before completing.

4What does `pg_basebackup -D /var/lib/pgsql/backup -X stream -P` do?

A.Creates a base backup of the running cluster to /var/lib/pgsql/backup while streaming WAL changes to ensure a consistent backup, showing progress

B.Creates a logical backup of all databases to the specified directory

C.Backs up only the pg_wal directory using streaming replication protocol

D.Archives existing WAL files to the specified directory without copying data files

Explanation: -D sets the destination, -X stream includes WAL via a second streaming connection during the backup, and -P shows progress. Together they create a consistent base backup suitable for PITR.

5Which pg_basebackup option is required to make the backup usable for Point-in-Time Recovery?

A.-X stream (or -X fetch/fetch) to include WAL files needed to reach consistency

B.--wal-method=none to create the smallest possible backup

C.--target-action=promote to auto-promote the standby

D.--format=plain to enable PITR-compatible file layout

Explanation: PITR requires WAL segments generated during the backup window. -X stream streams them live; -X fetch copies them after; without either, WAL must be separately available via archive.

6What is the difference between `pg_dump` and `pg_basebackup` in PostgreSQL?

A.pg_dump creates a logical (SQL/custom format) backup of a database; pg_basebackup creates a physical file-system-level backup of the entire cluster

B.pg_dump is faster for large databases; pg_basebackup is for small databases only

C.pg_dump requires the cluster to be stopped; pg_basebackup runs online

D.pg_dump backs up all databases; pg_basebackup backs up only one

Explanation: pg_dump exports a database as SQL statements or custom binary format (portable, restore to different major versions). pg_basebackup copies the physical data directory (cluster-level, not portable across major versions without pg_upgrade).

7What configuration parameter enables WAL archiving in PostgreSQL, and what is the minimum required wal_level?

A.archive_mode = on; wal_level must be at least 'replica'

B.archive_mode = on; wal_level must be 'logical'

C.archive_mode = on; wal_level can remain 'minimal'

D.archive_command alone enables archiving; wal_level is unrelated

Explanation: archive_mode = on enables WAL archiving. wal_level must be 'replica' or higher (replica or logical) to include all data needed for standby servers and PITR.

8In PostgreSQL PITR, what file must be present in the restore target directory to specify recovery settings in PostgreSQL 12+?

A.postgresql.conf with recovery_target_time or recovery_target_lsn parameters, and a standby.signal or recovery.signal file

B.recovery.conf with recovery_target_time and restore_command

C.pg_recovery.conf placed in the data directory

D.No file is needed; recovery parameters are auto-detected from pg_control

Explanation: PostgreSQL 12 replaced recovery.conf with parameters in postgresql.conf plus a signal file: standby.signal for standby mode or recovery.signal for one-time PITR. Without the signal file, recovery parameters are ignored.

9Which SQL query uses EXPLAIN ANALYZE to show the actual execution time and row counts?

A.EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending';

B.EXPLAIN SELECT o.id FROM orders WHERE status = 'pending';

C.ANALYZE SELECT * FROM orders;

D.EXPLAIN VERBOSE SELECT * FROM orders WHERE id = 1;

Explanation: EXPLAIN ANALYZE executes the query and shows the query plan with actual vs estimated rows and timing at each node. EXPLAIN alone only shows the estimated plan without execution.

10In an EXPLAIN ANALYZE output, what does '(actual time=0.123..45.678 rows=1000 loops=3)' mean?

A.The node ran 3 times (loops); startup time was 0.123ms, total time 45.678ms per loop, and it returned 1000 rows per execution

B.The query ran for 45.678 seconds total with 3 parallel workers

C.The index was scanned 3 times and found 1000 matching rows in 45ms total

D.The node used 3 buffer pages and took 45.678ms to complete

Explanation: In EXPLAIN ANALYZE, 'loops=N' means the plan node executed N times (e.g., once per outer row in a nested loop). Times and row counts are per-loop averages. Total cost = per-loop-time × loops.

About the EDB PostgreSQL Professional Exam

The EDB PostgreSQL Professional Certification validates advanced PostgreSQL DBA skills. It covers declarative table partitioning, streaming and logical replication, high availability architectures with Patroni and PgBouncer, backup and recovery with pg_basebackup and Barman, deep performance tuning using EXPLAIN ANALYZE, autovacuum configuration, advanced indexing, and security with Row Level Security (RLS) and pgaudit.

Exam sponsor: EnterpriseDB (EDB). The requirements and fees below concern the certification or admission exam, separate from our free practice resources.

Questions

60 questions

Time Limit

90 minutes

Passing Score

70%

Exam / Certification Fees

$200 USD

Exam sponsor website

Fees, eligibility, and exam policies can change. Confirm them with the exam sponsor before applying or paying.

Official sources

Our practice resources: topics covered

We aim to reflect publicly available exam outlines and topic information in our study resources. Coverage, format, and difficulty may differ from the actual exam, and we cannot guarantee that every detail is accurate or current. Confirm exam requirements, fees, and policies with the official exam sponsor.

~20%

Advanced Data Management

Declarative table partitioning (range, list, hash, composite), partition pruning and constraint exclusion, TOAST storage, large objects, unlogged tables, and advanced data type strategies.

~20%

Replication

Streaming replication setup (primary_conninfo, recovery.conf/standby.signal), replication slots (physical and logical), synchronous_commit levels (off/local/remote_write/on/remote_apply), logical replication with CREATE PUBLICATION and CREATE SUBSCRIPTION, and replication lag monitoring.

~15%

High Availability

Hot standby server configuration, pg_promote() for failover, Patroni distributed HA cluster management, EDB Postgres Distributed, PgBouncer connection pooling modes (transaction/session/statement), and HAProxy load balancing.

~15%

Backup and Recovery

pg_basebackup (full and incremental), WAL archiving with archive_command, continuous archiving, point-in-time recovery (PITR) with recovery_target_time/lsn/xid, Barman installation and configuration, Barman backup strategies, and restore testing.

~15%

Performance Tuning

EXPLAIN ANALYZE plan reading (Seq Scan, Index Scan, Bitmap Heap Scan, Hash Join, Nested Loop), work_mem and shared_buffers tuning, autovacuum configuration (autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_delay), partial indexes, expression indexes, covering indexes, and pg_stat_activity and pg_stat_statements.

~15%

Security

Row Level Security (RLS) with CREATE POLICY (USING and WITH CHECK), enabling RLS on tables, role-based policy bypass (BYPASSRLS), SSL/TLS client certificate authentication, pg_hba.conf methods, audit logging with pgaudit extension, and encryption options.

Preparing for the EDB PostgreSQL Professional Exam

What You Need to Know

Passing score: 70%
Exam length: 60 questions
Time limit: 90 minutes
Exam / certification fees: $200 USD Official sources

Using Our Practice Resources

Work through all 100 available questions
Review every answer and explanation
Track weak areas and revisit them
Use our AI tutor for tough concepts

EDB PostgreSQL Professional: Suggested Study Strategy

1Practice declarative partitioning hands-on: create range, list, and hash partitioned tables and test partition pruning with EXPLAIN.

2Set up a streaming replication lab with a primary and standby, then practice pg_promote() failover and reconnecting clients.

3Install Barman in a test environment and perform at least one complete PITR recovery from a Barman-managed backup.

4Write RLS policies for both USING and WITH CHECK clauses and test with different roles using SET ROLE.

5Analyze EXPLAIN ANALYZE output until you can identify Seq Scan vs Index Scan, Hash Join vs Nested Loop, and spot missing indexes.

6Know the synchronous_commit options: off, local, remote_write, on, remote_apply — and the durability/performance tradeoffs of each.

Frequently Asked Questions

What is the EDB PostgreSQL Professional exam?

It is an advanced-level PostgreSQL DBA certification from EnterpriseDB (EDB) that validates hands-on expertise in partitioning, replication, high availability, backup and recovery, performance tuning, and security. It builds on the Associate-level skills and is designed for experienced PostgreSQL administrators.

What is logical replication and how does it differ from streaming replication?

Streaming replication copies the entire physical WAL stream from primary to standby, replicating all changes to create an exact byte-for-byte copy. Logical replication replicates individual table changes using a publication/subscription model, allowing selective replication of specific tables, cross-version replication, and replication to non-PostgreSQL subscribers.

How do you set up Row Level Security (RLS) in PostgreSQL?

First, ALTER TABLE mytable ENABLE ROW LEVEL SECURITY. Then CREATE POLICY policy_name ON mytable USING (owner = current_user). The USING clause filters rows for SELECT/DELETE, and WITH CHECK filters rows for INSERT/UPDATE. Table owners bypass RLS by default; use ALTER TABLE FORCE ROW LEVEL SECURITY to enforce policies on owners.

What is Barman and how does it work?

Barman (Backup and Recovery Manager) is the EDB/2ndQuadrant tool for PostgreSQL backup orchestration. It connects to PostgreSQL servers, archives WAL segments continuously via streaming or rsync, takes full base backups with pg_basebackup integration, maintains backup catalogs, and enables PITR recovery to specific timestamps or LSNs. It runs as a separate dedicated backup server.

What autovacuum parameters are most important for tuning?

autovacuum_vacuum_scale_factor (default 0.2, lower to vacuum more frequently on large tables), autovacuum_analyze_scale_factor, autovacuum_vacuum_cost_delay (throttle I/O impact), autovacuum_max_workers (concurrent autovacuum processes), and autovacuum_vacuum_threshold (minimum dead tuple count before triggering). Per-table storage parameters (autovacuum_vacuum_scale_factor) override global settings.