A backup is a question. A restore answers it.

Keep the first exercise deliberately small

This exercise creates a toy notes database and one attachment, captures both, then restores them into a new database and directory. Use a disposable Linux machine with PostgreSQL 17 server and matching client utilities, Bash and GNU coreutils. It must contain no production data and have no connection to production applications, outbound mail or scheduled jobs. Use only the archive created in this exercise.

The commands assume a preconfigured, non-superuser database role named restore_lab, permitted to create test databases, and an OS login of the same name that can authenticate locally. The PostgreSQL socket is /var/run/postgresql on port 5432. Have the administrator prepare that account in the disposable environment; this guide does not modify database access rules. Check the endpoint before creating anything:

psql -h /var/run/postgresql -p 5432 -U restore_lab -d postgres -c '\conninfo' &&
pg_dump --version &&
pg_restore --version

Stop if the host, account or version is unexpected. Keep clients and server on the same major version for this first drill. pg_dump cannot dump a server newer than its own major version, and an older restore target is not generally guaranteed compatible. See pg_dump version limits. Run each block separately and stop on an error. The && guards prevent later commands in a pasted block from running after a failure; never continue to the next block after a failed database or directory creation.

Create a record and its matching file

The names offvps_restore_source and offvps_restore_target must be unused. An existing database is a reason to stop and choose a new isolated exercise, not a reason to drop it. Create a fresh filesystem workspace and the source database:

umask 077 &&
lab=$(mktemp -d "$PWD/offvps-restore.XXXXXX") &&
mkdir "$lab/source" "$lab/source/uploads" "$lab/bundle" "$lab/restore" &&
createdb -h /var/run/postgresql -p 5432 -U restore_lab \
  --template=template0 offvps_restore_source

The fresh workspace avoids writing over an existing directory. mktemp creates a unique directory; createdb creates a new database from the selected template. Save these two markers:

psql -X -v ON_ERROR_STOP=1 -h /var/run/postgresql -p 5432 \
  -U restore_lab -d offvps_restore_source \
  -c "CREATE TABLE notes (
        id integer PRIMARY KEY,
        body text NOT NULL,
        attachment text NOT NULL
      );
      INSERT INTO notes VALUES (1, 'restore-marker-01', 'marker.txt');" &&
printf '%s\n' 'restore-marker-01' > "$lab/source/uploads/marker.txt"

The record says which file belongs to the note, and both contain the same marker. This makes a missing attachment or a mismatched copy visible. -X avoids personal psql startup settings and ON_ERROR_STOP stops on an SQL error; see psql scripting options. There is no application writing to this toy dataset.

Capture the database and files as one recovery set

pg_dump -h /var/run/postgresql -p 5432 -U restore_lab \
  --format=custom --file="$lab/bundle/notes.dump" offvps_restore_source &&
cp -a "$lab/source/uploads" "$lab/bundle/uploads" &&
pg_restore --list "$lab/bundle/notes.dump"

The custom-format archive is read by pg_restore; a plain SQL dump follows a different restore procedure. Listing the archive helps confirm the expected table and data entries are present. A consistent database dump does not automatically synchronize external uploads. For a real app, use its documented maintenance or write-pause procedure so that the file tree and database describe the same point in time. The toy exercise is already quiet.

Record the database version, application release, capture start/end and file path beside the bundle. Keep real credentials separately under appropriate access controls. A per-database dump does not capture global roles and tablespaces; a real recovery plan must account for those as well. The scope is described in PostgreSQL’s SQL dump guide.

Restore into new targets without cleaning an existing one

createdb -h /var/run/postgresql -p 5432 -U restore_lab \
  --template=template0 offvps_restore_target &&
pg_restore -h /var/run/postgresql -p 5432 -U restore_lab \
  --dbname=offvps_restore_target --no-owner --no-acl \
  --single-transaction "$lab/bundle/notes.dump" &&
mkdir "$lab/restore/uploads" &&
cp -a "$lab/bundle/uploads/." "$lab/restore/uploads/"

The target database is created empty. The restore intentionally omits --clean, which could remove existing objects. --single-transaction makes this small database restore succeed as a unit or stop without applying its partial changes; it cannot be combined with parallel jobs. --no-owner and --no-acl simplify ownership for this test role, so this drill does not validate the application’s production roles or permissions. See pg_restore.

The file copy targets the new restore directory, leaving the source intact. GNU cp -a attempts to preserve file attributes; inspect ownership and access for the account that will run a real application. Copying bytes is not a test of application permissions. See cp’s archive option.

Check the relationship, not just exit codes

psql -X -h /var/run/postgresql -p 5432 -U restore_lab \
  -d offvps_restore_target -c "SELECT id, body, attachment FROM notes;" &&
cat "$lab/restore/uploads/marker.txt"

Expected results are one row with ID 1, body restore-marker-01 and attachment marker.txt, plus a file containing restore-marker-01. Check that the source still contains its original row and file. For your real app, also point an isolated instance at the restored database and upload directory, open representative records, fetch an attachment and test the intended login permissions. Keep callbacks, mail and jobs contained while doing so.

Write down what you proved and what remains

The expected outputs above are exercise criteria, not results already observed by OffVPS. Because the first drill keeps all files on one machine, it provides no protection from that machine’s loss. A real plan needs a protected copy in a separate failure domain, usable decryption keys, retention and a tested retrieval path. Review the exact disposable database names and directory before later cleanup; keep production outside that process.

Repeat the exercise after important changes and record failures as carefully as successes. An optional backup service does not prove your application’s recovery time. Continue with planning a scheduled task when you are ready to automate capture, while retaining a separate restore drill.