Hacker News Reader

Comment by ndsipa_pomu | Hacker News Reader

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?

View on HN

> 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).

Seems def interesting to handle backup, wonder if could just be something simple enough that lets you downgrade if upgrade fails

Yeah. Probably going to need to add a bunch more error catching and handling-of-specific-situations. Edge cases being a thing and all. :)

Chapter 26. Backup and Restore: 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...

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

- pgsql-backup.sh: https://github.com/fukawi2/pgsql-backup/blob/develop/src/pgs...

- https://github.com/SadeghHayeri/pgkit#backup 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-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

Continuous Data Protection > Continuous vs near continuous: https://en.wikipedia.org/wiki/Continuous_Data_Protection#Con...

Thanks. I hadn't come across pgkit before. :)

A 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).

What do you reckon the right place(s) to warn people would be?

Probably 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.

Thanks. I'll make that update in the morning too. :)

Warning added in https://github.com/justinclift/docker-pgautoupgrade/commit/e....

Thanks heaps. :)

As an interactive prompt before mutating the data, with a `-y` to bypass the interactive check (and in the docs for `-y`)

Nah. I can't see anything that needs interaction as being workable, as this is supposed to be an "automatic upgrade" thing.

I 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.

Any 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.

Reply on news.ycombinator.com