# Backup and Restore — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/o-backup

> Untested backups are wishes.

## 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.

```python
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.

**Quiz:** What is the most important practice for backups?

- [ ] Using the largest compression
- [ ] Never deleting old backups
- [ ] Storing them only on the database server
- [x] Regularly testing restores

*Answer:* Regularly testing restores. A backup is only as good as its restore.