4 ms·
Dockerized local and offline backing up of PostgreSQL with rotation, compression
- INTPenis 3y agoSometimes 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/86456960e2110b287632fd7f42c1cd31/1c5c811126d981507bbd6bbd70c0cfe7e9521644 https://gist.github.com/efrecon/86456960e2110b287632fd7f42c1...
- teaearlgraycold 3y agoWhy use which instead of just calling nc directly?
- INTPenis 3y agoIt'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.
- mati365 3y agoHow do you update postgres on CoreOS? Its better to keep IT immutable so in case of change the whole image must be recreated?
- INTPenis 3y agoI 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.
- michelpp 3y agoAlternatively 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.html https://www.postgresql.org/docs/current/continuous-archiving...
- itsthecourier 3y agoExactly, this is the way
- stavros 3y agoThis 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.
- javaunsafe2019 3y agoReally? 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!
- fiddlerwoaroof 3y agoIt’s funny how you just can’t win with documentation: either “there is no documentation” or “tl;dr”
- BigJono 3y agoSurely 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.
- quest88 3y agoQueue the future “show hn: a lightweight pg backup solution”
- deleted 3y ago[deleted]
- Belphemur 3y agoI'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-databases/ https://torsion.org/borgmatic/docs/how-to/backup-your-databa...
- gkhartman 3y agoThis 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.
- BonoboIO 3y agoThanks for the tip. Maybe this will replace my shell automysqlbackup script.
- tehlike 3y agoCloudnativepg or crunchydata postgres operator takes care of this.