By Shahid MalikRestic handles encryption, deduplication, and getting
Tabeer.ai's backups off the server — but it doesn't know anything about Postgres. The actual
database snapshot is pg_dump, and getting it right meant a couple of surprises worth writing
down, plus a restore test I ran against the real production dataset rather than trusting the
dump blindly.
Custom Format, Not Plain SQL
pg_dump --format=custom --file="$DUMP" "$DATABASE_URL"
--format=custom produces a compressed, non-human-readable archive instead of a plain .sql
file. Two reasons that matters here: it's meaningfully smaller (Tabeer.ai's ~5,000-question
database dumps to 728KB in custom format), and it enables pg_restore's selective restore —
individual tables, or --list/--use-list to restore in a specific order — which a plain SQL
dump can't do without editing the file by hand.
RDS Is VPC-Internal Only
First attempt was to run pg_dump straight from my own machine against the RDS endpoint. It hung
and timed out after 60 seconds. RDS in this setup has no public accessibility — it's only
reachable from inside its VPC, which means from the EC2 instance itself, not from anywhere
external. The fix was simply running pg_dump from the server over SSH, where Django's own
connection to the same database already proved connectivity works.
A smaller trap on the way there: source .env inside a non-interactive SSH command didn't
actually export DATABASE_URL into the dump command's environment — pg_dump fell back to
trying a local Unix socket and failed with No such file or directory. The fix was extracting the
value explicitly instead of relying on source to survive the SSH command boundary:
export DATABASE_URL=$(grep "^DATABASE_URL=" /opt/tabeerdotai/backend/.env | cut -d= -f2-)
Testing the Restore, Not Just the Dump
A dump file that pg_dump produces without erroring isn't proof it's restorable — corruption, version mismatches, and permission issues all show up on the restore side, often only when it's too late. So before trusting this as a real backup strategy, I actually restored it.
First obstacle: version mismatch. The server runs postgresql-client 18; my local machine had
postgresql@16. Restoring produced:
pg_restore: error: unsupported version (1.16) in file header
Fixed by installing postgresql@18 locally to match — a custom-format dump's binary header is
version-sensitive in a way plain SQL isn't.
Second obstacle: restoring into a throwaway local database threw 48 errors, one per table:
ERROR: role "shahid" does not exist
The dump preserves the original RDS role for every ALTER TABLE ... OWNER TO statement. That's
correct behavior when restoring back into the same RDS instance under the same role — it's a
problem only when restoring into a different environment with a different role, like my local
Postgres. The fix is exactly the flags meant for that case:
pg_restore --no-owner --no-privileges --dbname="$LOCAL_DB" "$DUMP"
The Actual Verification
With the restore clean, I checked the numbers against the live site rather than just trusting "pg_restore exited 0":
5,145 questions restored
21 users restored
Matching exactly what https://tabeer.ai/api/content/stats/ reports live. That's the difference
between "the backup command didn't crash" and "the backup is actually usable" — a dump that
restores to a database with the right row counts, using the actual production data, not a
synthetic fixture.
What This Means Day to Day
The dump step is the first stage of the nightly Restic
pipeline — pg_dump writes to a mktemp -d temp directory, Restic backs that file up encrypted,
and the temp directory is removed via a trap regardless of how the script exits. Neither tool
does the other's job: pg_dump knows how to produce a consistent, restorable Postgres snapshot;
Restic knows how to get that snapshot encrypted and deduplicated off the box. Combining them
narrowly, instead of reaching for one heavier tool that tries to do both, kept each piece simple
enough to actually test in isolation.
If your backup strategy has never actually been restored and verified against real data, that's worth fixing before you need it. Get in touch.