Backups, Replication & Maintenance

pg_dump and point-in-time recovery, streaming replication, VACUUM and the settings that matter.

Running PostgreSQL in production means planning for failures before they happen. This lesson is an overview of the operational side; the commands are standard PostgreSQL tools (this lesson has no session because they run against a whole server, outside SQL).

Maintenance and configuration

Copying the data directory of a running server with cp Use pg_basebackup (or pg_dump), or stop the server first

Files copied while PostgreSQL writes to them are inconsistent; without the matching WAL the copy may not start or may silently contain corrupt data.

What is point-in-time recovery?

Restoring a physical base backup and then replaying archived WAL up to a chosen moment (e.g. just before an accidental DROP TABLE).

What is the difference between streaming and logical replication?

Streaming replication copies the whole cluster byte for byte (same major version) to standbys. Logical replication copies row changes of selected tables and works across versions and with different table sets on the subscriber.