Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
RashiqAzhan opened this issue Jul 31, 2023 · 63 comments
Open

Comments

@RashiqAzhan
Copy link

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
Copy link
Member

justinclift commented Jul 31, 2023

Oh, now that's strange. It's supposed to have moved your old data files to an "old" subdirectory prior to doing that, and it's also supposed to have created a brand new subdirectory (called "new" too) to do the initdb into.

# Move the PostgreSQL data files into a subdirectory of the mount point
OLD="${PGDATA}/old"
NEW="${PGDATA}/new"
echo "Moving the old database files prior to pg_upgrade"
mkdir "${OLD}"
if [ ! -d "${OLD}" ]; then
echo "*********************************************************************"
echo "Creation of temporary directory '${OLD}' failed. Aborting completely"
echo "*********************************************************************"
exit 7
fi
mv -v "${PGDATA}"/* "${OLD}"
mkdir "${NEW}"
if [ ! -d "${NEW}" ]; then
echo "********************************************************************"
echo "Creation of temporary directory '${NEW}' failed. Aborting completely"
echo "********************************************************************"
# With a failure at this point we should be able to move the old data back
# to its original location
mv -v "${OLD}"/* "${PGDATA}"
exit 8
fi
chmod 0700 "${OLD}" "${NEW}"
# Return the error handling back to automatically aborting on non-0 exit status
set -e

Which docker image / tag is this happening with?

@RashiqAzhan
Copy link
Author

I'm using postgres:14.5 from here.

I would also like say I have this warning regarding psql.

WARNING: The supplied POSTGRES_PASSWORD is 100+ characters.

  This will not work if used via PGPASSWORD with "psql".

  https://www.postgresql.org/message-id/flat/E1Rqxp2-0004Qt-PL%40wrigleys.postgresql.org (BUG #6412)
  https://github.com/docker-library/postgres/issues/507

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".

Could 100+ character could be a sticking point?

@justinclift
Copy link
Member

justinclift commented Aug 1, 2023

Interesting. I've not personally come across a situation where people were using a 100+ character password, so I didn't know it was even a thing.

I'll have to look into it over the next few days. 😄


On a different note, the docker image you're coming from (postgres:14.5) is not an "alpine" based image. So I'd kind of expect it'll probably not be totally 100% compatible.

You're not the first person to try the pgautoupgrade docker image coming from a non-alpine one, so it's clearly something we should try better to support. That's another thing I'll need to investigate over the next few days as well. 😄

@RashiqAzhan
Copy link
Author

RashiqAzhan commented Aug 1, 2023

Thank you so much. I understand this is an open source project so I greatly appreciate your contribution and time into this.

(Fat fingers, erroneously closed the issue)

@justinclift
Copy link
Member

justinclift commented Aug 1, 2023

No worries at all. Thanks for trying it out, and providing feedback, which is how projects improve over time. 😄

@Heziode
Copy link

Heziode commented Aug 6, 2023

I also got this error, starting from an postgres:11-alpine to pgautoupgrade/pgautoupgrade:latest (currently latest: 15-alpine3.8).

I upgrading a Weblate database.

Here is my log:

PostgreSQL Database directory appears to contain a database; Skipping initialization
******************************************************
Performing PG upgrade on version 11 database files
******************************************************
Moving the old database files prior to pg_upgrade
'/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/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'
PostgreSQL 11 database files found, upgrading to PostgreSQL 15
Initialising PostgreSQL 15 data directory
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/data/new ... 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:
    /usr/local/bin/pg_ctl -D /var/lib/postgresql/data/new/ -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.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:  role "postgres" does not exist
could not connect to source postmaster started with the command:
"/usr/local-pg11/bin/pg_ctl" -w -l "/var/lib/postgresql/data/new/pg_upgrade_output.d/20230806T174251.151/log/pg_upgrade_server.log" -D "/var/lib/postgresql/data/old" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting
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.
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 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.
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 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.
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 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.
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".
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 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.
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.
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 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.
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".
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 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.
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.
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 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.
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 locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
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".
Data page checksums are disabled.
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.
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 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.
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".

And the corresponding DB service into docker-compose:

  database:
    # image: postgres:11-alpine
    image: pgautoupgrade/pgautoupgrade:latest
    container_name: weblate_database
    env_file:
      - ./environment
    volumes:
      - postgres-data:/var/lib/postgresql/data
    restart: always
    networks:
      - backend

environment contains Postgres env (I set fake data, but my password use special chars like @):

POSTGRES_PASSWORD=p@ssw0rd
POSTGRES_USER=weblate
POSTGRES_DATABASE=weblate
POSTGRES_HOST=database
POSTGRES_PORT=

The database is a little bit heavy (the exported SQL of the database is greater than 320mb)

@justinclift
Copy link
Member

Thanks for the bug report. Looking at the log output there, this line is standing out:

connection to server on socket "/var/lib/postgresql/.s.PGSQL.50432" failed: FATAL:
 role "postgres" does not exist

That sounds like your PostgreSQL database uses something other than the "postgres" user as the superuser. Probably weblate by looking at your environment variables. 😄

Co-incidentally, the recent dev tagged image has some support for that. If you try it again, but using the dev tagged image there's a decent chance it'll work.

Would you be ok to try that out and let us know if it does indeed do the job?

If it does, we can promote the dev tag to latest (etc) so others in the same situation have things automatically work for them too. 😄

@Heziode
Copy link

Heziode commented Aug 6, 2023

I tested with dev tag, it works now!

@justinclift
Copy link
Member

justinclift commented Aug 6, 2023

Excellent! I've pushed a rebuilt dev image to 0.5.0, latest, and 15-alpine3.8 tags.

That should fix this problem for others using a similar setup too. 😄

@eurekin
Copy link

eurekin commented Nov 19, 2023

I have a very similar case. I had the same error as this issue description. Found the culprit in my case, logs:

postgres-postgres-1    | ------------------------------------
postgres-postgres-1    | 2023-11-19 22:48:45.170 CET [29] FATAL:  lock file "postmaster.pid" already exists
postgres-postgres-1    | 2023-11-19 22:48:45.170 CET [29] HINT:  Is another postmaster (PID 1) running in data directory "/var/lib/postgresql/data/old"?

After that, postgres wanted to start, but the data directory contained new and old subdirs instead of typical data. After restoring the old content, removing the postmaster.pid it proceeded succesfully without any more issues.

I'm assuming the issue arose due to my auto-upgrade setup. Is watchtower recommended for upgrading the postgres? What could be the cause for dangling pidlock file?

@jjb
Copy link
Member

jjb commented Mar 15, 2024

Hi, thanks so much for this awesome project

I'm getting this error

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.

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".

here's my docker compose

  postgres:
    image: 'pgautoupgrade/pgautoupgrade:16-alpine'
    environment:
      POSTGRES_USER: 'redacted'
      POSTGRES_PASSWORD: 'redacted'
      POSTGRES_DB: 'redacted'
      PGDATA: '/var/lib/postgresql/data/pgdata'
    volumes:
      - 'postgres:/var/lib/postgresql/data'
    ports:
      - '5432'

before the upgrade, image was 'postgres:12-alpine'

any ideas?

@justinclift
Copy link
Member

Hmmm, that sounds like the initdb command the script runs is somehow not being pointed to /var/lib/postgresql/data/new. That's a temporary directory created by the script before hand just for initdb purposes.

The way I tend to debug stuff like this is by starting the container (with this image and the PG data mounted), but tell the script to stop just before the upgrade runs. Then I manually run the lines of the upgrade script 1 by 1 to see where it breaks, and why.

That whole "tell it to stop just before the upgrade script runs" is done by defining the environment variable PGAUTO_DEVEL=before before starting the container. I normally do it from the command line, but you could do the same thing using the docker compose.

If you're up for investigating, that'd be the approach to use. Note, it's not something you'd want to be debugging live on your production database, as the database will be unavailable to other applications meanwhile. ie if you're up for it, then do it in a copy somewhere where it won't affect things

Thoughts? 😄

@jjb
Copy link
Member

jjb commented Mar 15, 2024

will work on that

@justinclift
Copy link
Member

For reference, this is the code to manually step through when trying to figure out what's going wrong:

### The main pgautoupgrade scripting starts here ###
echo "************************************"
echo "PostgreSQL data directory: ${PGDATA}"
echo "************************************"
# Get the version of the PostgreSQL data files
local PGVER=${PGTARGET}
if [ -s "${PGDATA}/PG_VERSION" ]; then
PGVER=$(cat "${PGDATA}/PG_VERSION")
fi
# If the version of PostgreSQL data files doesn't match our desired version, then upgrade them
if [ "${PGVER}" != "${PGTARGET}" ]; then
# Ensure the database files are a version we can upgrade
local RECOGNISED=0
local OLDPATH=unset
if [ "${PGVER}" = "9.5" ] || [ "${PGVER}" = "9.6" ] || [ "${PGVER}" = "10" ] || [ "${PGVER}" = "11" ] || [ "${PGVER}" = "12" ]; then
RECOGNISED=1
fi
if [ "${PGTARGET}" -gt 13 ] && [ "${PGVER}" = "13" ]; then
RECOGNISED=1
fi
if [ "${PGTARGET}" -gt 14 ] && [ "${PGVER}" = "14" ]; then
RECOGNISED=1
fi
if [ "${PGTARGET}" -gt 15 ] && [ "${PGVER}" = "15" ]; then
RECOGNISED=1
fi
if [ "${RECOGNISED}" -eq 1 ]; then
OLDPATH="/usr/local-pg${PGVER}"
echo "*******************************************************************************************"
echo "Performing PG upgrade on version ${PGVER} database files. Upgrading to version ${PGTARGET}"
echo "*******************************************************************************************"
else
echo "****************************************************************************"
echo "Unrecognised version of PostgreSQL database files found, aborting completely"
echo "****************************************************************************"
exit 9
fi
# Check for presence of old/new directories, indicating a failed previous autoupgrade
echo "----------------------------------------------------------------------"
echo "Checking for left over artifacts from a failed previous autoupgrade..."
echo "----------------------------------------------------------------------"
local OLD="${PGDATA}/old"
local NEW="${PGDATA}/new"
if [ -d "${OLD}" ]; then
echo "*****************************************"
echo "Left over OLD directory found. Aborting."
echo "*****************************************"
exit 10
fi
if [ -d "${NEW}" ]; then
echo "*****************************************"
echo "Left over NEW directory found. Aborting."
echo "*****************************************"
exit 11
fi
echo "-------------------------------------------------------------------------------"
echo "No artifacts found from a failed previous autoupgrade. Continuing the process."
echo "-------------------------------------------------------------------------------"
# Don't automatically abort on non-0 exit status, as that messes with these upcoming mv commands
set +e
# Move the PostgreSQL data files into a subdirectory of the mount point
echo "---------------------------------------"
echo "Creating OLD temporary directory ${OLD}"
echo "---------------------------------------"
mkdir "${OLD}"
if [ ! -d "${OLD}" ]; then
echo "*********************************************************************"
echo "Creation of temporary directory '${OLD}' failed. Aborting completely"
echo "*********************************************************************"
exit 7
fi
echo "--------------------------------------------"
echo "Creating OLD temporary directory is complete"
echo "--------------------------------------------"
echo "-------------------------------------------------------"
echo "Moving existing data files into OLD temporary directory"
echo "-------------------------------------------------------"
mv -v "${PGDATA}"/* "${OLD}"
echo "-------------------------------------------------------------------"
echo "Moving existing data files into OLD temporary directory is complete"
echo "-------------------------------------------------------------------"
echo "---------------------------------------"
echo "Creating NEW temporary directory ${NEW}"
echo "---------------------------------------"
mkdir "${NEW}"
if [ ! -d "${NEW}" ]; then
echo "********************************************************************"
echo "Creation of temporary directory '${NEW}' failed. Aborting completely"
echo "********************************************************************"
# With a failure at this point we should be able to move the old data back
# to its original location
mv -v "${OLD}"/* "${PGDATA}"
exit 8
fi
echo "--------------------------------------------"
echo "Creating NEW temporary directory is complete"
echo "--------------------------------------------"
echo "-----------------------------------------------------"
echo "Changing permissions of temporary directories to 0700"
echo "-----------------------------------------------------"
chmod 0700 "${OLD}" "${NEW}"
echo "---------------------------------------------------------"
echo "Changing permissions of temporary directories is complete"
echo "---------------------------------------------------------"
# Return the error handling back to automatically aborting on non-0 exit status
set -e
# If no initdb arguments were passed to us from the environment, then work out something valid ourselves
if [ "x${POSTGRES_INITDB_ARGS}" != "x" ]; then
echo "------------------------------------------------------------------------------"
echo "Using initdb arguments passed in from the environment: ${POSTGRES_INITDB_ARGS}"
echo "------------------------------------------------------------------------------"
else
echo "------------------------------------"
echo "Determining our own initdb arguments"
echo "------------------------------------"
local COLLATE=unset
local CTYPE=unset
local ENCODING=unset
COLLATE=$(echo 'SHOW LC_COLLATE' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" | grep 'lc_collate = "' | cut -d '"' -f 2)
CTYPE=$(echo 'SHOW LC_CTYPE' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" | grep 'lc_ctype = "' | cut -d '"' -f 2)
ENCODING=$(echo 'SHOW SERVER_ENCODING' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" | grep 'server_encoding = "' | cut -d '"' -f 2)
POSTGRES_INITDB_ARGS="--locale=${COLLATE} --lc-collate=${COLLATE} --lc-ctype=${CTYPE} --encoding=${ENCODING}"
echo "---------------------------------------------------------------"
echo "The initdb arguments we determined are: ${POSTGRES_INITDB_ARGS}"
echo "---------------------------------------------------------------"
fi
# Initialise the new PostgreSQL database directory
echo "--------------------------------------------------------------------------------------------------------------------"
echo "Old database using collation settings: '${POSTGRES_INITDB_ARGS}'. Initialising new database with those settings too"
echo "--------------------------------------------------------------------------------------------------------------------"
initdb_locale "${POSTGRES_INITDB_ARGS}"
echo "------------------------------------"
echo "New database initialisation complete"
echo "------------------------------------"
# Change into the PostgreSQL database directory, to avoid a pg_upgrade error about write permissions
cd "${PGDATA}"
# Run the pg_upgrade command itself
echo "---------------------------------------"
echo "Running pg_upgrade command, from $(pwd)"
echo "---------------------------------------"
/usr/local/bin/pg_upgrade --username="${POSTGRES_USER}" --link -d "${OLD}" -D "${NEW}" -b "${OLDPATH}/bin" -B /usr/local/bin
echo "--------------------------------------"
echo "Running pg_upgrade command is complete"
echo "--------------------------------------"
# Move the new database files into place
echo "-----------------------------------------------------"
echo "Moving the new database files to the active directory"
echo "-----------------------------------------------------"
mv -v "${NEW}"/* "${PGDATA}"
echo "-----------------------------------------"
echo "Moving the new database files is complete"
echo "-----------------------------------------"
# Re-use the pg_hba.conf and pg_ident.conf from the old data directory
echo "--------------------------------------------------------------"
echo "Copying the old pg_hba and pg_ident configuration files across"
echo "--------------------------------------------------------------"
cp -f "${OLD}/pg_hba.conf" "${OLD}/pg_ident.conf" "${PGDATA}"
echo "-------------------------------------------------------------------"
echo "Copying the old pg_hba and pg_ident configuration files is complete"
echo "-------------------------------------------------------------------"
# Remove the left over database files
echo "---------------------------------"
echo "Removing left over database files"
echo "---------------------------------"
rm -rf "${OLD}" "${NEW}" ~/delete_old_cluster.sh
echo "---------------------------------------------"
echo "Removing left over database files is complete"
echo "---------------------------------------------"
echo "**********************************************************"
echo "Automatic upgrade process finished with no errors reported"
echo "**********************************************************"
fi
### The main pgautoupgrade scripting ends here ###

And yep, it's copiously commented and tries to be as straight forward as possible. ie tries to be easy to see what it's doing at all times

@jjb
Copy link
Member

jjb commented Mar 15, 2024

thanks!

so, the place where i have the problem is a hosted environment that doesn't have great logs and such. but i can push whatever i want there and then ssh in while it's running.

i'm not the greatest at docker - here's how i'm trying to deploy without doing any upgrades: jjb@abbf1e3

that failed, without any logs (don't ask). any ideas?

@justinclift
Copy link
Member

Ahhh. An important concept with Docker is that a container only runs while there's a main process running. If that main process ends, then the docker container will exit thinking it's work has been done.

By commenting out those three lines, you've most likely stopped the PostgreSQL (PG) server from launching... so there's nothing left to run and the container just exits.

To work around that, add a never ending while loop to the end of that docker entry point script:

while :; do
    sleep 5
done

That'll leave the container running, then with another instance of your terminal program you can jump into the running container:

$ docker exec -it CONTAINER_NAME /bin/bash

From there, you can switch to the postgres user and prod things as needed.

Does that help? 😄

@jjb
Copy link
Member

jjb commented Mar 15, 2024

never mind, i see the entrypoint needs to run... will figure out a better way to NOOP it

@jjb
Copy link
Member

jjb commented Mar 15, 2024

haha posted that at the same time your message came in

thanks for the suggestion! in my case, pg does need to come up so healtchchecks are good, otherwise i can't ssh in. so i just need to NOOP the upgrade and allow postgres to run. looking into making a clean short circuit now.

@justinclift
Copy link
Member

Cool, all good. 😄

@jjb
Copy link
Member

jjb commented Mar 15, 2024

🤷 jjb@24b809b

@jjb
Copy link
Member

jjb commented Mar 15, 2024

"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise. See the documentation for
more information on how to properly start the server.

@justinclift
Copy link
Member

Hmmm, if you want to have PostgreSQL run without the upgrade happening, you could just comment out the whole upgrade piece. Or you could muck around with the test that it uses for determining if the upgrade should happen:

https://github.com/jjb/docker-pgautoupgrade/blob/24b809bcd873cf951303460d15dc0274f3c80fc8/docker-entrypoint.sh#L375

Changing that line to something like this would stop the upgrade from automatically running too:

if [ "one" != "two" ]; then

^^^ that'll always fail, so the stuff inside the then clause won't run.

@justinclift
Copy link
Member

justinclift commented Mar 15, 2024

"root" execution of the PostgreSQL server is not permitted.

Is that when trying to manually start PostgreSQL after you've jumped into a running container? If so, then switch to the postgres user first (su - postgres).

@jjb
Copy link
Member

jjb commented Mar 15, 2024

trying this at the end of the entrypoint script

exec su-exec postgres "$@"

@justinclift
Copy link
Member

Hmmm, that doesn't sound like the right approach (to me).


Oh, there might be a conceptual misunderstanding.

For the upgrade pieces to run, it needs to happen when the PostgreSQL container itself is running... but the PostgreSQL server software inside the container has to be shut down.

That's why the upgrade scripting check and commands are before the entry point script starts PostgreSQL. That way, the upgrade is all taken care of and done before PostgreSQL starts.

Does that make sense, or am I misunderstanding the approach you're taking? 😄

@jjb
Copy link
Member

jjb commented Mar 15, 2024

sure, that makes sense

but, can't i still hack around on the running instance? (this is all in a throw-away environment) either by first shutting down pg, or just not caring and plowing over files to test the commands?

@jjb
Copy link
Member

jjb commented Mar 15, 2024

postgres: could not access the server configuration file "/var/lib/postgresql/data/pgdata/postgresql.conf": No such file or directory

i'll try your one=two approach

@jjb
Copy link
Member

jjb commented Mar 15, 2024

sounds good, thanks! but i think maybe you missed the error message i got when i tried my hack - see the text under the image here #7 (comment)

@jjb
Copy link
Member

jjb commented Mar 15, 2024

so the false change doesn't NOOP the whole script, this stuff still runs, i don't understand the system enough to know why

	if [ "$1" = 'postgres' ] && ! _pg_want_help "$@"; then
		docker_setup_env
		# setup data directories and permissions (when run as root)
		docker_create_db_directories

@justinclift
Copy link
Member

justinclift commented Mar 15, 2024

Ahhh. Hmmm, that error message isn't making sense to me if that's the only change to the script.


Oh, it sounds like it's erroring out before it even gets to the upgrade piece.

Just as an experiment, I'd try passing in an empty directory for your PostgreSQL data directory and see if that script still errors out or if the initdb piece runs ok.

It could turn out to be something else unexpected is going on.

@jjb
Copy link
Member

jjb commented Mar 15, 2024

okay, trying this

_main() {
	mkdir /tmp/my-empty-dir
    environment:
      PGDATA: '/tmp/my-empty-dir'
    volumes:
      - 'postgres:/tmp/my-empty-dir'

@justinclift
Copy link
Member

Hmmm, you might want to leave off the volumes line (and the one below it), otherwise I think it's going to still mount your local PG data into the container at that location.

@jjb
Copy link
Member

jjb commented Mar 15, 2024

okay, just nuke the whole volumes section?

@justinclift
Copy link
Member

Yep, for testing purposes, just to see if the script runs ok with it gone. 😄

@justinclift
Copy link
Member

It looks like it's getting towards 8pm your time, and I'm starting to need sleep pretty badly myself.

If you're up for it, we can schedule a time to investigate this properly over Skype or something when we're both fully awake? 😄

@jjb
Copy link
Member

jjb commented Mar 15, 2024

wow, thanks for the offer - although i fear the turnaround time on each experiment will be too long, so maybe we should get me a working shell first. you can find my email off of my website linked here https://github.com/jjb

@jjb
Copy link
Member

jjb commented Mar 15, 2024

progress! and now i realized i should be using my existing pg version image 🤦

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


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.
Success. You can now start the database server using:

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

waiting for server to start....2024-03-15 23:58:01.233 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-15 23:58:01.237 UTC [39] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-03-15 23:58:01.249 UTC [42] LOG:  database system was shut down at 2024-03-15 23:57:57 UTC
2024-03-15 23:58:01.257 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-15 23:58:01.394 UTC [39] LOG:  received fast shutdown request
2024-03-15 23:58:01.399 UTC [39] LOG:  aborting any active transactions
2024-03-15 23:58:01.401 UTC [39] LOG:  background worker "logical replication launcher" (PID 45) exited with exit code 1
2024-03-15 23:58:01.401 UTC [40] LOG:  shutting down
2024-03-15 23:58:01.405 UTC [40] LOG:  checkpoint starting: shutdown immediate
.2024-03-15 23:58:02.454 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.053 s; sync files=301, longest=0.014 s, average=0.004 s; distance=4327 kB, estimate=4327 kB; lsn=0/19495A8, redo lsn=0/19495A8
2024-03-15 23:58:02.460 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
****************************************************************************

@jjb
Copy link
Member

jjb commented Mar 16, 2024

my existing version: 12 🫠

@justinclift
Copy link
Member

Ok, well that gets it a bit closer. 😄

I've just emailed you (Ping... as subject), so we can figure things out when you're up for it. 😁

@justinclift
Copy link
Member

Heh Heh Heh. 12 isn't the worst I've seen. I started this project due to needing to upgrade a bunch of 9.something production systems. 😁

@jjb
Copy link
Member

jjb commented Mar 16, 2024

🫠 isn't because it's old but because your upgrade image doesn't support it... so i can't start up my disabled fork for 12.. right? but maybe i can simply hack your build scripts easily enough

@justinclift
Copy link
Member

justinclift commented Mar 16, 2024

Heh Heh Heh. Is that false check still in place, so it's skipping the upgrade scripting?

The pgautoupgrade container should definitely support upgrading from PG 12.

@jjb
Copy link
Member

jjb commented Mar 16, 2024

seems to be building just fine (will be half an hour because building AMD on my ARM mac)

@jjb
Copy link
Member

jjb commented Mar 16, 2024

The pgautoupgrade container should definitely support upgrading from PG 12.

right but the makefile doesn't support building PGTARGET=12 (upgrading to 12)

but changing up the invocation was trivial, so i think we are off to the races

@justinclift
Copy link
Member

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. 😄

@jjb
Copy link
Member

jjb commented Mar 16, 2024

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
Copy link
Member

justinclift commented Mar 16, 2024

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. 😉

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
Copy link
Member

jjb commented Mar 16, 2024

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
Copy link
Member

jjb commented Mar 16, 2024

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
Copy link
Member

jjb commented Mar 16, 2024

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
Copy link
Member

jjb commented Mar 16, 2024

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
Copy link
Member

jjb commented Mar 16, 2024

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

@justinclift
Copy link
Member

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. 😄

@justinclift
Copy link
Member

I've just added support for migrating to PG 12 (7ffe5e7), 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. 😄

@justinclift
Copy link
Member

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 (ba99db1) and (c869388).

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

@FireMasterK
Copy link

FireMasterK commented Sep 8, 2024

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. 🤔

@uwekoenig
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

7 participants