Update docker-compose.yml files and documentation to recent Postgres Version

[PaulLebmann]

Hi Barcus!

Since the version of postgres is fixed to 9.3 in the docker-compose.yml I wondered if there is a newer version.

According to https://endoflife.date/postgresql this version is EOL. I dont't know if there are any security issues with it and since the database is only available in the docker-compose network I guess that most issues would be mitigated, but nonetheless I tried an update to the recent version.

Of course I used the most recent version of Postgres and it took me a few days to realise, that a backup or the database is no longer possible, because the version in the bareos-fd container of pg_dump is lower that my new Postgres. Nevermind, this is my bad, I set up a separate backup job to create dumps until bareos-fd uses the next Ubuntu LTS as base.

Nonetheless I suggest upgrading the PostgreSQL version used in the docker-compose files in the repo to the latest supported by the Ubuntu base image to get the most out of the PostgreSQL lifetime.

I also think a section in the documentation on updating the PostgreSQL container would be useful. I did my upgrade according to https://github.com/docker-library/postgres/issues/37#issuecomment-928330405 and https://github.com/docker-library/postgres/issues/37#issuecomment-973884334

I had some problems with PostgreSQL intitializing so that an import via putting the .sql file in a mountpoint in /docker-entrypoint-initdb.d (like documented in https://hub.docker.com/_/postgres) did not work. PostgreSQL startet initializing on the first startup but not with the .sql file and restarted so that on the second start there is already a database and the .sql file is not imported. So I manually applied the .sql file.

A slightly adjusted upgrade path would be:

1. Check the latest PostgreSQL Version your bareos-fd supports: docker exec bareos_bareos-fd_1 pg_dump --version
2. Add a mountpoint to your bareos-db containter to save the database dump. I use ./pgsql-backup:/pgsql-backup. Otherwise you could save it to /var/lib/postgresql/data since you already created that mountpoint.
3. Make sure no backups are running. You could stop all other bareos containers. Stopping bareos-dir should be sufficient.
4. Create a database dump via docker exec bareos_bareos-db_1 pg_dumpall -U postgres -f /pgsql-backup/db-migration.sql
5. Stop the database container
6. move the old database data out of the way and into safety mv pgsql/data pgsql/data-old
7. Set new PostgreSQL Version in docker-compose.yml. It should match the version you got from step 1.
8. run docker-compose up -d and ingore the errors bareos-dir is throwing.
9. Import your data to the new database: docker exec -it bareos_bareos-db_1 psql -U postgres < /pgsql-backup/db-migration.sql
10. Restart your bareos stack and check for errors because in the meantime bareos-dir should have given up trying to get a connection to the database. docker-compose restart && docker-compose logs -f
11. Test your director with bconsole or the webui.

I had errors on step 10 because of different password encryption in PostgreSQL 9.3 and 14 (see https://www.postgresql.org/docs/11/auth-password.html) so I had to reissue the password for the PostgreSQL users. After that everything ran fine.

A rollback would be just stopping all containers, copying the old database data directory back into place, changing the PostgreSQL version to the old one in docker-compose.yml and restarting everything.

So probably you could think about upgrading the PostgreSQL version or extending the documentation. I hope this little writeup is useful for someone out there.

Thanks for all your great work!

best regards,

Paul

[barcus]

Hi Paul, thank you for all those information ! Really interesting topic but to be honnest I've never been working on :) I'm kind of PostgreSQL rookie using pg9.3 for a long time without any customization... i'm gonna change that soon

[PaulLebmann]

Hi Barcus,

this has been my first PostgreSQL major version upgrade as well. :) What I described above was my second attempt at it. But thanks to docker and to mounting the PostgreSQL data directory from a directory and not from a volume (what I took from your template) a rollback is really simple.

I couldn't find an automatic way to do the upgrade, and since the linked issue on PostgreSQL upgrades is still rather recent I guess the best, or maybe at the time the only, way is to do it in documentation. One could write a bash script to run outside of the containers, but since some additional troubleshooting may be needed I am not sure if this would send the right message. Maybe it would make it seem to be too easy and your issue queue would be flooded with PostgreSQL questions.

[barcus]

Finally, i'm gonna create a new project to ease PostgreSQL upgrade using a dedicated docker image. I started something like that using pg_upgrade, it works ! Using it i'm able to migrate in one shot from 9.3 to 11/12/13/14 but Bareos fd required v12 max to be compliant.

[barcus]

will try to do it there https://github.com/barcus/postgresql-upgrade work in progress :)