initdb: error "/var/lib/postgresql/data" exists but is not empty

[RashiqAzhan]

I'm trying to upgrade from postgres 14 to 15 while retaining data.

When I use the image to pgautoupgrade/pgautoupgrade:latest instead of postgres:14 in the compose file and restart, I get the following errors.

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty
If you want to create a new database system, either remove or empty
he directory "/var/lib/postgresql/data" or run initdb
with an argument other than "/var/lib/postgresql/data".

Maybe I'm misunderstanding but shouldn't the data directory be not empty? It should, and does, have data from version 14 that I want migrated to 15. Thank you for any help.

[justinclift]

Ahhhh. You're meaning going to PG 12. Got it. Yeah, out by one version.

You're right, you should be able to hack that into the Dockerfile and script without too much hassle. :smile:

[jjb]

i think it made it past this

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.

and then it died after this

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 /tmp/my-empty-dir ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /tmp/my-empty-dir -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: 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.
waiting for server to start....2024-03-16 00:13:11.133 UTC [39] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-03-16 00:13:11.136 UTC [39] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-03-16 00:13:11.149 UTC [42] LOG:  database system was shut down at 2024-03-16 00:13:07 UTC
2024-03-16 00:13:11.156 UTC [39] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down....2024-03-16 00:13:11.283 UTC [39] LOG:  received fast shutdown request
2024-03-16 00:13:11.288 UTC [39] LOG:  aborting any active transactions
2024-03-16 00:13:11.289 UTC [39] LOG:  background worker "logical replication launcher" (PID 45) exited with exit code 1
2024-03-16 00:13:11.291 UTC [40] LOG:  shutting down
2024-03-16 00:13:11.294 UTC [40] LOG:  checkpoint starting: shutdown immediate
.2024-03-16 00:13:12.346 UTC [40] LOG:  checkpoint complete: wrote 935 buffers (5.7%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.016 s, sync=1.016 s, total=1.056 s; sync files=301, longest=0.012 s, average=0.004 s; distance=4327 kB, estimate=4327 kB; lsn=0/19495A8, redo lsn=0/19495A8
2024-03-16 00:13:12.352 UTC [39] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

************************************
PostgreSQL data directory: /tmp/my-empty-dir
************************************
****************************************************************************
Unrecognised version of PostgreSQL database files found, aborting completely
****************************************************************************

[justinclift]

Interesting. That Unrecognised version of PostgreSQL database files found, aborting completely should only happen if there's an "unknown" version of PostgreSQL data files in the data directory. In theory.

k, plan b, lets add a version of that volume line back to your compose file, and give it an empty directory on your local system to mount:

    environment:
      PGDATA: '/var/lib/postgresql/data'
    volumes:
      - '/tmp/pgtemp1:/var/lib/postgresql/data'

That should create an empty directory /tmp/pgtemp1 on your local system, and mount that inside the container at /var/lib/postgresql/data.

When the container starts, it should see that directory is empty and initialise it with a blank PostgreSQL database.

If the rest of the container startup process errors out again, you can look at the /tmp/pgtemp1 directory on your local system to see WTF is in the files there. :wink:

There should be a PG_VERSION file (plain text) in the directory after it's initialised, that says what version of PostgreSQL did the initialisation.

[jjb]

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data
************************************
****************************************************************************
Unrecognised version of PostgreSQL database files found, aborting completely
****************************************************************************

[jjb]

i knew i was flying too close to the sun thinking i could use false in shell scripting. my text editor highlights it a different color! why!

postgres-6d5984cd68-7fgng:/# if [ "one" == "two" ]; then echo yes; fi
postgres-6d5984cd68-7fgng:/# if [ false ]; then echo yes; fi
yes

[jjb]

okay, in additinon to the above facepalm, i also realized i wasn't deploying my patched version of your code but was building main.

now i'm back on track possibly getting better info. i got a build with all this glorious log output. then i put i more logging and for some reason the next run didn't have any output. still trying to figure out if my fault or something else, but want to safe this here for posterity. the interesting things are

• in the middle mv: can't rename '/var/lib/postgresql/data/pgdata/old': Invalid argument - i put in some logging here to try to figure out what it's trying to move, but that didn't work... yet
• at the end

  2024-03-16 02:20:41.927 UTC [30] FATAL:  lock file "postmaster.pid" already exists
  2024-03-16 02:20:41.927 UTC [30] HINT:  Is another postmaster (PID 1) running in data directory "/var/lib/postgresql/data/pgdata/old"?

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data/pgdata
************************************
*******************************************************************************************
Performing PG upgrade on version 12 database files.  Upgrading to version 16
*******************************************************************************************
----------------------------------------------------------------------
Checking for left over artifacts from a failed previous autoupgrade...
----------------------------------------------------------------------
-------------------------------------------------------------------------------
No artifacts found from a failed previous autoupgrade.  Continuing the process.
-------------------------------------------------------------------------------
---------------------------------------
Creating OLD temporary directory /var/lib/postgresql/data/pgdata/old
---------------------------------------
--------------------------------------------
Creating OLD temporary directory is complete
--------------------------------------------
-------------------------------------------------------
Moving existing data files into OLD temporary directory
-------------------------------------------------------
'/var/lib/postgresql/data/pgdata/PG_VERSION' -> '/var/lib/postgresql/data/pgdata/old/PG_VERSION'
'/var/lib/postgresql/data/pgdata/base' -> '/var/lib/postgresql/data/pgdata/old/base'
mv: can't rename '/var/lib/postgresql/data/pgdata/old': Invalid argument
'/var/lib/postgresql/data/pgdata/global' -> '/var/lib/postgresql/data/pgdata/old/global'
'/var/lib/postgresql/data/pgdata/old' -> '/var/lib/postgresql/data/pgdata/old/old'
'/var/lib/postgresql/data/pgdata/pg_commit_ts' -> '/var/lib/postgresql/data/pgdata/old/pg_commit_ts'
'/var/lib/postgresql/data/pgdata/pg_dynshmem' -> '/var/lib/postgresql/data/pgdata/old/pg_dynshmem'
'/var/lib/postgresql/data/pgdata/pg_hba.conf' -> '/var/lib/postgresql/data/pgdata/old/pg_hba.conf'
'/var/lib/postgresql/data/pgdata/pg_ident.conf' -> '/var/lib/postgresql/data/pgdata/old/pg_ident.conf'
'/var/lib/postgresql/data/pgdata/pg_logical' -> '/var/lib/postgresql/data/pgdata/old/pg_logical'
'/var/lib/postgresql/data/pgdata/pg_multixact' -> '/var/lib/postgresql/data/pgdata/old/pg_multixact'
'/var/lib/postgresql/data/pgdata/pg_notify' -> '/var/lib/postgresql/data/pgdata/old/pg_notify'
'/var/lib/postgresql/data/pgdata/pg_replslot' -> '/var/lib/postgresql/data/pgdata/old/pg_replslot'
'/var/lib/postgresql/data/pgdata/pg_serial' -> '/var/lib/postgresql/data/pgdata/old/pg_serial'
'/var/lib/postgresql/data/pgdata/pg_snapshots' -> '/var/lib/postgresql/data/pgdata/old/pg_snapshots'
'/var/lib/postgresql/data/pgdata/pg_stat' -> '/var/lib/postgresql/data/pgdata/old/pg_stat'
'/var/lib/postgresql/data/pgdata/pg_stat_tmp' -> '/var/lib/postgresql/data/pgdata/old/pg_stat_tmp'
'/var/lib/postgresql/data/pgdata/pg_subtrans' -> '/var/lib/postgresql/data/pgdata/old/pg_subtrans'
'/var/lib/postgresql/data/pgdata/pg_tblspc' -> '/var/lib/postgresql/data/pgdata/old/pg_tblspc'
'/var/lib/postgresql/data/pgdata/pg_twophase' -> '/var/lib/postgresql/data/pgdata/old/pg_twophase'
'/var/lib/postgresql/data/pgdata/pg_wal' -> '/var/lib/postgresql/data/pgdata/old/pg_wal'
'/var/lib/postgresql/data/pgdata/pg_xact' -> '/var/lib/postgresql/data/pgdata/old/pg_xact'
'/var/lib/postgresql/data/pgdata/postgresql.auto.conf' -> '/var/lib/postgresql/data/pgdata/old/postgresql.auto.conf'
'/var/lib/postgresql/data/pgdata/postgresql.conf' -> '/var/lib/postgresql/data/pgdata/old/postgresql.conf'
'/var/lib/postgresql/data/pgdata/postmaster.opts' -> '/var/lib/postgresql/data/pgdata/old/postmaster.opts'
'/var/lib/postgresql/data/pgdata/postmaster.pid' -> '/var/lib/postgresql/data/pgdata/old/postmaster.pid'
-------------------------------------------------------------------
Moving existing data files into OLD temporary directory is complete
-------------------------------------------------------------------
---------------------------------------
Creating NEW temporary directory /var/lib/postgresql/data/pgdata/new
---------------------------------------
--------------------------------------------
Creating NEW temporary directory is complete
--------------------------------------------
-----------------------------------------------------
Changing permissions of temporary directories to 0700
-----------------------------------------------------
---------------------------------------------------------
Changing permissions of temporary directories is complete
---------------------------------------------------------
------------------------------------
Determining our own initdb arguments
------------------------------------
2024-03-16 02:20:41.927 UTC [30] FATAL:  lock file "postmaster.pid" already exists
2024-03-16 02:20:41.927 UTC [30] HINT:  Is another postmaster (PID 1) running in data directory "/var/lib/postgresql/data/pgdata/old"?

[jjb]

In the light of morning I quickly created a minimal local reproduction!

what did it: the volumes config - without that, the service starts.

version: '3'

services:
  postgres:
    image: 'pgautoupgrade/pgautoupgrade:15-alpine'
    platform: 'linux/amd64'
    environment:
      POSTGRES_PASSWORD: 'password'
      POSTGRES_DB: 'app'
      PGDATA: '/var/lib/postgresql/data/pgdata'
    volumes:
      - 'postgres:/var/lib/postgresql/data'
    ports:
      - '5432'

docker-compose up postgres

 | PostgreSQL Database directory appears to contain a database; Skipping initialization
 |
 | ************************************
 | PostgreSQL data directory: /var/lib/postgresql/data/pgdata
 | ************************************
 | ****************************************************************************
 | Unrecognised version of PostgreSQL database files found, aborting completely
 | ****************************************************************************
exited with code 9

[jjb]

okay, now realizing that isn't actually a standalone reproduction, requires the volume having something in it first. standby...

[justinclift]

With your PostgreSQL data directory, what version of PostgreSQL is it upgrading from?

I should update the script in the main repository to support PG 12 as the target. That would probably help your situation out a lot. :smile:

[justinclift]

I've just added support for migrating to PG 12 (7ffe5e7ae59419e09903d4d01614992da00a5708), and pushed a new Docker image with the tag 12-dev to the Docker Hub repository.

If you try that docker image out, it might just all work out automatically. :smile:

[justinclift]

As an update for anyone keeping an eye on this issue, the problem that @jjb was hitting was really two separate things. Both now fixed (https://github.com/pgautoupgrade/docker-pgautoupgrade/commit/ba99db1a6398932999146059d90c5be23bb0375e) and (https://github.com/pgautoupgrade/docker-pgautoupgrade/commit/c869388e73312c842126deff8cfb57b20d85c8b6).

@eurekin From the description of the problem you hit, the 2nd of those might have fixed things for you as well. :smile:

[FireMasterK]

``` PostgreSQL Database directory appears to contain a database; Skipping initialization ************************************ PostgreSQL data directory: /var/lib/postgresql/data ************************************ ******************************************************************************************* Performing PG upgrade on version 15 database files. Upgrading to version 16 ******************************************************************************************* ---------------------------------------------------------------------- Checking for left over artifacts from a failed previous autoupgrade... ---------------------------------------------------------------------- ------------------------------------------------------------------------------- No artifacts found from a failed previous autoupgrade. Continuing the process. ------------------------------------------------------------------------------- --------------------------------------- Creating OLD temporary directory /var/lib/postgresql/data/old --------------------------------------- -------------------------------------------- Creating OLD temporary directory is complete -------------------------------------------- ------------------------------------------------------- Moving existing data files into OLD temporary directory ------------------------------------------------------- '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION' '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base' '/var/lib/postgresql/data/core' -> '/var/lib/postgresql/data/old/core' '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global' mv: can't rename '/var/lib/postgresql/data/old': Invalid argument '/var/lib/postgresql/data/old' -> '/var/lib/postgresql/data/old/old' '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts' '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem' '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf' '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf' '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical' '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact' '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify' '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot' '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial' '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots' '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat' '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp' '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans' '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc' '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase' '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal' '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact' '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf' '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf' '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts' ------------------------------------------------------------------- Moving existing data files into OLD temporary directory is complete ------------------------------------------------------------------- --------------------------------------- Creating NEW temporary directory /var/lib/postgresql/data/new --------------------------------------- -------------------------------------------- Creating NEW temporary directory is complete -------------------------------------------- ----------------------------------------------------- Changing permissions of temporary directories to 0700 ----------------------------------------------------- --------------------------------------------------------- Changing permissions of temporary directories is complete --------------------------------------------------------- ------------------------------------------------------------------------------ Using initdb arguments passed in from the environment: "-E UTF8" ------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------- Old database using collation settings: '"-E UTF8"'. Initialising new database with those settings too -------------------------------------------------------------------------------------------------------------------- Initialising PostgreSQL 16 data directory initdb: error: too many command-line arguments (first is "UTF8"") initdb: hint: Try "initdb --help" for more information. 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 this locale configuration: provider: libc LC_COLLATE: C LC_CTYPE: C LC_MESSAGES: en_US.utf8 LC_MONETARY: en_US.utf8 LC_NUMERIC: en_US.utf8 LC_TIME: en_US.utf8 The default text search configuration will be set to "english". Data page checksums are disabled. initdb: error: directory "/var/lib/postgresql/data" exists but is not empty initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data". 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 this locale configuration: provider: libc LC_COLLATE: C LC_CTYPE: C LC_MESSAGES: en_US.utf8 LC_MONETARY: en_US.utf8 LC_NUMERIC: en_US.utf8 LC_TIME: en_US.utf8 The default text search configuration will be set to "english". Data page checksums are disabled. initdb: error: directory "/var/lib/postgresql/data" exists but is not empty initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data". 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 this locale configuration: provider: libc LC_COLLATE: C LC_CTYPE: C LC_MESSAGES: en_US.utf8 initdb: error: directory "/var/lib/postgresql/data" exists but is not empty initdb: hint: If you want to create a new database system, either remove or empty the directory "/var/lib/postgresql/data" or run initdb with an argument other than "/var/lib/postgresql/data". LC_MONETARY: en_US.utf8 LC_NUMERIC: en_US.utf8 LC_TIME: en_US.utf8 The default text search configuration will be set to "english". Data page checksums are disabled. ```

I got this error when using pgautoupgrade/pgautoupgrade:16-alpine

    postgres:
        image: pgautoupgrade/pgautoupgrade:16-alpine
        restart: unless-stopped
        environment:
            - POSTGRES_USER=appservice
            - POSTGRES_PASSWORD=changeme
            - LC_COLLATE=C
            - LC_CTYPE=C
            - POSTGRES_INITDB_ARGS="-E UTF8"
        volumes:
            - pg_db:/var/lib/postgresql/data
        container_name: postgres
        shm_size: '1gb'

The volume seems to have a old and new directory after running it, but new is empty.

Edit: Removing the POSTGRES_INITDB_ARGS variable seems to fix the issue. :thinking:

[uwekoenig]

I can confirm the issue with POSTGRES_INITDB_ARGS mentioned by @FireMasterK

Wanted to upgrade from image postgres:12-alpine to pgautoupgrade/pgautoupgrade:15-alpine and had to remove the line with the arguments to run the database migration.

  db:
#    image: postgres:12-alpine
    image: pgautoupgrade/pgautoupgrade:15-alpine
    container_name: xwiki-postgres-db
    volumes:
      - postgres-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_ROOT_PASSWORD=xwiki
      - POSTGRES_PASSWORD=xwiki
      - POSTGRES_USER=xwiki
      - POSTGRES_DB=xwiki
#      - POSTGRES_INITDB_ARGS="--encoding=UTF8"
    networks:
      - bridge
    restart: unless-stopped