homebrew-postgresql
homebrew-postgresql copied to clipboard
Published
20 hours ago •
petere
petere
postgresql 14 fails to start after upgrading from postgresql 13
% uname -mrsv
Darwin 20.6.0 Darwin Kernel Version 20.6.0: Mon Aug 30 06:12:21 PDT 2021; root:xnu-7195.141.6~3/RELEASE_X86_64 x86_64
relevant log lines from /usr/local/var/log/postgres.log
2021-10-11 09:49:12.206 CDT [1347] LOG: server process (PID 91405) was terminated by signal 9: Killed: 9
2021-10-11 09:49:12.206 CDT [1347] LOG: terminating any other active server processes
2021-10-11 09:49:12.206 CDT [1461] WARNING: terminating connection because of crash of another server process
2021-10-11 09:49:12.206 CDT [1461] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-10-11 09:49:12.206 CDT [1461] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-10-11 09:49:12.218 CDT [1347] LOG: all server processes terminated; reinitializing
2021-10-11 09:49:12.230 CDT [1347] LOG: startup process (PID 91406) was terminated by signal 9: Killed: 9
2021-10-11 09:49:12.230 CDT [1347] LOG: aborting startup due to startup process failure
2021-10-11 09:49:12.232 CDT [1347] LOG: database system is shut down
2021-10-11 09:49:12.660 CDT [91407] FATAL: database files are incompatible with server
2021-10-11 09:49:12.660 CDT [91407] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0.
brew postgresql-upgrade-database:
% brew postgresql-upgrade-database
==> Upgrading postgresql data from 13 to 14...
waiting for server to start....2021-10-11 10:28:17.679 CDT [6759] LOG: starting PostgreSQL 13.4 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 13.0.0 (clang-1300.0.29.3), 64-bit
2021-10-11 10:28:17.681 CDT [6759] LOG: listening on IPv4 address "127.0.0.1", port 5432
2021-10-11 10:28:17.681 CDT [6759] LOG: listening on IPv6 address "::1", port 5432
2021-10-11 10:28:17.682 CDT [6759] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-10-11 10:28:17.695 CDT [6760] LOG: database system was shut down at 2021-10-11 10:22:01 CDT
2021-10-11 10:28:17.700 CDT [6759] LOG: database system is ready to accept connections
done
server started
waiting for server to shut down....2021-10-11 10:28:17.922 CDT [6759] LOG: received fast shutdown request
2021-10-11 10:28:17.923 CDT [6759] LOG: aborting any active transactions
2021-10-11 10:28:17.924 CDT [6759] LOG: background worker "logical replication launcher" (PID 6766) exited with exit code 1
2021-10-11 10:28:17.924 CDT [6761] LOG: shutting down
2021-10-11 10:28:17.935 CDT [6759] LOG: database system is shut down
done
server stopped
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "thomas".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Chicago
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
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:
/usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start
==> Migrating and upgrading data...
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster
*failure*
Consult the last few lines of "pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting
Error: Upgrading postgresql data from 13 to 14 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres...
Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@13/13.4/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 12` exited with 1.
last lines from /usr/local/var/log/pg_upgrade_utility.log:
command: "/usr/local/opt/postgresql/bin/pg_resetwal" -o 836127 "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
pg_resetwal: error: lock file "postmaster.pid" exists
pg_resetwal: Is a server running? If not, delete the lock file and try again.
no postmaster.pid:
% ls /usr/local/var/postgres/postmaster.pid
ls: /usr/local/var/postgres/postmaster.pid: No such file or directory
Any guidance would be appreciated!
Were you ever able to work around this? I am having exactly the same issue..
Unfortunately not, had to install postgresql@13 and copy the var/postgres directory to var/postgresql@13, stop my postgresql service, start postgresql@13, and I got back my database
Darn! But thanks much for the helpful workaround, I hadn't gotten to the point of figuring that out yet. :-)
It sure looks like the same issue as this one: https://github.com/Homebrew/homebrew-core/issues/73818
But the solution that worked there (changing the call to pg_upgrade and setting -j to 1 instead of Hardware::CPU.cores) does not seem to help...
brew postgresql-upgrade-database is not something from this repository. You should report issues with it to the Homebrew core repositories.
