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.

Sign up for GitHub

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

Jump to bottom

ERROR: You must have read and write access in the current directory #12

Open
darkdecoy opened this issue Aug 17, 2023 · 19 comments
Open

ERROR: You must have read and write access in the current directory #12

darkdecoy opened this issue Aug 17, 2023 · 19 comments

Comments

@darkdecoy
Copy link

When trying to move from postgres 14.7-alpine to pgautoupgrade/pgautoupgrade 15-alpine3.8, I keep getting the error You must have read and write access in the current directory.

Log Output:

Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: mv: can't rename '/var/lib/postgresql/data/old': Invalid argument
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/core' -> '/var/lib/postgresql/data/old/core'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/old' -> '/var/lib/postgresql/data/old/old'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: PostgreSQL 14 database files found, upgrading to PostgreSQL 15
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: Initialising PostgreSQL 15 data directory
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: The files belonging to this database system will be owned by user "matrix".
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: This user must also own the server process.
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: The database cluster will be initialized with locale "C".
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: The default database encoding has accordingly been set to "SQL_ASCII".
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: The default text search configuration will be set to "english".
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: Data page checksums are disabled.
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: fixing permissions on existing directory /var/lib/postgresql/data/new ... ok
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: creating subdirectories ... ok
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: selecting dynamic shared memory implementation ... posix
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: selecting default max_connections ... 100
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: selecting default shared_buffers ... 128MB
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: selecting default time zone ... UTC
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: creating configuration files ... ok
Aug 17 21:33:50 matrix.linuxdelta.com matrix-postgres[281930]: running bootstrap script ... ok
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: performing post-bootstrap initialization ... ok
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: syncing data to disk ... ok
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: Success. You can now start the database server using:
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]:     /usr/local/bin/pg_ctl -D /var/lib/postgresql/data/new/ -l logfile start
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: initdb: warning: enabling "trust" authentication for local connections
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: 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.
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: You must have read and write access in the current directory.
Aug 17 21:33:51 matrix.linuxdelta.com matrix-postgres[281930]: Failure, exiting
Aug 17 21:33:51 matrix.linuxdelta.com systemd[1]: matrix-postgres.service: Main process exited, code=exited, status=1/FAILURE
Aug 17 21:33:51 matrix.linuxdelta.com systemd[1]: matrix-postgres.service: Failed with result 'exit-code'.
Aug 17 21:33:55 matrix.linuxdelta.com systemd[1]: Stopped Postgres server (matrix-postgres).

docker run command:

/usr/bin/env docker run \
                        --rm \
                        --name matrix-postgres \
                        --log-driver=none \
                        --user=1000:1000 \
                        --cap-drop=ALL \
                        --read-only \
                        --tmpfs=/tmp:rw,noexec,nosuid,size=100m \
                        --tmpfs=/run/postgresql:rw,noexec,nosuid,size=100m \
                        --shm-size=256m \
                        --network=matrix \
                        --env-file=/matrix/postgres/env-postgres-server \
                        --mount type=bind,src=/matrix/postgres/data,dst=/var/lib/postgresql/data \
                        --mount type=bind,src=/etc/passwd,dst=/etc/passwd,ro \
                        docker.io/pgautoupgrade/pgautoupgrade:15-alpine3.8 \
                        postgres -c max_connections=200 -c shared_buffers=4GB -c effective_cache_size=12GB -c maintenance_work_mem=1GB -c checkpoint_completion_target=0.9 -c wal_buffers=16MB -c default_statistics_target=100 -c random_page_cost=1.1 -c effective_io_concurrency=200 -c work_mem=5242kB -c min_wal_size=1GB -c max_wal_size=4GB -c max_worker_processes=8 -c max_parallel_workers_per_gather=4 -c max_parallel_workers=8 -c max_parallel_maintenance_workers=4

Docker Environment File:

POSTGRES_USER=matrix
POSTGRES_PASSWORD=
POSTGRES_DB=matrix
POSTGRES_INITDB_ARGS=--lc-collate C --lc-ctype C --encoding UTF8
@justinclift
Copy link
Member

justinclift commented Aug 17, 2023 •
edited
Loading

Interesting. The very first line of the output there seems to be where it started going wrong:

mv: can't rename '/var/lib/postgresql/data/old': Invalid argument

That's a very strange error for it to output, but then keep on going.

Oh... I'm guessing that's probably not the output from the very first run (?), so it probably already has the old directory there from a previous attempt. Maybe.

It looks like it's successfully creating the new subdirectory, and initialising PostgreSQL 15 into that.

But it looks like the whole process is dying shortly afterwards, prior to the pg_upgrade part of the script running.

At a minimum, we should probably add some precautionary checks to the start of the process to make sure there's no existing old or new subdirectories present (indicating a previous failure).

Also, it probably wouldn't hurt to add some more verbose progress logging so that knowing what's going on is easier.

I'll make some updates now, and push a 15-dev tag shortly if you're ok to try again with a data directory that you haven't attempted to upgrade yet?

@justinclift
Copy link
Member

With the mentions of matrix in places, does that mean this database is for the Matrix communications thing?

https://matrix.org

I've not tried that out yet, but if the same problem happens with Matrix databases created for a new setup then that would give me something to experiment with. 😄

@justinclift
Copy link
Member

Hmmm, there seem to be several backend server possibilities:

https://matrix.org/ecosystem/servers/

If this is the correct Matrix thing, please let me know which one I should try out in order to replicate the problem locally. 😄

@darkdecoy
Copy link
Author

Yes this is for a Matrix server that we run for our community. It is setup and configured using this ansible playbook (https://github.com/spantaleev/matrix-docker-ansible-deploy). We need to upgrade the postgres database from 14 to 15 and the documentation is to export the database and then re-import which with a database that is 300 to 400 GB takes a long time so we are looking for a better way to upgrade the database.

@darkdecoy
Copy link
Author

Yeah I would definitely be up for testing changes to see if they work.

@darkdecoy
Copy link
Author

The server that we are using is synapse

@justinclift
Copy link
Member

Thanks. I'm adjusting things with the extra logging at the moment. Need to run the test harness against that (shortly), so probably have something you can try in about 20-30 mins. 😄

@justinclift
Copy link
Member

k. I've just pushed a new docker image with the plain dev tag (eg pgautoupgrade/pgautoupgrade:dev). Please try that with a "clean" data directory you haven't tried upgrading yet, then paste the log output from that.

It's super likely to break in the exact same way as before, but at least now it should output enough info to narrow down WHERE and potentially WHY it's breaking. 😄

@justinclift
Copy link
Member

Oh, I wonder if the You must have read and write access in the current directory. is actually being caused by the Docker image using something silly for the "current directory" when it runs the pg_upgrade command.

The existing logging doesn't report that current directory. I'll fix that now, 1 minute...

@justinclift
Copy link
Member

justinclift commented Aug 17, 2023 •
edited
Loading

I've just pushed a new dev image which might fix the problem. It simply sets the "current directory" to be inside the PG data directory before running pg_upgrade.

The failure really could be that simple to fix. 😄

@darkdecoy
Copy link
Author

I ran with the dev image and it did make it further but it then failed again.

Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: PostgreSQL Database directory appears to contain a database; Skipping initialization
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: *******************************************************************************************
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: Performing PG upgrade on version 14 database files.  Upgrading to version 15
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: *******************************************************************************************
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ----------------------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: Checking for left over artifacts from a failed previous autoupgrade...
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ----------------------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -------------------------------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: No artifacts found from a failed previous autoupgrade.  Continuing the process.
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -------------------------------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: Creating OLD temporary directory /var/lib/postgresql/data/old
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: Moving existing data files into OLD temporary directory
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/PG_VERSION' -> '/var/lib/postgresql/data/old/PG_VERSION'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/base' -> '/var/lib/postgresql/data/old/base'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/core' -> '/var/lib/postgresql/data/old/core'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/global' -> '/var/lib/postgresql/data/old/global'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/old' -> '/var/lib/postgresql/data/old/old'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_commit_ts' -> '/var/lib/postgresql/data/old/pg_commit_ts'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_dynshmem' -> '/var/lib/postgresql/data/old/pg_dynshmem'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_hba.conf' -> '/var/lib/postgresql/data/old/pg_hba.conf'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_ident.conf' -> '/var/lib/postgresql/data/old/pg_ident.conf'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_logical' -> '/var/lib/postgresql/data/old/pg_logical'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_multixact' -> '/var/lib/postgresql/data/old/pg_multixact'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_notify' -> '/var/lib/postgresql/data/old/pg_notify'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_replslot' -> '/var/lib/postgresql/data/old/pg_replslot'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_serial' -> '/var/lib/postgresql/data/old/pg_serial'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_snapshots' -> '/var/lib/postgresql/data/old/pg_snapshots'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_stat' -> '/var/lib/postgresql/data/old/pg_stat'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_stat_tmp' -> '/var/lib/postgresql/data/old/pg_stat_tmp'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_subtrans' -> '/var/lib/postgresql/data/old/pg_subtrans'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_tblspc' -> '/var/lib/postgresql/data/old/pg_tblspc'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_twophase' -> '/var/lib/postgresql/data/old/pg_twophase'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_wal' -> '/var/lib/postgresql/data/old/pg_wal'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/pg_xact' -> '/var/lib/postgresql/data/old/pg_xact'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/postgresql.auto.conf' -> '/var/lib/postgresql/data/old/postgresql.auto.conf'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/postgresql.conf' -> '/var/lib/postgresql/data/old/postgresql.conf'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: '/var/lib/postgresql/data/postmaster.opts' -> '/var/lib/postgresql/data/old/postmaster.opts'
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: Creating NEW temporary directory /var/lib/postgresql/data/new
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: mv: can't rename '/var/lib/postgresql/data/old': Invalid argument
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -----------------------------------------------------------------------
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: PostgreSQL 14 database files found, upgrading to PostgreSQL 15
Aug 18 14:52:36 matrix.linuxdelta.com matrix-postgres[346061]: -----------------------------------------------------------------------
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------------------------------------------------------
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: Old database using collation: 'C'.  Initialising new database with that collation
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------------------------------------------------------
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: Initialising PostgreSQL 15 data directory
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: The files belonging to this database system will be owned by user "matrix".
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: This user must also own the server process.
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: The database cluster will be initialized with locale "C".
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: The default database encoding has accordingly been set to "SQL_ASCII".
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: The default text search configuration will be set to "english".
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: Data page checksums are disabled.
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: fixing permissions on existing directory /var/lib/postgresql/data/new ... ok
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: creating subdirectories ... ok
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: selecting dynamic shared memory implementation ... posix
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: selecting default max_connections ... 100
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: selecting default shared_buffers ... 128MB
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: selecting default time zone ... UTC
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: creating configuration files ... ok
Aug 18 14:52:37 matrix.linuxdelta.com matrix-postgres[346061]: running bootstrap script ... ok
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: performing post-bootstrap initialization ... ok
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: syncing data to disk ... ok
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: Success. You can now start the database server using:
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]:     /usr/local/bin/pg_ctl -D /var/lib/postgresql/data/new/ -l logfile start
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: initdb: warning: enabling "trust" authentication for local connections
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: 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.
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: Running pg_upgrade command, from /var/lib/postgresql/data
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: ---------------------------------------
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: Performing Consistency Checks
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: -----------------------------
Aug 18 14:52:38 matrix.linuxdelta.com matrix-postgres[346061]: Checking cluster versions                                   ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking database user is the install user                  ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking database connection settings                       ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking for prepared transactions                          ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking for system-defined composite types in user tables  ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking for reg* data types in user tables                 ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Checking for contrib/isn with bigint-passing mismatch       ok
Aug 18 14:52:39 matrix.linuxdelta.com matrix-postgres[346061]: Creating dump of global objects                             ok
Aug 18 14:52:42 matrix.linuxdelta.com matrix-postgres[346061]: Creating dump of database schemas                           ok
Aug 18 14:52:43 matrix.linuxdelta.com matrix-postgres[346061]: encodings for database "template1" do not match:  old "UTF8", new "SQL_ASCII"
Aug 18 14:52:43 matrix.linuxdelta.com matrix-postgres[346061]: Failure, exiting
Aug 18 14:52:43 matrix.linuxdelta.com systemd[1]: matrix-postgres.service: Main process exited, code=exited, status=1/FAILURE
Aug 18 14:52:43 matrix.linuxdelta.com systemd[1]: matrix-postgres.service: Failed with result 'exit-code'.

@justinclift
Copy link
Member

Thanks, that's super helpful. 😄

Two messages in the log are standing out to me:

mv: can't rename '/var/lib/postgresql/data/old': Invalid argument

and:

encodings for database "template1" do not match:  old "UTF8", new "SQL_ASCII"

The first one is clearly not fatal, but will need investigating anyway for safety.

The second one clearly is. It's probably a result of the initdb arguments you showed in your original message:

POSTGRES_INITDB_ARGS=--lc-collate C --lc-ctype C --encoding UTF8

I've not personally come across a situation before where UTF8 is used with the C locale. There's nothing really wrong with doing that however.

I'll update the scripting to use that environment variable if present, which should (in theory) make it more robust and also work for this situation. 😄

@justinclift
Copy link
Member

justinclift commented Aug 18, 2023 •
edited
Loading

Btw, after that above failure your entire (working) set of PostgreSQL 14 database files is in the old subdirectory.

You should be able to nuke the new subdirectory (failed upgrade files are in there), then move the contents of the old directory into your main PostgreSQL directory, ready to try again. Oh, delete the now empty old subdirectory after doing that too, as the newer pgautoupgrade scripting will abort if that's present at run time. 😄

@justinclift
Copy link
Member

I'm substantially refactoring the upgrade scripting at the same time, so it'll probably take a few more hours before there's something you can test. 😉

@justinclift
Copy link
Member

@darkdecoy There's a new dev tagged image for you to try.

This one has about an 80% chance of working completely successfully for you. 😄

@darkdecoy
Copy link
Author

darkdecoy commented Aug 21, 2023 •
edited
Loading

This time it did the upgrade and started the database server successfully. Going to do some further tests to ensure that the matrix server continues to function but what I have seen so far this is a much faster upgrade path than exporting the data and then reporting which took like 2 days.

@justinclift
Copy link
Member

Awesome! 😄

Out of curiosity, how long did it take for this pgautoupgrade approach to run (prior to the database start)?

@darkdecoy
Copy link
Author

Started systemd service at Aug 21 22:14:47
Server started and listening at Aug 21 22:15:06

@justinclift
Copy link
Member

19 seconds. Cool, that's a pretty fast upgrade compared to 2 days. 😁

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@justinclift @darkdecoy