How to Back Up and Restore Databases in PostgreSQL

Understanding Database Backups

A backup is a copy of your database at a specific point in time. If something goes wrong, you can restore to that saved version. There are two main types: logical backups and physical backups.

Logical Backups

A logical backup saves database objects and data—tables, rows, schemas, functions, and so on—by exporting SQL statements. This makes it human-readable and portable to newer or different versions of PostgreSQL. The trade-off is speed: running all those SQL queries takes time.

Physical Backups

A physical backup saves the actual PostgreSQL data files stored on disk, creating an exact copy of the database. This allows faster backup and restore because you avoid the overhead of running SQL commands. However, the raw data cannot easily be ported to a different version. Physical backups are useful for larger databases.

Think of it like building a house identical to one you already have. The logical way is to get the blueprint and learn where everything is, then rebuild. The physical way is to take the entire house and move it to a new location.

Setting Up the Environment

Start PostgreSQL. The environment has two machines; you'll work with the second one at the end. Open VS Code and then open the Integrated Terminal by clicking the three lines, then Terminal, New Terminal. Maximize the terminal for more space.

PostgreSQL is already running. Connect to the restaurant database:

sudo -u postgres psql -d restaurant

You'll see two tables. Clear the console:

\! clear

Creating a Logical Backup

Exit the PostgreSQL shell:

\q

Create your first logical backup using pg_dump. Redirect the output to a file:

sudo -u postgres pg_dump restaurant > /home/prepare.sh/backups/restaurant-backup.sql

Open the file to see the SQL statements. They are fully human-readable and can be run on a completely different server, even a different version.

Using Custom Format for Production

In production, avoid plain SQL files because they are heavier. Use the custom format with the -Fc flag:

sudo -u postgres pg_dump -Fc restaurant > /home/prepare.sh/backups/restaurant-backup.dump

The dump file is compressed and smaller. More importantly, pg_restore can restore it in parallel across multiple cores, which matters for databases that are gigabytes or larger. In production, always use the -Fc flag.

Destroying and Restoring the Database

Clear the console. Drop the database:

sudo -u postgres psql -c "DROP DATABASE restaurant;"

Verify it's gone by listing databases:

sudo -u postgres psql -c "\l"

The restaurant database no longer exists. To restore, first create a new database:

sudo -u postgres psql -c "CREATE DATABASE restaurant;"

Now restore from the SQL file using the input redirection operator:

sudo -u postgres psql -d restaurant < /home/prepare.sh/backups/restaurant-backup.sql

Verify the tables are restored:

sudo -u postgres psql -d restaurant -c "SELECT * FROM menu;"
sudo -u postgres psql -d restaurant -c "SELECT * FROM orders;"

Restoring from a Dump File

Drop the database again and recreate it:

sudo -u postgres psql -c "DROP DATABASE restaurant;"
sudo -u postgres psql -c "CREATE DATABASE restaurant;"

Use pg_restore instead of the shell redirection operator:

sudo -u postgres pg_restore -d restaurant /home/prepare.sh/backups/restaurant-backup.dump

Verify the data is restored:

sudo -u postgres psql -d restaurant -c "SELECT * FROM menu;"
sudo -u postgres psql -d restaurant -c "SELECT * FROM orders;"

Point-in-Time Recovery (PITR)

If you back up at midnight and a disaster happens at 3:00 PM, you lose 15 hours of data changes. Point-in-time recovery (PITR) solves this by keeping a base backup (a physical backup) and tracking timestamp changes in a write-ahead log (WAL). The WAL contains commit times and the changes that happened. When you give PITR a timestamp, it replays the WAL entries one by one, comparing commit times. When it reaches a timestamp greater than yours, it stops, restoring the database to that exact point.

Lab Exercise

On the second machine, there is a library database that has been heavily damaged. Use what you've learned to restore it, either from the SQL file or the dump file.