Scripts for automated backup and manual restore of PostgreSQL databases.
(e.g. to encrypted, redundant Block Storage on Digital Ocean Volumes)

Especially useful for not paying $200/month for small datasets.
(e.g. Heroku where only "Premium" databases have 30+ day backups)

1. VPS Setup
   See ./PROVISION.md.

2. Authorize with a passfile \

   ./foobar-app.pgpass:

   pg-hostname:5432:pg-dbname:pg-username:pg-secret
   

3. Backup

   # pg-xzbackup <pg-passfile> <backup-dir> [file-prefix]
   pg-xzbackup \
       ./foobar-app.pgpass \
       /mnt/pg-backups/ \
       foobar-app

4. Restore

   # pg-xzbackup <pg-passfile> <xz-backup-file>
   pg-xzrestore \
       ./foobar-app.pgpass \
       /mnt/pg-backups/postgres-latest.sql.xz

5. Schedule
   (run every H hours and delete after M months)

   # pg-schedule-backups <h> <m> <pgpass> <bak-dir> [prefix]
   pg-schedule-backups \
       8 \
       60 \
       /mnt/pg-backups/foobar-app.pgpass \
       /mnt/pg-backups/ \
       foobar-app

   (pg-loop-backup must be in PATH, pathman can help)

6. Check Logs

   sudo journalctl -xef --unit pg-loop-backup.service

Your backups directory will look something like this:

/mnt/pg_backups/
├── heroku-postgresql-baz-000.pgpass
├── foobar-app-latest.sql.xz
├── foobar-app-2023-03-14_16.56.06.sql.xz
├── foobar-app-2023-03-15_01.02.33.sql.xz
└── foobar-app-2023-03-15_09.09.50.sql.xz

If you have a DATABASE_URL, you can convert that to passfile format by hand or with pg-url-to-passfile:

./pg-url.env:

DATABASE_URL=postgres://pg-username:pg-secret@pg-hostname:5432/pg-dbname

pg-url-to-passfile \
    ./pg-url.env \
    ./foobar-app.pgpass

• You'll need to install postgresql-client and xz

  sudo apt install -y postgresql-client xz

• You could use cron rather than pg-loop-backup + serviceman

• To get your PostgreSQL Database URL:

  heroku config:get -a foobar-app DATABASE_URL > ./pg-url.txt
  pg-url-to-passfile ./pg-url.txt ./heroku-baz-000.pgpass

You can get years worth of storage for less than the price of a hamburger per month.

• The cheapest Digital Ocean instance is about ~$5/month
• 10gb of encrypted, CEPH-redundant Block Storage is about ~$1/month

Vultr, Scaleway, Linode, etc are all similar.

• You may also find bat useful for viewing the sql files.

Copyright (c) 2023 AJ ONeal
Copyright (c) 2023 Root, LLC

MPL-2.0