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

snspinn commented 3 years ago

This is related/similar to #29.

The issue

I'm running a timescale postgres on pg11, launching with docker-compose. When running the upgrade I get the following output and failure message

tianon/postgres-upgrade:11-to-12
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb:
Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/12/data -l logfile start

 warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

 "/var/lib/postgresql/11/data/PG_VERSION": No such file or directory
Failure, exiting

My setup

I am starting with a docker-compose setup where I recently upgrade the timescale from 1.3.1 to 17.5 (I give this for context but I don't think this is in anyway the source of the issue).

The compose takes the pattern as following:

version: '3.5'

services:
  timescale:
    image: timescale/timescaledb:1.7.5-pg11
    container_name: postgres
    restart: always
    command: postgres -c shared_preload_libraries=timescaledb
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=mytimescaledatabase
    volumes:
      - ./data/postgres:/var/lib/postgresql/data

As you'll see, My config is already a bit outside the pattern(s) described in the README.

Steps to run upgrade

Since my PGDATAOLD is effectively mounted, quite awkwardly, as ~/DIR/data/postgres:/var/lib/postgresql/data, may plan was to mount PGDATANEW as ~/DIR/data/12/postgres:/var/lib/postgresql/data.

I didn't do any 'upgrade testing' outlined in the step outline in the README, so be kind. ;)

I ran (from within working directory):

$ docker run --rm \
-v $PWD/data/postgres:/var/lib/postgresql/data \ 
-v $PWD/data/12/postgres:/var/lib/postgresql/12/data \
tianon/postgres-upgrade:11-to-12

Result

As outlined above.

Also...

The database runs, and is recognised as non-empty by postgres, when attached to a pg12 image.
My database/tables are missing.
Trying to connect to the database from PGAdmin I am greeted with FATAL: no pg_hba.conf entry for host "w.x.y.z", user "postgres", database "postgres", SSL off. This led to further investigation, but probably not worthwhile to explain in detail here (I can supply more details if requested).

davidbartonau commented 3 years ago

I am getting the same issue with 10-to-13 while upgrading a Mattermost setup.

could not open version file "/var/lib/postgresql/10/data/PG_VERSION": No such file or directory

davidbartonau commented 3 years ago

The directory /var/lib/postgresql/10/data/ is empty inside the container. Although so is /var/lib/postgresql/13/data/

It seems like the postgres cluster has not been initialised? My database is small enough that I think a pg_restore is my best option.

snspinn commented 3 years ago

P.s. Also fails with...

$ docker run --rm \
-v $PWD/data/postgres:/var/lib/postgresql/11/data \ 
-v $PWD/data/12/postgres:/var/lib/postgresql/12/data \
tianon/postgres-upgrade:11-to-12

snspinn commented 3 years ago

@davidbartonau what base does your container image use (i.e. debian, alpine, etc?).

The other thread mentioned that permission issues as a possible root cause, but a different directory stucture could be to blame also(?)..

snspinn commented 3 years ago

Re-arranging the bind volume directory structure, so that it matched the first example in the README, worked for me. i.e. ...

$ find $DIR -mindepth 2 -maxdepth 2
$DIR/11/data
$DIR/12/data

so I could run the upgrade with:

docker run --rm \
        -v "$DIR:/var/lib/postgresql" \
        "tianon/postgres-upgrade:11-to-12" \
        --link

(Replacing $DIR as appropriate in both snippets of course).

Don't know if there is an issue with the implementation when using the method I used above (mounting two seperate bind points) or I was just using it wrong. Either way, I will close this.

tianon / docker-postgres-upgrade