PostgreSQL CE and Oracle DBA CA Domain Review

PostgreSQL CE and Oracle DBA CA Domain Review

This lesson consolidates the highest-weight exam domains for two industry credentials: the EnterpriseDB PostgreSQL CE and the Oracle DBA Certified Associate (DBA CA). Mastering these domains is essential not only for passing your exams but for performing safely and competently on the job.

---

PostgreSQL CE High-Weight Domains

Server Installation and Configuration Parameters

PostgreSQL is controlled largely through the `postgresql.conf` file, which sets server-level parameters such as `max_connections`, `shared_buffers`, `work_mem`, and `listen_addresses`. Changes to some parameters require a server reload; others require a full restart. Knowing which parameters fall into each category is a common exam topic. The `pg_hba.conf` file controls client authentication: it lists rules in order, and the server uses the first matching rule. Authentication methods include `trust`, `md5`, `scram-sha-256`, `peer`, and `reject`. Incorrectly configured `pg_hba.conf` rules are among the most common causes of connection failures in production.

VACUUM and Autovacuum

PostgreSQL uses Multi-Version Concurrency Control (MVCC). Dead tuples left by updates and deletes must be reclaimed by VACUUM. Regular VACUUM reclaims space for re-use; VACUUM FULL rewrites the table, returning space to the OS but requiring an exclusive lock. The autovacuum daemon runs this process automatically based on configurable thresholds. Failing to vacuum aggressively enough leads to table bloat and, in extreme cases, transaction ID (XID) wraparound, which can cause data unavailability. This is a critical operational safety concern: monitor `age(datfrozenxid)` and ensure autovacuum is never disabled without a deliberate alternative plan.

Query Planning and EXPLAIN Output

The `EXPLAIN` command shows the query plan chosen by the planner without executing the query. `EXPLAIN ANALYZE` executes the query and shows actual row counts and timing. Key plan nodes include Sequential Scan, Index Scan, Hash Join, and Nested Loop. High row-estimate errors between planned and actual rows often indicate stale statistics; running `ANALYZE` updates them. Understanding cost units (arbitrary, not milliseconds) and identifying inefficient plans is a core DBA skill.

Replication

PostgreSQL supports streaming replication (binary, physical copy of the WAL stream to a standby) and logical replication (publishes changes at the row level to subscribers, allowing selective table replication across major versions). Streaming replication requires a replication role and appropriate `pg_hba.conf` entries. The `recovery.conf` (pre-v12) or `postgresql.conf` (v12+) on the standby controls connection parameters.

Backup Strategies

`pg_dump` exports a single database in SQL or custom format and is suitable for logical backups. `pg_basebackup` captures a physical, consistent base backup of the entire cluster and is used as the starting point for Point-In-Time Recovery (PITR) when combined with archived WAL segments. Understand when to use each tool and their key options.

Role-Based Access Control

PostgreSQL uses roles for both users and groups. Privileges are granted at the database, schema, table, column, and function level. The `GRANT` and `REVOKE` commands manage privileges; `ALTER DEFAULT PRIVILEGES` controls what new objects inherit. The `pg_roles` and `information_schema.role_table_grants` views are useful for auditing access — an important security practice.

---

Oracle DBA CA High-Weight Domains

Instance and Database Architecture

An Oracle instance is the combination of memory structures and background processes. The System Global Area (SGA) includes the Database Buffer Cache, Shared Pool, Redo Log Buffer, and Large Pool. The Program Global Area (PGA) is private to each server process. Key background processes include DBWR (writes dirty buffers to datafiles), LGWR (writes redo log buffer to online redo logs), SMON (system monitor, crash recovery), PMON (process monitor, cleans up failed processes), and CKPT (updates checkpoint information).

Tablespace and Datafile Management

Oracle stores data in tablespaces, which are logical containers mapped to one or more physical datafiles. The SYSTEM and SYSAUX tablespaces are required. Locally managed tablespaces (using bitmaps in the datafile headers) are the modern standard over dictionary-managed tablespaces. DBAs create, resize, add datafiles to, and take tablespaces offline as needed.

Undo and Redo Log Management

Undo segments store before-images of changed data to support rollback and read consistency. Online redo logs record all changes for recovery. Oracle requires at least two redo log groups, and best practice calls for multiplexed members. Log switches and checkpoints are performance-sensitive operations.

RMAN Backup and Recovery

RMAN (Recovery Manager) is Oracle's primary backup and recovery tool. Common commands include `BACKUP DATABASE`, `BACKUP ARCHIVELOG ALL`, `RESTORE DATABASE`, and `RECOVER DATABASE`. RMAN works with a recovery catalog or the control file. Understanding full, incremental level 0, and incremental level 1 backups, and the difference between complete and incomplete (point-in-time) recovery, is essential.

Privileges, Roles, and Data Guard

Oracle separates system privileges (e.g., `CREATE SESSION`, `CREATE TABLE`) from object privileges (e.g., `SELECT`, `INSERT` on a specific table). Pre-defined roles such as `DBA`, `CONNECT`, and `RESOURCE` bundle common privileges. Oracle Data Guard maintains one or more standby databases synchronized with the primary through redo transport; it supports switchover (planned) and failover (unplanned) operations.

---

Accuracy and Safety in Exam Prep

When studying, rely only on official documentation — the PostgreSQL documentation at postgresql.org and Oracle's official documentation — rather than unofficial summaries that may contain errors. In a live environment, always test configuration changes in a non-production system first. Backup procedures are a professional safety net: a DBA who cannot restore a backup has no backup. Treat your study materials with the same discipline you would apply on the job.