Upgrading PostgreSQL to a major version in Archlinux

upgrading_pg.md

Upgrading PostgreSQL to a major version in Archlinux

# pacman -S postgresql-old-upgrade

# systemctl stop postgresql.service

# mv /var/lib/postgres/data /var/lib/postgres/olddata

# mkdir /var/lib/postgres/data /var/lib/postgres/tmp

# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp

# su - postgres

[postgres]$ initdb -D '/var/lib/postgres/data'

[postgres]$ cd /var/lib/postgres/tmp

[postgres]$ pg_upgrade -b /opt/pgsql-`OLD_PG_VERSION`/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

# systemctl start postgresql

[postgres]$ /usr/bin/vacuumdb --all --analyze-in-stages

Logout from postgres user and execute

# /var/lib/postgres/tmp/delete_old_cluster.sh

or manually do

# rm -rf /var/lib/postgres/olddata

Once the upgrade is complete you may delete the `/var/lib/postgres/tmp` directory

# rm -rf /var/lib/postgres/tmp

Errors when upgrading to a major version

* template0 must not allow connections, i.e. its pg_database.datallowconn must
be false

* connection to database failed: FATAL:  database "template1" is not currently
accepting connections

This happens when the column datallowconn from pg_database is set to true in template0 and in template1 is set to false

[postgres] > SELECT datname, datallowconn FROM pg_database;

            datname             | datallowconn
--------------------------------+--------------
 template0                      | t
 template1                      | f

Solution

# su - postgres

$ /opt/pgsql-`PG_OLD_VERSION`/bin/pg_ctl -D /var/lib/postgres/olddata/ start

$ /opt/pgsql-`PG_OLD_VERSION`/bin/psql -U pg-user -d postgres

[postgres] > UPDATE pg_database SET datallowconn='false' WHERE datname='template0';

[postgres] > UPDATE pg_database SET datallowconn='true' WHERE datname='template1';

$ /opt/pgsql-`PG_OLD_VERSION`/bin/pg_ctl -D /var/lib/postgres/olddata/ stop

very helpful! In addition, when encountering encoding problem between UTF-8 and SQL_ASCII you can initdb like this:

initdb --locale en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data

Thank you 👍

I ran into that template1-error and you saved my day, thank you! :D