Dockerized local and offline backing up of PostgreSQL with rotation, compression | Hacker News Reader

Dockerized local and offline backing up of PostgreSQL with rotation, compression

github.com

137 pointsthunderbong26 comments

Alternatively you could use Continuous Archiving with Point In Time Recovery [1] which has been built into Postgres for a while. Once your db exceeds several tens of gigabytes dumping to SQL is just not a viable solution anymore.

[1] https://www.postgresql.org/docs/current/continuous-archiving...

Are there any standalone projects for this? A stable docker image that I can point to a Postgres server that can handle both archiving streamed logs to storage and populating a standby (say, on a different cloud) in real time?

https://pgbarman.org/ , haven't used with a Docker image but there seem to be several (non-official) options

Pgbackrest probably?

This is great, I just wish it were a bit easier to set up and configure. That multi-page wall-of-text document is not looking inviting at all.

Really? I just read over it lying in bed for the last 15 minutes and found it super straight forward and well explained. Kudos to the pg team!

It’s funny how you just can’t win with documentation: either “there is no documentation” or “tl;dr”

Surely if there's anything you don't need a 'tl;dr' for it's setting up your database backups lol.

I just had a quick skim of those docs, looks like lots of paragraphs going over all of all the things that can go wrong and how to prevent them. Good. If it takes a week to read all that and set up then it takes a week. I'll take a pass on the "docker container that just does it for me" thanks.

In your opinion the two choices are "no documentation" and "badly-written documentation"?

"Badly written" is something I have never heard about official Postgres documentation.

No, whatever you do about documentation, people will either complain that you didn’t write it or they will complain that they don’t have the time to read it. This is true regardless of how good the documentation you write is.

Long does not imply bad at all, unless the tl:dr; is in response to it being a badly structured wall of text.

I've not read that page from end to end, only scanned through it, but it seems perfectly good for covering the concept, with one caveat that it might not include a detail I'd put in there. Unless I missed it in my scan the page may be missing a point that seems obvious but relatively green admins might miss until they run it of backup space (or start getting larger bills for the auto-growing cloud storage they are backing up to!): your WAL based backups will grow infinitely, and restore time will grow likewise, if you don't occasionally restart your backup sequence with a fresh full backup.

> unless the tl:dr; is in response to it being a badly structured wall of text.

It is, yes. It does contain a lot of information, but the fact that it's one big wall of text makes my ADHD go do something else. Even a table of contents, or some better-structured headlines would help.

Plop it into a LLM and ask it to summarize with references or reformat to your preference. GPT3.5-turbo does a decent job and you can always refer to original source with references.

That's a good idea, thanks! I might even resubmit that as a PR.

Queue the future “show hn: a lightweight pg backup solution”

Exactly, this is the way

Sometimes it's like HN is reading my mind.

I just finished a setup where I run PostgreSQL as a quadlet on CoreOS, and in order to manage backups I use systemd Require so the postgres container depends on a pgbackrest container that just runs a relatively simple script to check if storage is empty or not, and restores the backup from S3 if storage is empty.

And another container runs as a oneshot timer that takes full and incremental backups to S3.

Instead of this [1] I use a much simpler while ! pg_isready loop.

1. https://gist.github.com/efrecon/86456960e2110b287632fd7f42c1...

How do you update postgres on CoreOS? Its better to keep IT immutable so in case of change the whole image must be recreated?

I assume you mean major release upgrade, I haven't gotten that far yet so I view it as a problem to solve once it arises. Obviously it runs in a container, but I have to run the pg_upgrade tool before I start the new container image tag.

An immutable OS like CoreOS for example isn't much different than a regular distro. You can still do a lot of manual work on it if you want. I try to avoid doing manual work on it because I want to be strict IaC, but I can for example test doing a major release upgrade in staging, manually over SSH, and work out the kinks, before I put it into practice using containers and scripts in production.

Though I see very little reason to automate the hell out of a major release upgrade, better do it safely and slowly with SOP's.

Why use which instead of just calling nc directly?

It's not my script, it's the script used by the linked project.

I use pg_isready. Unfortunately I won't link my project because I try to keep this HN account separate from my true identity.

I've been using Borgmatic for this.

It does everything, uses pg_dump under the hood, and adds the output as files for Borg.

Borg then takes care of encryption, incremental and also bit rot. Best solution ever in my book.

Also you can directly restore the db with Borgmatic.

https://torsion.org/borgmatic/docs/how-to/backup-your-databa...

This looks great. When working in smaller teams, there's a lot of pushback when the suggestion of adding a new tool comes up, mostly due to the learning overhead involved. This might be just what we need, since it leverages existing tools many are familiar with already. Thanks for the suggestion.

Thanks for the tip. Maybe this will replace my shell automysqlbackup script.

Cloudnativepg or crunchydata postgres operator takes care of this.

Reply on news.ycombinator.com