3 ms·
This looks useful. I've had issues in the past with PostgreSQL as a backend for NextCloud (all running in docker) and blindly pulling the latest PostgreSQL and
by ndsipa_pomu 3y ago
This looks useful.
I've had issues in the past with PostgreSQL as a backend for NextCloud (all running in docker) and blindly pulling the latest PostgreSQL and then wondering why it didn't work when the major version jumped. (It's easy enough to fix once you figure out why it's not running - just do a manual export from the previous version and import the data into the newer version).
However, does this container automatically backup the data before upgrading in case you discover that the newer version isn't compatible with whatever is using it?
- justinclift 3y ago> However, does this container automatically backup the data before upgrading ... Nope. This container is the official Docker Postgres 15.3-alpine3.18 image + the older versions of PostgreSQL compiled into it and some pg_upgrade scripting added to the docker entrypoint script to run the upgrade before starting PostgreSQL. It goes out of it's way to use the "--link" option when running pg_upgrade, to upgrade in-place and therefore avoid making an additional copy of the data. That being said, this is a pretty new project (about a week old on GitHub), and having some support for making an (optional) backup isn't a bad idea. I'll have to think on a good way to make that work. Probably needs to check for some environment variable as a toggle or something, for the people who want it... (unsure yet).
- tough 3y agoSeems def interesting to handle backup, wonder if could just be something simple enough that lets you downgrade if upgrade fails
- justinclift 3y agoYeah. Probably going to need to add a bunch more error catching and handling-of-specific-situations. Edge cases being a thing and all. :)
- westurner 3y agoChapter 26. Backup and Restore: https://www.postgresql.org/docs/current/backup.html https://www.postgresql.org/docs/current/backup.html Chapter 26. Backup and Restore > 26.3. Continuous Archiving and Point-in-Time Recovery (PITR) > 26.3.4. Recovering Using a Continuous Archive Backup: https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY https://www.postgresql.org/docs/current/continuous-archiving... IIRC there are fancier ways than pg_dump to do Postgres backups that aren't postgres native PITR? gh topic postgresql-backup: https://github.com/topics/postgresql-backup https://github.com/topics/postgresql-backup - pgsql-backup.sh: https://github.com/fukawi2/pgsql-backup/blob/develop/src/pgsql-backup.sh https://github.com/fukawi2/pgsql-backup/blob/develop/src/pgs... - https://github.com/SadeghHayeri/pgkit#backup https://github.com/SadeghHayeri/pgkit#backup https://github.com/SadeghHayeri/pgkit/blob/main/pgkit/cli/commands/pitr.py https://github.com/SadeghHayeri/pgkit/blob/main/pgkit/cli/co... : $ sudo pgkit pitr backup <name> <delay> > Recover: This command is used to recover a delayed replica to a specified point in time between now and the database's delay amount. The time can be given in the YYYY-mm-ddTHH:MM format. The latest keyword can also be used to recover the database up to the latest transaction available.: $ sudo pgkit pitr recover <name> <time> $ sudo pgkit pitr recover <name> latest > The database will then start replaying the WAL files. It's progress can be tracked through the log files at /var/log/postgresql/. - "PostgreSQL-Disaster-Recovery-With-Barman" https://github.com/softwarebrahma/PostgreSQL-Disaster-Recovery-With-Barman https://github.com/softwarebrahma/PostgreSQL-Disaster-Recove... : > The solution architecture chosen here is a 'Traditional backup with WAL streaming' architecture implementation (Backup via rsync/SSH + WAL streaming). This is chosen as it provides incremental backup/restore & a bunch of other features. Glossary of backup terms: https://en.wikipedia.org/wiki/Glossary_of_backup_terms https://en.wikipedia.org/wiki/Glossary_of_backup_terms Continuous Data Protection > Continuous vs near continuous: https://en.wikipedia.org/wiki/Continuous_Data_Protection#Continuous_vs_near_continuous https://en.wikipedia.org/wiki/Continuous_Data_Protection#Con...
- justinclift 3y agoThanks. I hadn't come across pgkit before. :)
- ndsipa_pomu 3y agoA default option is to warn about the possibility and then just leave the admins to do their own backups (which they should be doing anyway).
- justinclift 3y agoWhat do you reckon the right place(s) to warn people would be?
- ndsipa_pomu 3y agoProbably in the project readme. You could put a warning in the logs as well (e.g. on container startup), but that wouldn't be as visible.
- justinclift 3y agoThanks. I'll make that update in the morning too. :)
- justinclift 3y agoWarning added in https://github.com/justinclift/docker-pgautoupgrade/commit/ed16ccbcc5df6404d1045b9e29bdfa5e8f15edd6 https://github.com/justinclift/docker-pgautoupgrade/commit/e.... Thanks heaps. :)
- westurner 3y agoAs an interactive prompt before mutating the data, with a `-y` to bypass the interactive check (and in the docs for `-y`)
- justinclift 3y agoNah. I can't see anything that needs interaction as being workable, as this is supposed to be an "automatic upgrade" thing.
- jacooper 3y agoI think following MariaDBs lead is a good idea, thru have an environment variable to enable automatic upgrades, and it creates a backup before every upgrade. Works like a charm.
- justinclift 3y agoAny idea how well it works with multi-TB databases? Copying TBs of data around seems like it would delay the start of the database by a lot.