# How to restore PostgreSQL from a self-managed-postgres backup Source: https://docs.quake.ai/docs/automation/how-to/restore-postgres-from-backup Markdown: https://docs.quake.ai/docs/automation/how-to/restore-postgres-from-backup.md --- # How to restore PostgreSQL from a self-managed-postgres backup Copy a scheduled `pg_dumpall` from a running [self-managed-postgres](/resources/iac-templates/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](/docs/network/how-to/ssh-bastion-access) for `ProxyJump` setup. Restoring a dump back onto the instance that produced it proves the dump file is readable. It leaves untested the recovery path you would use after you lose that instance. Run the drill against a separate scratch instance so the test follows that recovery path. - A running [self-managed-postgres](/resources/iac-templates/self-managed-postgres) deployment with at least one completed backup (run `/usr/local/bin/pg-backup.sh` on the source if the cron has not fired yet) - A jump host that routes to the private subnet (bastion with a floating IP, VPN, or another instance on the same network). See [SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access) - Enough quota for one additional scratch instance during the drill 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: ```bash 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: ```bash 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: ```bash 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](/resources/iac-templates/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](/resources/deployments/deploy-self-managed-postgres-template) 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: ```bash 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: ```bash 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: ```bash sudo -u postgres psql -c "\l" sudo -u postgres psql -c "\du" ``` Compare row counts for a known table against the source instance: ```bash 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: ```bash 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: ```bash 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](/resources/iac-templates/self-managed-postgres): the template this guide restores from - [Deploy the self-managed PostgreSQL template with OpenTofu](/resources/deployments/deploy-self-managed-postgres-template): full deployment walkthrough, including the scratch-instance pattern used in Step 2 - [SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access): `ProxyJump` for the private-address SSH and `scp` steps - [How to emulate a branchable Postgres workflow](/docs/automation/how-to/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](/docs/compute/how-to/backup-and-restore): instance-level snapshot backups, for comparison with this database-level approach - [Block storage how-to guides](/docs/block/how-to): volume snapshots as an alternative, instance-level recovery mechanism