"/var/lib/postgresql/data" is not a valid data directory (upgrading from 14 to 17).

[Mr-AA]

Hello ,
I encountered an issue while running the pgautoupgrade process to update to postgres version 17 using "pgautoupgrade:17-bookwarm". The tool fails(error logs are attached).

COMMAND :

$ docker run --name pgauto -it --mount type=bind,source="/home/$USER/postgres/data",target="/var/lib/postgresql/data" -e POSTGRES_PASSWORD=<PASSWORD>  pgautoupgrade/pgautoupgrade:17-bookwarm

LOG:

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data
************************************
Creating upgrade lock file at /var/lib/postgresql/data/upgrade_in_progress.lock
*******************************************************************************************
Performing PG upgrade on version 14 database files.  Upgrading to version 17
*******************************************************************************************
----------------------------------------------------------------------
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/current_logfiles' -> '/var/lib/postgresql/data/old/current_logfiles'
'/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
'/var/lib/postgresql/data/kalki.state' -> '/var/lib/postgresql/data/old/kalki.state'
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_log' -> '/var/lib/postgresql/data/old/pg_log'
'/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/postgresql.conf.ORG' -> '/var/lib/postgresql/data/old/postgresql.conf.ORG'
'/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
'/var/lib/postgresql/data/postmaster.pid' -> '/var/lib/postgresql/data/old/postmaster.pid'
'/var/lib/postgresql/data/upgrade_in_progress.lock' -> '/var/lib/postgresql/data/old/upgrade_in_progress.lock'
-------------------------------------------------------------------
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
---------------------------------------------------------
-------------------------------------------------
Remove postmaster.pid file from PG data directory
-------------------------------------------------
------------------------------------
Determining our own initdb arguments
------------------------------------
2024-12-17 11:05:54.315 GMT [61] LOG:  skipping missing configuration file "/var/lib/postgresql/data/postgresql.auto.conf"
2024-12-17 11:05:54 UTC [61-2] FATAL:  "/var/lib/postgresql/data" is not a valid data directory
2024-12-17 11:05:54 UTC [61-3] DETAIL:  File "/var/lib/postgresql/data/PG_VERSION" is missing

[andyundso]

Could it be that the container crashed between Determining our own initdb arguments and then the next line of logs. Because at that point, the files are actually located at /var/lib/postgresql/data/old and not /var/lib/postgresql/data.

do you plan to use one-shot mode (so later use the Postgres image again) or keep ours permanently?

[Mr-AA]

Observed Behavior: The following log entries were recorded before the Docker container crashed, based on my observations. The logs captured from the Docker container also exhibit similar entries.

Additional Attempts(as suggested): I attempted to include the -e PGAUTO_ONESHOT=yes environment variable as part of the Docker run command to address the issue. Unfortunately, this yielded the same result, with no change in behavior.

[andyundso]

I'm wondering if there are some specific conditions in your database that make one of our queries inside "Determing our own initdb arguments" crash.

Can you try to run the command below, which provides these initdb arguments and therefore skips this section?

docker run --name pgauto -it --mount type=bind,source="/home/$USER/postgres/data",target="/var/lib/postgresql/data"-e PGAUTO_ONESHOT=yes -e POSTGRES_INITDB_ARGS="--encoding=UTF-8" pgautoupgrade/pgautoupgrade:17-bookworm

I am actually not sure if POSTGRES_PASSWORD is needed when the database is already initialized. Because the image will change to the postgres user anyhow, which usually has password-free access to the Postgres socket ... but in case my command crashes again, please also try with providing the Postgres password.

[Mr-AA]

COMMAND:

docker run --name pgauto -it --mount type=bind,source="/home/$USER/postgres/data",target="/var/lib/postgresql/data" -e PGAUTO_ONESHOT=yes -e POSTGRES_INITDB_ARGS="--encoding=UTF-8" -e POSTGRES_PASSWORD=<PASSWORD> pgautoupgrade/pgautoupgrade:17-bookworm

RESPONSE LOG:

PostgreSQL Database directory appears to contain a database; Skipping initialization

************************************
PostgreSQL data directory: /var/lib/postgresql/data
************************************
Creating upgrade lock file at /var/lib/postgresql/data/upgrade_in_progress.lock
*******************************************************************************************
Performing PG upgrade on version 14 database files.  Upgrading to version 17
*******************************************************************************************
----------------------------------------------------------------------
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
-------------------------------------------------------
renamed '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
renamed '/var/lib/postgresql/data/current_logfiles' -> '/var/lib/postgresql/data/old/current_logfiles'
renamed '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
renamed '/var/lib/postgresql/data/kalki.state' -> '/var/lib/postgresql/data/old/kalki.state'
mv: cannot move '/var/lib/postgresql/data/old' to a subdirectory of itself, '/var/lib/postgresql/data/old/old'
renamed '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
renamed '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
renamed '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
renamed '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
renamed '/var/lib/postgresql/data/pg_log' -> '/var/lib/postgresql/data/old/pg_log'
renamed '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
renamed '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
renamed '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
renamed '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
renamed '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
renamed '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
renamed '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
renamed '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
renamed '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
renamed '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
renamed '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
renamed '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
renamed '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
renamed '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
renamed '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
renamed '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
renamed '/var/lib/postgresql/data/postgresql.conf.ORG' -> '/var/lib/postgresql/data/old/postgresql.conf.ORG'
renamed '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
renamed '/var/lib/postgresql/data/postmaster.pid' -> '/var/lib/postgresql/data/old/postmaster.pid'
renamed '/var/lib/postgresql/data/upgrade_in_progress.lock' -> '/var/lib/postgresql/data/old/upgrade_in_progress.lock'
-------------------------------------------------------------------
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: --encoding=UTF-8
------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Old database using collation settings: '--encoding=UTF-8'.  Initialising new database with those settings too
--------------------------------------------------------------------------------------------------------------------
Initialising PostgreSQL 17 data directory
could not execute command ""/usr/lib/postgresql/17/bin/postgres" -V": Cannot allocate memory
initdb: error: program "postgres" is needed by initdb but was not found in the same directory as "/usr/lib/postgresql/17/bin/initdb"

[andyundso]

could not execute command ""/usr/lib/postgresql/17/bin/postgres" -V": Cannot allocate memory -> looks like your machine needs more RAM?

[justinclift]

Note that I just updated the logs and command text output in this issue to make it a bit more readable for others. No change to the content. 😄

[Mr-AA]

My server has around 24GB of memory and the Postgres data folder's complete size is around 4.5GB. I ran a few commands to show you memory, which is below

COMMAND1:

docker info | grep Memory

RESPONSE1:

WARNING: No swap limit support
Total Memory: 29.45GiB

COMMAND2:

$ cat /proc/meminfo

RESPOSNSE2:

MemTotal:       30882976 kB
MemFree:        15823176 kB
MemAvailable:   29227484 kB
Buffers:          464192 kB
Cached:         12749236 kB
SwapCached:         7172 kB
Active:          6505756 kB
Inactive:        7117660 kB
Active(anon):     228320 kB
Inactive(anon):   437960 kB
Active(file):    6277436 kB
Inactive(file):  6679700 kB
Unevictable:        3652 kB
Mlocked:            3652 kB
SwapTotal:      25161724 kB
SwapFree:       25128252 kB
Dirty:                24 kB
Writeback:             0 kB
AnonPages:        409028 kB
Mapped:           132580 kB
Shmem:            253868 kB
Slab:            1309984 kB
SReclaimable:     841984 kB
SUnreclaim:       468000 kB
KernelStack:        5712 kB
PageTables:        12284 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    40603212 kB
Committed_AS:    6292420 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:    294912 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:     1455996 kB
DirectMap2M:    30001152 kB

[justinclift]

Hmmm, maybe the user that docker is running as doesn't have permission to allocate the desired resources?

Are you using standard Docker, or something else (ie podman), etc?

[Mr-AA]

I already tried running pgauto ducker with "sudo" also, same response was coming.

Docker version:
$ docker --version
Docker version 19.03.13, build 4484c46d9d

[justinclift]

Interesting. Does the standard PostgreSQL docker container seem to run ok?

• https://hub.docker.com/_/postgres

Also, which Linux distribution are you running?

I'm wondering if it's something that has restrictions in place (by default) for some purpose.