Today I had the pleasure to have Bruce Momjian in my session about PostgreSQL Upgrade Best Practices at the IT Tage 2017 in Frankfurt. While browsing through the various options you have for upgrading there was one slide where I claimed that the old cluster needs to be down before you run pg_upgrade in check mode as you will hit a (non-critical) failure message otherwise. Lets see if that really is the case or I did something wrong…
To start with lets initialize a new 9.6.2 cluster:
postgres@pgbox:/home/postgres/ [PG962] initdb --version initdb (PostgreSQL) 9.6.2 dbi services build postgres@pgbox:/home/postgres/ [PG962] initdb -D /tmp/aaa 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 locales COLLATE: en_US.UTF-8 CTYPE: en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: de_CH.UTF-8 NUMERIC: de_CH.UTF-8 TIME: de_CH.UTF-8 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. creating directory /tmp/aaa ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok 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: pg_ctl -D /tmp/aaa -l logfile start
Start that:
postgres@pgbox:/home/postgres/ [PG962] pg_ctl -D /tmp/aaa -l logfile start postgres@pgbox:/home/postgres/ [PG962] psql -c "select version()" postgres version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.2 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit (1 row) Time: 0.861 ms
For being able to upgrade we’ll need a new cluster, so:
postgres@pgbox:/home/postgres/ [PG10] initdb --version initdb (PostgreSQL) 10.0 dbi services build postgres@pgbox:/home/postgres/ [PG10] initdb -D /tmp/bbb 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 locales COLLATE: en_US.UTF-8 CTYPE: en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: de_CH.UTF-8 NUMERIC: de_CH.UTF-8 TIME: de_CH.UTF-8 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. creating directory /tmp/bbb ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok 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: pg_ctl -D /tmp/bbb -l logfile start
We’ll not start that one but will just run pg_upgrade in check mode from the new binaries:
postgres@pgbox:/home/postgres/ [PG10] pg_upgrade --version pg_upgrade (PostgreSQL) 10.0 dbi services build postgres@pgbox:/home/postgres/ [PG10] export PGDATAOLD=/tmp/aaa postgres@pgbox:/home/postgres/ [PG10] export PGDATANEW=/tmp/bbb postgres@pgbox:/home/postgres/ [PG10] export PGBINOLD=/u01/app/postgres/product/96/db_2/bin/ postgres@pgbox:/home/postgres/ [PG10] export PGBINNEW=/u01/app/postgres/product/10/db_0/bin/ postgres@pgbox:/home/postgres/ [PG10] pg_upgrade -c *failure* Consult the last few lines of "pg_upgrade_server.log" for ...
… and here we go. From the log:
postgres@pgbox:/home/postgres/ [PG10] cat pg_upgrade_server.log ----------------------------------------------------------------- pg_upgrade run on Tue Dec 12 21:23:43 2017 ----------------------------------------------------------------- command: "/u01/app/postgres/product/96/db_2/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/tmp/aaa" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1 pg_ctl: another server might be running; trying to start server anyway waiting for server to start....FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID 2194) running in data directory "/tmp/aaa"? stopped waiting pg_ctl: could not start server Examine the log output.
So, @Bruce: Something to improve 🙂
Again: It was a pleasure to have you there and I hope we’ll meet again at one of the conferences in 2018.
Update 09-JAN-2017 (see comments below): Patch here: https://git.postgresql.org/pg/commitdiff/d25ee30031b08ad1348a090914c2af6bc640a832