Backing up Fly.io PostgreSQL DB with a one-liner

Fly.io is in the process of turning their PostgreSQL DB offering into a managed database which might mean they’ll have automated backups.

Until then, you have to do it manually.

# In one Terminal
fly proxy 5432:5432 -a elixirdrops-db

# In the second Terminal
pg_dump -h localhost \
  -U elixirdrops \
  -d elixirdrops \
  --format custom \
  > "elixirdrops.dump"

I promised a one-liner, so here it is:

export DB=elixirdrops; export datetime=$(date +'%Y%m%d%H%M%S'); fly proxy 5432:5432 -a elixirdrops-db & (sleep 5 && pg_dump -h localhost -U $DB -d $DB --format custom > "${DB}_${datetime}.dump" && lsof -ti tcp:5432 | xargs kill -9)

It runs the Fly.io proxy in background, backs up the database, then kills the proxy process.

Notice we don’t enter a password there as we don’t want to keep the password in the shell history. To make it work, create a ~/.pgpass file in the following format:

HOST:PORT:DB:USER:PASSWORD

So, for me, the file looks something like this:

localhost:5432:elixirdrops:elixirdrops:TMI
localhost:5432:optimum_landing:optimum_landing:TMI

Then, set appropriate file permissions:

chmod 600 ~/.pgpass

And you’re good to go.