How do backup and restore work in PostgreSQL using pg_dump and Point-in-Time Recovery (PITR)?

Explanation & Concept

PostgreSQL offers two complementary backup approaches:

1. Logical Backups (`pg_dump` / `pg_restore`): Dumps SQL schema and data as portable scripts or custom compressed files. Ideal for migrations and smaller databases. 2. Physical Backups + WAL Archiving (PITR): Backs up raw database cluster files (`pg_basebackup`) combined with continuously archived WAL logs. Enables restoration to an exact historical microsecond in time before an incident occurred.

Code Example

bash

-- 1. Create a custom-format compressed backup
pg_dump -h localhost -U postgres -F c -b -v -f prod_backup.dump mydatabase

-- 2. Restore in parallel across 4 CPU cores
pg_restore -h localhost -U postgres -d newdatabase -j 4 prod_backup.dump

How It Works & Key Takeaways

Custom format (`-F c`) is flexible because `pg_restore` can selectively filter tables, run multi-threaded parallel restores (`-j`), and reorder index creation after table inserts finish.

Backup & RestoreAdvanced

1Question 1 of 2

bash

How do backup and restore work in PostgreSQL using pg_dump and Point-in-Time Recovery (PITR)?

Explanation & Concept

PostgreSQL offers two complementary backup approaches:

Code Example

bash

-- 1. Create a custom-format compressed backup
pg_dump -h localhost -U postgres -F c -b -v -f prod_backup.dump mydatabase

-- 2. Restore in parallel across 4 CPU cores
pg_restore -h localhost -U postgres -d newdatabase -j 4 prod_backup.dump

How It Works & Key Takeaways

Custom format (`-F c`) is flexible because `pg_restore` can selectively filter tables, run multi-threaded parallel restores (`-j`), and reorder index creation after table inserts finish.