Backup and Restore

Logical dumps and physical backups

pg_dump creates logical backups of a database (or selected tables) that pg_restore can restore, useful for migrations and smaller databases. Production systems also use physical backups with WAL archiving for point-in-time recovery (pgBackRest, Barman, or the managed provider's automated backups). Whatever you use, test restores regularly, keep backups in another location or account, and measure how long a restore takes.

Dump, drop and restore a table, run

I ran this with Python 3, psycopg 3.3 and PostgreSQL 16.2, using separate connections to act as concurrent sessions. pg_dump writes the products table in custom format; after the table is dropped, pg_restore brings it back with its three rows.

import os, subprocess
uri, bindir = os.environ["DEMO_URI"], os.environ["PG_BIN"]
dump = subprocess.run([f"{bindir}/pg_dump", "--format=custom", "--table=products", "--dbname", uri],
                      capture_output=True, check=True).stdout
print("dump size in bytes is larger than 1000:", len(dump) > 1000)
psql = lambda sql, db=uri: subprocess.run([f"{bindir}/psql", "-X", "-qAt", "-d", db, "-c", sql], capture_output=True, text=True).stdout.strip()
psql("DROP TABLE products CASCADE")
print("after DROP, products exists:", psql("SELECT to_regclass('products') IS NOT NULL"))
subprocess.run([f"{bindir}/pg_restore", "--dbname", uri], input=dump, check=True, capture_output=True)
print("after restore:", psql("SELECT string_agg(name, ', ' ORDER BY id) FROM products"))

Output:

dump size in bytes is larger than 1000: True
after DROP, products exists: f
after restore: Notebook, Fountain pen, Desk lamp

Schedule restore drills

Restore into a scratch environment on a schedule and check the data; it proves both the backup and your runbook.

त्वरित जाँच: What is the most important practice for backups?

• Using the largest compression
• Never deleting old backups
• Storing them only on the database server
• Regularly testing restores