Usage with DB data in a volume?

[jpbaril]

How to use this tool with database data that is stored in a Docker volume and not a bind directory ?

[justinclift]

No idea (yet). If the database directory is available inside the container at some path (ie /foo/bar), then in theory things should work anyway if your PGDATA environment variable is set to that path. Along the lines of:

$ docker compose -e PGDATA=/foo/bar pgautoupgrade/pgautoupgrade:latest

Does that help? 😄

[jpbaril]

If the database directory is available inside the container at some path

That's indeed where the challenge lies.

I tried to map it directly to the Docker volume in /var/lib/docker/volumes/.

It seemed to work at first but I now face another issue. I get this:
"connection to server on socket "/var/run/postgresql/.s.PGSQL.50432" failed: FATAL: role "postgres" does not exist"

FYI, I think my DB has instead a role that is the same as the app name.

[justinclift]

No worries. As a potentially useful data point, if you're familiar with shell scripting then the pgautoupgrade container is 99% the standard Docker PostgreSQL container with some shell scripting added in the right strategic spot:

docker-pgautoupgrade/docker-entrypoint.sh

Lines 371 to 585 in e8a81ee

	### 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 [ "${PGTARGET}" -gt 16 ] && [ "${PGVER}" = "16" ]; 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 "Remove postmaster.pid file from PG data directory"
	echo "-------------------------------------------------"
	rm -f "${OLD}"/postmaster.pid
	echo "------------------------------------"
	echo "Determining our own initdb arguments"
	echo "------------------------------------"
	local COLLATE=unset
	local CTYPE=unset
	local ENCODING=unset
	ENCODING=$(echo 'SHOW SERVER_ENCODING' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" "${POSTGRES_DB}" | grep 'server_encoding = "' | cut -d '"' -f 2)
	# LC_COLLATE and LC_TYPE have been removed with PG v16
	# https://www.postgresql.org/docs/release/16.0/
	if [ "${PGVER}" -lt 16 ]; then
	COLLATE=$(echo 'SHOW LC_COLLATE' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" "${POSTGRES_DB}" | grep 'lc_collate = "' | cut -d '"' -f 2)
	CTYPE=$(echo 'SHOW LC_CTYPE' | "${OLDPATH}/bin/postgres" --single -D "${OLD}" "${POSTGRES_DB}" | grep 'lc_ctype = "' | cut -d '"' -f 2)
	POSTGRES_INITDB_ARGS="--locale=${COLLATE} --lc-collate=${COLLATE} --lc-ctype=${CTYPE} --encoding=${ENCODING}"
	else
	POSTGRES_INITDB_ARGS="--encoding=${ENCODING}"
	fi
	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 "---------------------------------------"
	bin_path=$(get_bin_path)
	"${bin_path}/pg_upgrade" --username="${POSTGRES_USER}" --link -d "${OLD}" -D "${NEW}" -b "${OLDPATH}/bin" -B "${bin_path}" --socketdir="/var/run/postgresql"
	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 "---------------------------------------------"
	UPGRADE_PERFORMED=1
	echo "***************************************************************************************"
	echo "Automatic upgrade process finished upgrading the data format to PostgreSQL ${PGTARGET}."
	echo "The database has not yet been reindexed nor updated the query planner stats. Those "
	echo "will be done by a background task shortly. "
	echo "***************************************************************************************"
	fi
	### The main pgautoupgrade scripting ends here ###

Mentioning that because there are several other environment variables used there (we should document those @spwoodcock?). The one you're probably after is the POSTGRES_USER variable, used on (at least) line 544.

So, give that a go and see if things work? 😄

[jpbaril]

Yeah, I did search for such environmental variable but it looks like I did not look carefully enough.

Still, it did not work.
Now I have this error message in log file "/var/lib/postgresql/data/new/pg_upgrade_output.d/20241006T045617.218/log/pg_upgrade_utility.log":

vacuumdb: vacuuming database "postgres"
vacuumdb: error: processing of database "postgres" failed: ERROR:  unexpected data beyond EOF in block 19 of relation base/5/2619
HINT:  This has been seen to occur with buggy kernels; consider updating your system.

[justinclift]

Uh, that's not good. That seems to be saying the on disk format for your database is corrupted. 😦

---

Hmmm, we don't yet have a way of skipping the (recently added) background tasks that automatically run after the main part of the upgrade process. Those background tasks are things like vacuum analyze and database reindexing.

What you could do, is start your docker compose with the PGAUTO_DEVEL=server environment variable (in addition to the others).

That's meant for development, but will run the main upgrade process then pause before starting the further background tasks or the main PostgreSQL process. So, you could start the container with the above argument (and keep an eye on the logs), then when the upgrade is done you tell docker to shut down the container.

In theory that should result in a database with upgraded data files, but without vacuum (etc) having been run on the result. You'd want to manually check if the database is ok at that point (ie does it start, etc), and you'd probably also want to then manually run vacuum analyze and index database on all of the databases.

[jpbaril]

That seems to be saying the on disk format for your database is corrupted. 😦

ok. So I just retried with the backuped original data and now with the use of the required environmental variable it works beautifully. Thanks.

[spwoodcock]

Using a docker volume instead of a bind mount should be as simple as:

--volume name-of-volume:/var/lib/postgresql/data

Or if using compose:

volumes:
  db-data-volume:
services:
  upgrade:
    volumes:
      - db-data-volume:/var/lib/postgresql/data

---

Agree @justinclift it would be great to have env vars / config options documented on a wiki page. I'm currently on holidays, but back in a week & will hopefully have some time for writing up some docs 👍

[jpbaril]

Using a docker volume instead of a bind mount should be as simple as:

--volume name-of-volume:/var/lib/postgresql/data

Or if using compose:

volumes:
  db-data-volume:
services:
  upgrade:
    volumes:
      - db-data-volume:/var/lib/postgresql/data

In the end as I had mentioned I just used direct path to data in /var/lib/docker/volumes/ even if not ideal.
Yet, for the compose version, I'm not sure and did not test it but would it not also need "external: true" since the volume would have already been created by another container ?

[spwoodcock]

Using the docker volume directory for bind mounts is not advised, as it could cause permission issues or even corruption.

But yes you are right, if the docker volume is already created you should reference the volume like this:

volumes:
  db-data-volume:
    external: true
    name: the-name-of-the-existing-volume
services:
  upgrade:
    volumes:
      - db-data-volume:/var/lib/postgresql/data