PostgreSQL Physical Backups: pg_basebackup and WAL Archiving

PostgreSQL Physical Backups: pg_basebackup and WAL Archiving

Why Physical Backups Matter

A physical backup copies the actual data files that make up a PostgreSQL cluster—the base directory, tablespace files, and Write-Ahead Log (WAL) segments. Unlike a logical backup (pg_dump), a physical backup captures the cluster at the binary level, making it the foundation of Point-In-Time Recovery (PITR). PITR lets you restore a database to any precise moment in the past, not just the moment a backup was taken. This capability is essential for database administrators responding to accidental data deletion, application errors, or corruption.

Understanding WAL and WAL Archiving

PostgreSQL records every change to data in the Write-Ahead Log before applying it to the actual data files. WAL segments are fixed-size files (16 MB by default) stored in `pg_wal/`. The server reuses old segments once they are no longer needed for crash recovery. To support PITR, you must preserve every WAL segment generated after your base backup by archiving them to a safe location before the server recycles them.

WAL archiving requires three settings in `postgresql.conf`:

• `wal_level = replica` — ensures enough information is written to WAL to support standby servers and archiving.
• `archive_mode = on` — enables the archiving mechanism.
• `archive_command` — a shell command PostgreSQL runs to copy each completed WAL segment to your archive location. A common example for a local directory:

```

archive_command = 'cp %p /mnt/wal_archive/%f'

```

Here `%p` is the full path to the source WAL file and `%f` is just the filename. The command must return exit code 0 only on success. If the command fails, PostgreSQL retries it and keeps the WAL segment safe until archiving succeeds.

After editing `postgresql.conf`, reload the server with `pg_ctl reload` or `SELECT pg_reload_conf();`. Force a WAL switch with `SELECT pg_switch_wal();` to confirm the first segment appears in your archive directory.

Taking a Base Backup with pg_basebackup

`pg_basebackup` connects to the running server using the replication protocol and copies the entire data directory in a consistent state. The command used in this lab is:

```bash

pg_basebackup -Ft -z -P -Xs -D /path/to/backup_dir

```

| Flag | Meaning |

|------|---------|

| `-Ft` | Output format: tar |

| `-z` | Compress with gzip |

| `-P` | Show progress |

| `-Xs` | Stream WAL during the backup using a second connection |

| `-D` | Destination directory |

The backup produces `base.tar.gz` (the data directory) and `pg_wal.tar.gz` (WAL segments generated during the backup). Inside `base.tar.gz` is a `backup_label` file recording the backup start LSN (Log Sequence Number), the timeline, and the start time. Record this information in your lab log—you need it to confirm recovery.

The replication connection requires a user with the `REPLICATION` privilege, and `pg_hba.conf` must permit it. Verify before running the backup.

Simulating Ongoing Transactions

After the backup, insert rows at intervals to create a known transaction history. A simple bash loop:

```bash

for i in $(seq 1 10); do

psql -c "INSERT INTO test_table(note, ts) VALUES ('batch_$i', now());"

sleep 30

done

```

Record the timestamp after each batch. You will use one of these timestamps as your recovery target.

Point-In-Time Recovery Procedure

Step 1 — Stop the server and remove the data directory.

Stop PostgreSQL cleanly with `pg_ctl stop -m fast`. Move or delete the existing data directory. Never skip a clean shutdown if the server is reachable; it reduces the WAL replay needed and confirms no clients are connected.

Step 2 — Restore the base backup.

Extract `base.tar.gz` into the new data directory. Extract `pg_wal.tar.gz` into `pg_wal/` inside that directory.

Step 3 — Copy archived WAL segments.

Copy your archived WAL segments into `pg_wal/` so the recovery process can replay them. Alternatively, configure `restore_command` to pull them on demand.

Step 4 — Create the recovery signal file.

In PostgreSQL 12 and later, create an empty file named `recovery.signal` in the data directory. Its presence tells the server to enter recovery mode at startup.

Step 5 — Set recovery parameters in postgresql.conf.

Add or uncomment:

```

restore_command = 'cp /mnt/wal_archive/%f %p'

recovery_target_time = '2025-06-10 14:35:00'

recovery_target_action = 'promote'

```

Set `recovery_target_time` to a timestamp that falls between two known insert batches.

Step 6 — Start the server and verify.

Start PostgreSQL. Watch the server log for messages confirming WAL replay and the recovery stopping at the target time. Connect and run `SELECT COUNT(*) FROM test_table;` — the count must exactly match the expected number of rows inserted before your target timestamp.

Safety and Best Practices

• Never test recovery on production. Always practice on a dedicated lab or staging system.
• Permissions matter. The archive directory must be readable and writable by the `postgres` OS user only (`chmod 700`). Loose permissions expose backup data.
• Verify your archive_command. A silently failing command leaves you without a recoverable archive. Check the archive directory after forcing a WAL switch.
• Label your backups. Record the start time, LSN, and PostgreSQL version in a backup manifest or lab log. You cannot safely restore a base backup taken with a different major PostgreSQL version.
• Test restores regularly. A backup you have never restored is an untested backup.