Migration from v17 to v18 appears impossible

[redbeardymcgee]

After pulling an update on postgres:latest which bumped the major version from 17 to 18, the following error is encountered.

Error: in 18+, these Docker images are configured to store database data in a format which is compatible with "pg_ctlcluster" (specifically, using major-version-specific directory names). This better reflects how PostgreSQL itself works, and how upgrades are to be performed.

See also #1259

Counter to that, there appears to be PostgreSQL data in: /var/lib/postgresql/data (unused mount/volume)

This is usually the result of upgrading the Docker image without upgrading the underlying database using "pg_upgrade" (which requires both versions).

The suggested container configuration for 18+ is to place a single mount at /var/lib/postgresql which will then place postgreSQL data in a subdirectory, allowing usage of "pg_upgrade --link" without mount point boundary issues.

See #37 for a (long) discussion around this process, and suggestions for how to do so.

Below are the efforts I have made to resolve the issue without success.

I have also tried to remove /data from my mount point in a podman quadlet, but it results in the same error. The only difference is Counter to that, there appears to be PostgreSQL data in: /var/lib/postgresql rather than /var/lib/postgresql/data.

I'm guessing this means we will need to export the data and reimport it to a new volume, or maybe we can just move the data around on the filesystem. I'm not sure, but this is going to take down dozens of my containers for an indefinite period of time.

EDIT: After re-reading the error a few more times, there is an instruction to use the above suggested mount point, then run pg_upgrade --link. However, this is not possible because the container will error and die before the user is able to enter it and run the upgrade command. Potential workaround may be to change the entrypoint and/or exec command to run the pg_upgrade --link operation, then remove the changed entrypoint/exec to allow the container to run normally again. Not tested.

EDIT#2: You must identify the directory where the old cluster binaries reside. Please use the -b command-line option or the PGBINOLD environment variable. Failure, exiting is the result of a naive Exec=sh -c 'sleep 3 && su postgres -c "pg_upgrade --link"' in my quadlet. I don't know what old cluster binaries even are, yet, let alone where they are stored in my container.

EDIT#3: More fumbling around resulted in no progress, but I did come to understand that there may be no upgrade process in place for this scenario. At least, if there is, I couldn't figure it out. Noted in the above edit, you need to point to the old cluster binaries with the -b option or PGBINOLD env var; this would simply be the old versions of the postgres binaries, which simply will not exist on an upgraded container, so this requirement is impossible to fulfill. As you work out more cli options that must be supplied, you will continue to run into errors that are mostly unresolvable without unusual manual workarounds. Remember that the error states you should place a single mount at /var/lib/postgresql which will then place postgreSQL data in a subdirectory. What subdirectory, and is it created automatically?

Is this upgrade process documented anywhere?

Originally posted by @redbeardymcgee in #1376

I have dozens of services which will be impacted eventually by this migration to v18, so I would like to understand what procedure would resolve this issue without locking my version to 17 or older.

[tianon]

Migration between PostgreSQL major versions with the same data directory has never been supported - this change is a reflection of that fact. See #37 for a very long discussion on that topic.

If you have deployments which are deploying a fresh database, they should be mounting at /var/lib/postgresql now (instead of /var/lib/postgresql/data).

If you want to migrate an existing deployment with pg_upgrade, then you need to do the pg_upgrade dance, namely either restructure your existing data to be in a subdirectory so you can control the "old" and "new" paths on the same volume and use --link, or do it without --link and separate volumes.

[redbeardymcgee]

Thank you for your comment. I think the heart of my question is what exactly constitutes the pg_upgrade dance and how is it performed? Can you share an example of the process?

[tianon]

There are over 100 comments on exactly that point in #37, linked above -- however, I have also just today upgraded the documentation on my own personal PoC to be more clear/explicit on this point, which you can find at https://github.com/tianon/docker-postgres-upgrade#readme (it ends with an explicit example of a full "start a 17 server, fill it with data, stop it, upgrade it to 18, start 18").

[redbeardymcgee]

Thank you. That discussion was unapproachable for clear documentation on the process, but I appreciate that there are dozens of competing scripts and ideas. I love your PoC for its directness and clarity.