← Back to quizzesFree quiz

PostgreSQL 18.7 Core Concepts

PostgreSQL is a powerful, open‑source relational database system that offers a rich set of features for developers and administrators. This course breaks down the essential concepts tested…

10 questions~5 min
PostgreSQL 18.7 Core Concepts — Qwi
0 / 10
Score: 0%
1

Which function checks whether a PostgreSQL server is accepting connections?

2

What isolation level prevents non‑repeatable reads but allows phantom rows?

3

Which configuration parameter controls the naming pattern of WAL segment files?

4

Which view provides information about currently running backend processes?

5

Which file stores host‑based authentication rules for PostgreSQL?

6

Which utility restores a database from a custom‑format dump file?

7

What is the effect of calling pg_sleep(5) in a SQL statement?

8

Which function returns the name of a WAL file for a given LSN?

9

Which view lists the locks currently held by active sessions?

10

Which function creates a new logical replication slot?

Understanding Core PostgreSQL Concepts

PostgreSQL is a powerful, open‑source relational database system that offers a rich set of features for developers and administrators. This course breaks down the essential concepts tested in the "PostgreSQL 18.7 Core Concepts" quiz, providing clear explanations, practical examples, and SEO‑friendly language to help you master the fundamentals.

1. Monitoring Server Availability with pg_isready

The pg_isready utility is the go‑to command for checking whether a PostgreSQL server is ready to accept connections. It contacts the server, evaluates the response, and returns an exit status that scripts can use to trigger alerts or automated recovery steps.

  • Typical usage: pg_isready -h localhost -p 5432
  • Exit codes: 0 = ready, 1 = rejecting connections, 2 = no response, 3 = error.
  • Why it matters: Quickly detecting downtime helps maintain high availability and reduces mean time to recovery (MTTR).

2. Transaction Isolation Levels: Read Committed

PostgreSQL supports several isolation levels defined by the SQL standard. The Read Committed level prevents non‑repeatable reads—a transaction will never see a row that was changed after it began—but it still allows phantom rows to appear when new rows match a query's WHERE clause.

  • Non‑repeatable read: A row’s column values change between two reads within the same transaction.
  • Phantom row: New rows become visible to a later query in the same transaction.
  • Read Committed is the default isolation level in PostgreSQL because it balances consistency with performance.

To set a different isolation level, use SET TRANSACTION ISOLATION LEVEL or specify it in the BEGIN statement.

3. Write‑Ahead Logging (WAL) Segment Naming

The configuration parameter wal_segment_size determines the size of each WAL segment file and indirectly influences the naming pattern. While the actual file names follow a fixed 24‑character hexadecimal format (e.g., 0000000100000000000000A1), the segment size (default 16 MB) defines how many bytes each file contains before a new segment is created.

  • Changing wal_segment_size requires a cluster restart and a fresh base backup.
  • Typical values: 16 MB, 32 MB, 64 MB, or 128 MB, chosen based on workload and storage considerations.
  • Proper sizing helps reduce checkpoint overhead and improves recovery time.

4. Inspecting Active Backends with pg_stat_activity

The system view pg_stat_activity provides real‑time insight into every backend process connected to the database. It shows session IDs, user names, current queries, start times, and state (e.g., active, idle, idle in transaction).

SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state <> 'idle';
  • Use this view for troubleshooting long‑running queries or detecting blocked sessions.
  • Administrators can terminate problematic backends with SELECT pg_terminate_backend(pid);.
  • Monitoring tools like pgAdmin and pgBadger rely on this view for dashboards.

5. Host‑Based Authentication: The Role of pg_hba.conf

Security in PostgreSQL is largely controlled by the pg_hba.conf file, which stands for "host‑based authentication." Each line defines a rule that matches a client’s IP address, database, user, and authentication method (e.g., md5, scram-sha-256, trust).

  • Typical entry: host all all 192.168.0.0/24 md5
  • After editing, reload the configuration with SELECT pg_reload_conf(); or pg_ctl reload.
  • Misconfiguration can lock out legitimate users or expose the server to unauthorized access.

6. Restoring from a Custom‑Format Dump with pg_restore

When you create a dump using pg_dump -Fc, the output is a custom‑format archive that supports selective restores, parallelism, and compression. The companion utility pg_restore reads this archive and reconstructs the database objects.

pg_restore -d target_db -j 4 mydb.dump
  • -j enables parallel restoration, dramatically speeding up large restores.
  • Use --list to view the archive’s contents before restoring.
  • For plain‑SQL dumps, the psql command is used instead of pg_restore.

7. Delaying Execution with pg_sleep()

The function pg_sleep(seconds) pauses the execution of the current backend for the specified number of seconds. It is often used in testing, throttling, or simulating long‑running operations.

SELECT pg_sleep(5);  -- pauses for five seconds
  • During the sleep, the session holds any locks it already acquired.
  • Be cautious in production environments; excessive sleeps can block other transactions.
  • Combined with pg_notify, it can create simple polling mechanisms.

8. Mapping LSN to WAL File Names with pg_walfile_name

Log Sequence Numbers (LSNs) uniquely identify positions within the WAL. The function pg_walfile_name(lsn) converts an LSN to the corresponding WAL file name, which is essential for point‑in‑time recovery (PITR) and debugging replication issues.

SELECT pg_walfile_name('0/3000140');
  • The returned name follows the 24‑character format used by the pg_wal directory.
  • Pair it with pg_walfile_name_offset(lsn) to obtain both file name and byte offset.
  • Understanding this mapping helps when manually copying WAL files for backup or when investigating corruption.

9. Putting It All Together: A Sample Maintenance Workflow

Below is a concise, step‑by‑step workflow that demonstrates how the concepts covered in this course interact during routine maintenance.

  1. Check server health: pg_isready to ensure the instance accepts connections.
  2. Review active sessions: Query pg_stat_activity for long‑running queries.
  3. Apply configuration changes: Edit pg_hba.conf or adjust wal_segment_size, then reload with SELECT pg_reload_conf();.
  4. Create a backup: Use pg_dump -Fc -f mydb.dump for a custom‑format dump.
  5. Test recovery: Restore to a test database with pg_restore -d testdb mydb.dump.
  6. Simulate delay: Run SELECT pg_sleep(5); to verify timeout handling in client applications.
  7. Locate WAL files: Convert an LSN from a backup log using pg_walfile_name(lsn).

By mastering these commands and views, you gain the ability to monitor, secure, and recover PostgreSQL databases efficiently.

10. Key Takeaways

  • pg_isready is essential for health checks and automation.
  • Read Committed isolation prevents non‑repeatable reads while allowing phantom rows.
  • wal_segment_size defines WAL file size and influences naming conventions.
  • pg_stat_activity provides a live snapshot of backend processes.
  • pg_hba.conf controls host‑based authentication; always reload after edits.
  • pg_restore restores custom‑format dumps with options for parallelism.
  • pg_sleep() pauses execution, useful for testing and throttling.
  • pg_walfile_name maps LSNs to WAL file names, crucial for PITR.

Integrating these tools into your daily DBA routine will improve reliability, performance, and security of your PostgreSQL deployments.