Backup, Restore & Replication

Disks fail, people run DELETE without WHERE, and ransomware encrypts servers. A database without a tested backup is a database you will lose one day. PostgreSQL gives you two kinds of backup, and it can also keep live copies of your data on other servers through replication.

In this lesson you will make logical backups with pg_dump, restore them, learn how physical backups and point-in-time recovery work, and get an overview of streaming and logical replication. All commands run in a terminal on a real server, not in the browser.

Logical backups with pg_dump

pg_dump connects like any client and writes the database out as SQL commands or an archive. It takes a consistent snapshot, so the database stays fully usable while it runs. The custom format (-Fc) is compressed and is the most flexible choice:

pg_dump -U postgres -d shop -Fc -f shop_2025-06-01.dump
pg_dump -U postgres -d shop -f shop.sql
pg_dump -U postgres -d shop --schema-only -f shop_schema.sql
pg_dump -U postgres -d shop -t orders -t order_items -Fc -f orders_only.dump

The first line makes a custom-format archive, the second a plain SQL file you can read in any editor, the third only the table definitions, and the last only two tables. Add -Fd -j 4 for a directory-format dump written by four parallel jobs, which is much faster on large databases.

pg_dump copies one database. Roles and tablespaces belong to the whole cluster, so back them up with pg_dumpall --globals-only -f globals.sql. Running pg_dumpall without that option dumps every database as one plain SQL file.

Restoring

Plain SQL dumps are restored with psql; custom and directory archives with pg_restore, which can restore everything or only selected parts:

createdb -U postgres shop_restored
pg_restore -U postgres -d shop_restored -j 4 shop_2025-06-01.dump

pg_restore -U postgres -d shop_restored -t products --clean shop_2025-06-01.dump
pg_restore -l shop_2025-06-01.dump

psql -U postgres -d shop_restored -f shop.sql

-j 4 restores with four parallel jobs, -t products --clean replaces just one table, and -l lists what an archive contains without restoring anything. Restoring into an empty, newly created database is the safest pattern; you can rename databases afterwards.

PostgreSQL 18 added pg_dump --statistics, which also saves the planner statistics so that a restored database plans queries well straight away, and pg_upgrade now keeps statistics when upgrading to a new major version.

Physical backups and point-in-time recovery

A logical dump is a snapshot from one moment. If you dump every night at 2 a.m. and the disk dies at 6 p.m., you lose 16 hours of orders. Physical backups solve this. PostgreSQL writes every change to the WAL (write-ahead log) before changing the data files. If you keep:

1. a base backup, a copy of the data directory made with pg_basebackup, and
2. every WAL file since then, archived with the archive_command or archive_library setting,

you can restore the base backup and replay the WAL up to any moment you choose, for example "one second before the bad DELETE". This is point-in-time recovery (PITR).

pg_basebackup -U replicator -h db1.internal -D /backups/base_2025-06-01 -Ft -z -P

Since PostgreSQL 17, pg_basebackup --incremental can copy only the blocks changed since an earlier backup, and pg_combinebackup joins a full backup and its incrementals back into one. In practice, most teams use a tool such as pgBackRest or Barman, or their cloud provider's managed backups, which automate base backups, WAL archiving, retention and restore tests.

Replication overview

Replication keeps copies of a database on other servers, called standbys or replicas. It protects against a server failing and can spread read queries over several machines. It is not a backup: a DROP TABLE on the primary is replicated to every standby within seconds.

• Streaming replication (physical) sends the WAL from the primary to standbys, which replay it and stay an exact copy of the whole cluster. Standbys can answer read-only queries (hot standby). Replication is asynchronous by default; synchronous replication makes a commit wait until a standby has the data, so no committed transaction is lost if the primary dies, at the cost of slower commits.
• Logical replication sends row changes for chosen tables using publications on the source and subscriptions on the target. It can replicate between different major versions, which makes it useful for upgrades with little downtime, and to feed selected tables into another database.

-- on the source database
CREATE PUBLICATION shop_pub FOR TABLE orders, order_items;

-- on the target database (the tables must already exist there)
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=db1.internal dbname=shop user=replicator password=...'
  PUBLICATION shop_pub;

When a primary fails, a standby is promoted to become the new primary, either by hand (pg_ctl promote or SELECT pg_promote();) or by a high-availability manager such as Patroni. You can watch replication on the primary in the pg_stat_replication view.

Copying data inside the database

Before a risky change, a quick copy of a table inside the same database is a handy safety net. It is not a backup (it lives on the same disk), but it lets you compare or undo a change in seconds:

CREATE TABLE products_before_price_change AS TABLE products;

UPDATE products SET price = price * 1.10 WHERE category = 'Accessories';

SELECT p.product_name, b.price AS before, p.price AS after
FROM products AS p
JOIN products_before_price_change AS b USING (product_id)
WHERE p.category = 'Accessories'
ORDER BY p.product_id
LIMIT 3;

Query OK
Query OK, 6 rows affected
product_name        | before  | after
--------------------+---------+--------
Wireless Mouse      | 799.00  | 878.90
Mechanical Keyboard | 3499.00 | 3848.90
USB-C Hub           | 1899.00 | 2088.90
(3 rows)

CREATE TABLE ... AS TABLE products copies all rows but not indexes, constraints or defaults. Drop the copy once you are sure the change is right.

Common mistakes

• Only keeping backups on the same server. Copy them to another machine or cloud storage, and keep several days of history.
• Never testing a restore. Many teams discover a broken backup on the worst day.
• Forgetting roles. pg_dump does not include them; use pg_dumpall --globals-only as well.
• Treating replication as backup. Mistakes are replicated too. You need both.
• Committing passwords in backup scripts. Use a .pgpass file or environment-specific secrets.

Key points

• pg_dump makes consistent logical backups of one database; -Fc custom format is the flexible default.
• pg_restore restores custom and directory archives, in parallel and selectively; psql -f restores plain SQL dumps.
• pg_dumpall --globals-only saves roles and tablespaces.
• Base backups plus archived WAL allow point-in-time recovery; PostgreSQL 17 added incremental base backups.
• Streaming replication keeps exact standby copies; logical replication copies chosen tables via publications and subscriptions.
• Replication protects against server failure, backups protect against mistakes; test restores regularly.

pg_dump -U postgres -d shop -Fc -f shop.dump
pg_dumpall -U postgres --globals-only -f globals.sql
createdb -U postgres shop_test
pg_restore -U postgres -d shop_test -j 2 shop.dump
pg_restore -l shop.dump