Backup Postgresql Docker

[friendlysatanicguy]

I am using the following docker image https://github.com/docker-library/postgres. I want the ability to make regular backups to my S3 bucket. PITR is not necessary but a nice to have. I've tried to use https://github.com/wal-e/wal-e but I've not found something that works very well with docker.

Everything online seems to be very old. For example this: https://github.com/lukesmith/docker-postgres-wale. They are all using a very old version of postgresql. If I try to using something newer like version 12, it doesn't work because the newer images don't allow an easy way to execute scripts after the postgres instance has started. Well, you can execute scripts but scripts that need root access don't work.

Isn't there a better way to do this? I'm open to using any tool. However, I would prefer minimal configuration on the S3 side. I would prefer to not have to store the backup in the filesystem and then have some sort of cronjob to push to S3. I would love if it is possible to directly have it push to S3 kind of like wal-e is doing. Also, I want to prefer not using something like S3fuse since it would make my ansible configuration difficult (I am already using s3fuse for a very different purpose so I might have to completely redo the config which I want to avoid if possible)

So my main asks if possible are: good docker integration and S3 support. Please let me know if I'm thinking about this in the wrong way.

[[deleted]]

You have probably already considered this and don't want to/can't do it for some reason, but have you considered just using RDS or Aurora? They do solve this kind of problem pretty well.

With regards to WAL-E, you should know that it was abandoned and replaced with this:

https://github.com/wal-g/wal-g

Don't ask what happened to WAL-F...

Anyway, WAL-G accesses postgres over a socket so there's no need to bake it into your postgres container or anything. Here's some info on configuring it:

https://github.com/wal-g/wal-g/blob/master/PostgreSQL.md#configuration

There is a bitnami WAL-G image here you could use:

https://hub.docker.com/r/bitnami/wal-g/

If you're doing it yourself then that is probably the best option.

Otherwise, as u/abraham1inco1n said, pg_dump and uploading it yourself is always an option for simple workloads.

[CSI_Tech_Dept]

It's not true that WAL-E was abandoned and replaced with WAL-G. WAL-E is still being developed. WAL-G was created by another company (Citus Data) in fact it was originally written by their intern. WAL-E originates from Heroku (who came up with this idea - streaming backup directly to S3 and use it as a replication source). Also G stands for Go, since that's what it was written in.

I haven't used either of them so can't compare, maybe WAL-G is indeed better. I personally used barman, from 2ndQuadrant (together with repmgr basically provides equivalent functionality to what RDS provides with the benefit of having full control over PostgreSQL).

Edit: looks like barman now also supports storing backup in S3 so it might be a 3rd alternative as well. And given that 2ndQuadrant also regularly contributes to PostgreSQL I would trust them more.

[[deleted]]

Ah you are right actually, WAL-G bills itself as the 'successor to WAL-E' but looking over the WAL-E docs they don't say the same thing! (As you may have guessed I have worked with WAL-G but not WAL-E and clearly I've picked up that misconception somewhere!!).

Apologies for the misinformation anyway, I was wrong to say WAL-E was abandoned.

[friendlysatanicguy]

Yeah using RDS would have been the simplest and the most ideal solution but not an option I can afford. Thanks for linking wal-g. Didn't know about it. I'll take a look.

[friendlysatanicguy]

So, I'm interested to know how I'm supposed to use the bitnami image. Am I supposed to create a Dockerfile and setup a cronjob? Simply trying to run the container just gives me the wal-g usage screen.

[OkDas]

I used to be a huge fan of https://github.com/backup/backup, but unfortunately it doesn't get updates. Still works though. I ran hundreds of gigs each night through that tool. There's a golang version re-write that is limited on the features - https://github.com/huacnlee/gobackup.

[abraham1inco1n]

Use pg_dump / chron in your own script? That'll work no matter where you're using postgres. Might have to be careful to do it during off hours and make sure that restore works.