Upgrading from 14 to 15 failing with `"/var/lib/postgresql/14/data/PG_VERSION": No such file or directory`

[TommyTran732]

This is a reoccurrence of #36

I am using the alpine postgres image if that matters

[casalegno]

I have the same problem. Have any solution?

[casalegno]

Reading and rereading the documentation I got an Idea: is not possible upgrade. This tool is for pg_upgrade that is not intended for major upgrade

[squatica]

@casalegno the first paragraph of pg_upgrade manual contradicts your statement:

https://www.postgresql.org/docs/9.5/pgupgrade.html

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g., from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g., from 9.0.1 to 9.0.4.

[manuelminca]

Same problem here. The error makes sense since I don't have installed PG locally. I followed the documentation to use the volume to point to my data directory which contains PG_VERSION. But the docker container doesnt seem to use it.

I might try to install locally Postgres so it can find the file and the binary files.

[manuelminca]

Alright I found the way to solve it.

To give more context. My old data directory is mounted in a external disk in "/mnt/data/postgres" and I want to move the files to /mnt/data/postgres/16/postgres.

docker run --rm \
-v /mnt/data/postgres:/var/lib/postgresql/data \
-v /mnt/data/postgres/16/postgres:/var/lib/postgresql/16/data \
-e PGDATAOLD=/var/lib/postgresql/data \
-e PGDATANEW=/var/lib/postgresql/16/data \
tianon/postgres-upgrade:12-to-16 

The -v connects my local filesystem with the internal one in the docker container. And then, since PGDATAOLD and PGDATANEW env variables will be used inside the docker container, should point to the inner docker path.

That way works without setting --link option. With the --link option it complains about:

"could not create hard link between old and new data directories: Invalid cross-device link
In link mode the old and new data directories must be on the same file system."

Update:
The error makes sense since we are mounting 2 separate volumes. To use --link option, we have to only mount 1 volume and then reference properly in the ENV variables:

docker run --rm \
  -v /mnt/data/:/var/lib/postgresql/ \
  -e PGDATAOLD=/var/lib/postgresql/postgres \
  -e PGDATANEW=/var/lib/postgresql/postgres/16 \
  tianon/postgres-upgrade:12-to-16 --link

And it works as expected 😄

[empereira]

@manuelminca

Both containers need to be running, right?

[gogowitsch]

@manuelminca Regarding the tianon/postgres-upgrade images: they are made with Debian. That is a problem, because it conflicts with your use of Alpine containers:

As a result of the difference in distros, you will probably get the following warning. It might re-appear in the logs forever after migration:

database has no actual collation version, but a version was recorded

The usual recommended fix is to run ALTER DATABASE template0 REFRESH COLLATION VERSION;, but it will return

ERROR: invalid collation version change

@tkaefer wrote a guide for an alternative way that is compatible with Alpine: https://tkaefer.de/blog/2020/10/08/docker-compose-migrating-postgres-to-new-major/