I needed to back up every database on a managed Postgres instance, not just one. Connection string in hand, that turned into a small script, two gotchas I hadn’t hit before, and — weeks later, restoring the dump on my own machine — a version mismatch that stopped pg_restore cold.

Finding every database

Postgres doesn’t give you a “list of databases” flag on pg_dump itself, so the first step is a plain query:

psql -t -A -c "SELECT datname FROM pg_database WHERE datistemplate = false;" "$PG_CONN"

First attempt, I had the flags after the connection string. psql didn’t error, it just silently ignored them — no query ran, no rows came back, nothing in the output pointed at why. It printed an “extra command-line argument” warning and moved on. Flags before the connection string, every time, and I check the output isn’t empty before trusting the loop that comes next.

One of the databases the query returned was the provider’s own internal system database — not something the app user has rights to touch. Trying to dump it failed with a pg_hba.conf rejection. Rather than let that one failure kill the whole run, I excluded it by name before the loop started.

Dumping each one

pg_dump -Fc --no-owner --no-privileges -f "<db>.dump" "<db>"

-Fc for the custom format — compressed, and restorable with pg_restore instead of piping raw SQL back in. --no-owner --no-privileges so the dump doesn’t carry role/ownership assumptions from the source instance into wherever it lands next.

Most databases finished in seconds. One was over 7.5GB and took close to an hour. Running that in the foreground and staring at a blocked terminal is a bad way to spend an hour — I backgrounded it and checked on it by watching the dump file’s size grow instead of assuming the process had hung.

The script, minus the credentials

I turned the discovery-and-dump steps into a script, with the connection string and bucket name pulled from a separate config file that never gets committed — the script itself has no idea what the real values are. Each database gets dumped, the batch gets pushed to a GCS bucket, done.

My first draft was a cron job with retention pruning: keep the last N days, delete anything older, run nightly. Then I realized this wasn’t an ongoing job at all — it was a one-time migration task that just happened to need “back up everything first.” I stripped the cron and retention logic back out. It’s a good reminder of how much a script accumulates by default versus what the actual job in front of you needs; a scheduled job with pruning logic is a different, larger thing than “back this up once before I do something risky.”

A second gotcha, before the real one

Setting up the backup connection the first time, I hit:

psql: error: connection to server ... failed: FATAL:  database "avnadmin" does not exist

avnadmin is the default admin user on Aiven-managed Postgres, and libpq’s default behavior — when no database is named — is to connect to a database with the same name as the user. That database usually doesn’t exist. The fix is just to say which database to connect to; nothing subtler than that, but the error message doesn’t point at the actual cause at all.

The wall: pg_restore version mismatch

The dumps sat in GCS for a few weeks. Then I needed to restore one — a production dump — into a local Postgres instance to debug something. First attempt:

pg_restore: error: unsupported version (1.16) in file header

The dump had been made by a newer pg_dump (Postgres 17, on the managed instance) than the pg_restore I had locally (Postgres 14). The custom dump format’s version has to be readable by the tool doing the restore — a newer pg_restore can read older dump formats, but not the other way around. My local install was simply behind.

The obvious fixes are installing a newer Postgres client system-wide or spinning up a matching version in Docker. Both work, both are more than the problem needed. Instead I checked what else on the machine already shipped a Postgres client — and DBeaver, which I already had installed for exactly this kind of ad-hoc database poking, bundles its own set of PostgreSQL driver binaries, versioned, sitting right there on disk:

"C:\Users\<user>\AppData\Roaming\DBeaverData\drivers\clients\postgresql\win\17\pg_restore.exe" \
  -h <host> -p <port> -U <user> -d <target_db> --no-owner --no-privileges -v <dump-file>

Pointed at that binary directly instead of whatever was on PATH, and the restore ran clean. No install, no container, no version upgrade to the system tooling — just using a newer client that was already sitting in a GUI tool’s driver cache.

The lesson that’s actually worth remembering: pg_restore needs to be the same major version as the pg_dump that produced the file, or newer, never older. And before reaching for Docker or a fresh install to get that newer version, check whether one’s already on the machine — pgAdmin, DBeaver, and most other Postgres GUI clients bundle several versions of the command-line tools, and pointing at the binary directly is often faster than anything else.

I wrote the whole thing up as an internal runbook afterward, gotchas included, so the next restore doesn’t start from zero.