How to Configure PostgreSQL Point-in-time Recovery (pitr) with Wal Archiving and Pg_basebackup on Ubuntu Server

ByAkensai

How to Configure PostgreSQL Point-in-time Recovery (PITR) with WAL Archiving and pg_basebackup on Ubuntu Server is one of the most valuable database administration skills you can have. Data loss is a real risk for any production system. Whether a developer runs a bad DELETE query or a disk fails unexpectedly, you need a way to roll your database back to an exact moment in time. PostgreSQL’s PITR feature gives you exactly that capability. It combines a full base backup with a continuous stream of Write-Ahead Log (WAL) files. Together, they let you restore your database to any point in its history. In this tutorial, you’ll set up WAL archiving, create a base backup using pg_basebackup, and then walk through a full recovery scenario. By the end, you’ll have a working PITR setup on your Ubuntu server.

Prerequisites for PostgreSQL Point-in-Time Recovery Setup

Before you start, make sure your environment meets these requirements.

Software and access:

  • Ubuntu Server 20.04 or 22.04 (fresh or existing install)
  • PostgreSQL 14 or later installed and running
  • Root or sudo access to the server
  • A separate archive directory (local path or remote mount)

Assumed knowledge:

  • Basic Linux command-line skills
  • Familiarity with PostgreSQL basics (connecting, running queries)
  • Understanding of file permissions on Linux

Estimated time: 45–60 minutes

You’ll also want enough disk space for your archive directory. WAL files accumulate quickly on busy databases. A good rule of thumb is to allocate at least twice your database size for the archive. Check the official PostgreSQL documentation on continuous archiving for deeper background before you begin.

How to Configure PostgreSQL PITR with WAL Archiving and pg_basebackup

For a related walkthrough, see: How to Install Proxmox on Ubuntu

Follow these steps carefully. Each one builds on the last.

Step 1: Create the WAL archive directory

First, create a directory where PostgreSQL will store archived WAL segments. The postgres user must own it.

sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive
sudo chmod 700 /var/lib/postgresql/wal_archive

Step 2: Edit the PostgreSQL configuration file

Open the main configuration file. The path depends on your PostgreSQL version.

sudo nano /etc/postgresql/14/main/postgresql.conf

Find and update these settings. If a line is commented out, remove the # symbol.

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 60

The archive_command copies each WAL file to your archive directory. The %p is the full path to the WAL file. The %f is just the filename. The archive_timeout forces a WAL switch every 60 seconds. This limits how much data you can lose between archives.

Step 3: Restart PostgreSQL to apply changes

sudo systemctl restart postgresql

Verify the service is running cleanly.

sudo systemctl status postgresql

Step 4: Confirm WAL archiving is working

Connect to PostgreSQL and force a WAL switch.

sudo -u postgres psql -c "SELECT pg_switch_wal();"

Then check the archive directory for new files.

ls -lh /var/lib/postgresql/wal_archive/

You should see at least one WAL file. If the directory is empty, double-check your archive_command and file permissions.

Step 5: Take a base backup with pg_basebackup

Now create a full base backup. This is your recovery starting point.

sudo -u postgres pg_basebackup 
  -D /var/lib/postgresql/base_backup 
  -Ft 
  -z 
  -P 
  -Xs 
  -R

Here’s what each flag does:

  • -D , sets the output directory
  • -Ft , creates a tar format backup
  • -z , compresses the output
  • -P , shows progress
  • -Xs , streams WAL during backup
  • -R , writes a recovery configuration file

Step 6: Record the backup timestamp

Note the exact time your backup completed. You’ll use this as a reference point for recovery.

date --utc +"%Y-%m-%d %H:%M:%S"

Save this output somewhere safe. You’ll need it when setting your recovery target.

Step 7: Simulate data loss and perform recovery

To test your setup, simulate an accidental data loss. First, create a test table and insert some rows.

sudo -u postgres psql -c "CREATE TABLE test_recovery (id serial, note text);"
sudo -u postgres psql -c "INSERT INTO test_recovery (note) VALUES ('before drop');"

Record the current time. Then drop the table.

sudo -u postgres psql -c "SELECT now();"
sudo -u postgres psql -c "DROP TABLE test_recovery;"

Now stop PostgreSQL and replace the data directory with your base backup.

sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main.broken
sudo mkdir /var/lib/postgresql/14/main
sudo tar -xzf /var/lib/postgresql/base_backup/base.tar.gz 
  -C /var/lib/postgresql/14/main
sudo chown -R postgres:postgres /var/lib/postgresql/14/main
sudo chmod 700 /var/lib/postgresql/14/main

Create a recovery signal file and set your recovery target time. Edit postgresql.conf and add these lines.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2024-06-15 10:30:00'
recovery_target_action = 'promote'

Replace the timestamp with the time just before you dropped the table. Then create the recovery signal file.

sudo -u postgres touch /var/lib/postgresql/14/main/recovery.signal

Start PostgreSQL and watch the logs.

sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-14-main.log

PostgreSQL will replay WAL files up to your target time and then promote itself. Check that your table came back.

sudo -u postgres psql -c "SELECT  FROM test_recovery;"

Troubleshooting Common PostgreSQL PITR Errors

Archive directory is empty after restart
Check the archive_command output manually. Run the command yourself, replacing %p and %f with real values. Fix any permission errors on the archive directory.

Recovery stops before the target time
This usually means WAL files are missing from the archive. Make sure your archive_command was running before you took the base backup. Also confirm the archive directory path in restore_command is correct.

PostgreSQL won’t start after restore
Check /var/log/postgresql/postgresql-14-main.log for errors. A common cause is wrong ownership on the data directory. Run sudo chown -R postgres:postgres /var/lib/postgresql/14/main to fix it.

pg_basebackup fails with “could not connect to server”
Make sure your pg_hba.conf allows replication connections from localhost. Add this line if it’s missing.

local   replication     postgres                                trust

Then reload PostgreSQL with sudo systemctl reload postgresql.

For more detail on recovery configuration options, see the PostgreSQL recovery configuration reference.

Conclusion

You now know how to configure PostgreSQL Point-in-Time Recovery (PITR) with WAL Archiving and pg_basebackup on Ubuntu Server from start to finish. You set up WAL archiving, created a base backup, simulated data loss, and recovered to a specific point in time. This setup protects you against accidental deletions, bad migrations, and other data disasters. As a next step, consider automating your base backups with a cron job and shipping WAL archives to remote storage like S3 for offsite protection. You might also explore tools like pgBackRest or Barman, which wrap these concepts in a more manageable interface for larger environments.

Similar Posts