How to restore PostgreSQL from a self-managed-postgres backup

Copy a scheduled pg_dumpall from a running self-managed-postgres instance onto a second, disposable instance, restore it with psql, and confirm databases, roles, and row counts match. The source instance stays untouched.

The template allocates no floating IP. Reach both private addresses through a bastion, VPN, or jump instance on the same network. The commands below use ssh -J / scp -J; see SSH bastion access into a private subnet for ProxyJump setup.

Replace JUMP_HOST with the jump host's reachable address (floating IP or VPN hostname), SOURCE_PRIVATE_IP and SCRATCH_PRIVATE_IP with the two instance private addresses, and YOUR_PRIVATE_KEY_PATH with the key that matches key_name on both templates. The default image is Ubuntu 24.04, so the SSH user is ubuntu.

What the template already gives you#

self-managed-postgres cloud-init (cloud-init/postgres.yaml) installs two recovery mechanisms on first boot, both scoped to the local data volume:

• Scheduled logical backups. /usr/local/bin/pg-backup.sh runs on the backup_schedule cron (0 2 * * * by default) from /etc/cron.d/pg-backup. It writes a gzip-compressed pg_dumpall (roles and databases on that instance) to /var/lib/postgresql/backups/pgdumpall-TIMESTAMP.sql.gz.
• Local WAL archiving. PostgreSQL sets archive_mode = on and copies each completed WAL segment to /var/lib/postgresql/wal_archive on the same data volume.

Both mechanisms write to the same Cinder volume the running database uses. They cover logical mistakes (a bad DELETE, a dropped table, an application bug) while that volume is still attached. Copy backups off the instance before you rely on them for anything beyond same-volume recovery.

Step 1: Identify the backup to restore#

SSH to the source instance through the jump host, list the dumps, and pick one:

ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@SOURCE_PRIVATE_IP
ls -lh /var/lib/postgresql/backups/

If the directory is empty, the nightly cron has not run yet. Trigger one dump, then list again:

sudo /usr/local/bin/pg-backup.sh
ls -lh /var/lib/postgresql/backups/

Copy the target file to your workstation through the same jump host:

scp -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST \
  ubuntu@SOURCE_PRIVATE_IP:/var/lib/postgresql/backups/pgdumpall-TIMESTAMP.sql.gz .

Step 2: Provision a scratch restore target#

Deploy a second, disposable instance for the drill. Reuse the self-managed-postgres template in a separate working directory with a different instance_name and private_cidr, following Deploy the self-managed PostgreSQL template with OpenTofu through the tofu apply step. Set db_password to a throwaway value; you discard this instance when the drill finishes.

Confirm the scratch instance runs the same PostgreSQL major version as the source (pg_version in both terraform.tfvars files) so the dump format matches.

Step 3: Restore the dump#

Copy the backup file onto the scratch instance if you have not already, then SSH in:

scp -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST \
  pgdumpall-TIMESTAMP.sql.gz ubuntu@SCRATCH_PRIVATE_IP:/tmp/
ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@SCRATCH_PRIVATE_IP

On the scratch instance:

gunzip -c /tmp/pgdumpall-TIMESTAMP.sql.gz | sudo -u postgres psql

pg_dumpall output is plain SQL: psql replays CREATE ROLE, CREATE DATABASE, and per-database statements from the dump against the running server. Statements that collide with objects cloud-init already created (the default appuser role and appdb database) print errors. Expect those collisions; they do not mean the restore failed.

Step 4: Verify the restore#

Confirm the databases and roles from the source exist on the scratch instance:

sudo -u postgres psql -c "\l"
sudo -u postgres psql -c "\du"

Compare row counts for a known table against the source instance:

sudo -u postgres psql -d appdb -c "SELECT count(*) FROM YOUR_TABLE;"

Run the same query on the source instance and confirm the counts match (accounting for any writes that happened between the backup and the drill). Confirm the application role can authenticate and read data with the credentials the source uses:

PGPASSWORD='YOUR_APP_PASSWORD' psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT 1;"

Point-in-time recovery: what local WAL archiving covers#

self-managed-postgres declares a wal_archive_bucket variable. The current cloud-init does not wire that variable into archive_command: WAL segments land only in /var/lib/postgresql/wal_archive on the same Cinder volume as the database. Replaying those segments with pg_basebackup and recovery.signal lets you roll forward to a point in time after a logical mistake, as long as the volume that holds both the data directory and the WAL archive survives.

That local archive cannot recover you after you lose the volume, because the base data and the WAL segments live in the same place. Offsite point-in-time recovery requires shipping WAL segments to a separate location (Object Storage, for example) by changing archive_command in the template's cloud-init. Treat the current WAL archiving as a same-instance recovery aid.

Make the drill repeatable#

Re-run this drill:

• On a monthly cadence
• After any pg_version upgrade, since a dump taken on one major version may need pg_upgrade-style handling to restore cleanly on another
• After any change to pg-backup.sh, backup_schedule, or the cloud-init script that produces the backups

Step 5: Tear down the scratch instance#

Destroy the scratch deployment from its working directory once you confirm the restore:

tofu destroy

Type yes to confirm. Tear down the scratch instance after you verify the restore: it holds a copy of the source databases and consumes quota once verification finishes.

See also#

• Self-Managed PostgreSQL: the template this guide restores from
• Deploy the self-managed PostgreSQL template with OpenTofu: full deployment walkthrough, including the scratch-instance pattern used in Step 2
• SSH bastion access into a private subnet: ProxyJump for the private-address SSH and scp steps
• How to emulate a branchable Postgres workflow: use the same restore mechanics to spin up isolated database copies for development
• How to back up and restore a Quake AI VM: instance-level snapshot backups, for comparison with this database-level approach
• Block storage how-to guides: volume snapshots as an alternative, instance-level recovery mechanism