Restore PostgreSQL

A restore replaces the current database entirely. Schedule a maintenance window, confirm which Compose project you are pointing at, and take a fresh pre restore backup before you start.

Migrations are forward only. Starting a newer application image against an older restored database applies the pending migrations, which is fine, but starting an older image against a newer database is not supported. Restore the code revision that matches the archive. See Upgrade and roll back.

Restore into a running stack

set -eu
restore_file="$HOME/asobeast-backups/asobeast-YYYYMMDDTHHMMSSZ.dump"
test -s "$restore_file"
docker compose exec -T postgres pg_restore --list < "$restore_file" > /dev/null
docker compose stop web api
docker compose exec -T postgres dropdb --username asobeast --if-exists asobeast
docker compose exec -T postgres createdb --username asobeast asobeast
docker compose exec -T postgres pg_restore --username asobeast --dbname asobeast --clean --if-exists --no-owner --exit-on-error < "$restore_file"
docker compose up -d --wait
curl --fail --silent --show-error http://127.0.0.1:3000/api/health
curl --fail --silent --show-error http://127.0.0.1:3000/api/backend/health
docker compose exec -T postgres psql --username asobeast --dbname asobeast --command 'select count(*) from "App";'
docker compose exec -T postgres psql --username asobeast --dbname asobeast --command 'select count(*) from "Keyword";'

The pg_restore --list at the top is the important line. It reads the archive before anything is dropped, so a corrupt file fails while the current database is still intact. Starting the stack applies any migrations the checked out application version requires.

Restore into a fresh host

Same sequence, with two steps in front.

Bring up an empty stack

Install asobeast normally and let it boot once, so the volumes and the schema exist. See Self host with Docker Compose.

Check out the matching code revision

Use the revision the archive was taken from, or a newer one. Never an older one.

Run the restore block above

Then verify as below.

Use a fresh AUTH_SECRET on the new host only if you accept that every session is invalidated. Account records come from the archive either way.

After a restore

Check three things, in this order.

Check	Where	Expected
Apps and keywords	The app list, or the two `psql` counts above	Matches what the instance had
Health	`/api/backend/health`	`status` is `ok`, `db` and `redis` are `up`
Collection	The queue dashboard, or `pipeline.lastDailyRunAt`	The next daily run enqueues normally

pipeline.lastDailyRunAt lives in Redis rather than in PostgreSQL, so after a restore onto a fresh Redis it starts empty and fills on the next run. That is expected and is not data loss. See Health checks and monitoring.

If the restore fails

Keep the applications stopped. Read docker compose logs postgres, then restore the pre restore archive you took at the start before reopening service.

Back up PostgreSQL

Where the archive comes from.

Troubleshooting

Symptoms after a restore.

Get started

Install

Concepts

Guides

Configuration

Operations

Reference

Restore PostgreSQL

Restore into a running stack

Restore into a fresh host

After a restore

If the restore fails

Back up PostgreSQL

Troubleshooting

​Restore into a running stack

​Restore into a fresh host

​After a restore

​If the restore fails

​Related

Back up PostgreSQL

Troubleshooting

Restore into a running stack

Restore into a fresh host

After a restore

If the restore fails

Related