On the older stolon-based postgres-ha Postgres clusters, we used the included wal-g setup to manage external database snapshots and PITR.
However wal-g is currently not included in the newer repmgr-based postgres-flex, and it doesn’t look like anything has yet been included to replace it.
What would someone recommend for doing external snapshots (and ideally, PITR) on postgres-flex?
Most solutions don’t really seem viable right now as they really need to be included in the postgres-flex image to be usable.
Things I’ve looked at:
- wal-g – Needs to run on a postgres host with direct fs access.
- pgBackRest – Also needs to run on the postgres host with direct fs access.
- pgBarMan – Doesn’t need to run on the postgres host… but needs SSH access to the host to transfer over rsync.
Essentially all the production-oriented backup solutions rely on using postgres’ feature to pause committing WALs, copy the the data while its not being written to, and then resume postgres.
I’d rather not maintain a derived image and break the built-in image upgrades support so right now it needs to be something that runs on a separate machine to postgres…
Right now I’m basically stuck manually performing snapshots with pg_dumpall
and it’s not really a great solution:
DATE=`date +%s` ; pg_dumpall | \
gzip -8c > all_$DATE.sql.gz && \
aws s3 cp all_$DATE.sql.gz s3://$BACKUPS_STORAGE_BUCKET/pgdump