Upgrading a PostgreSQL Server

The following are my notes on how to upgrade a PostgreSQL server. A few things to consider:

  • I run a CentOS machine - you should be able to follow along with most OSs and package managers, though.
  • This is a v11 to v12 upgrade - if this is not the case for you, just edit the commands.
  • This upgrade strategy will incur on some database downtime - this should be minimal if you properly prepare.
$ yum install
$ yum install postgresql12-server
$ yum install postgresql12-contrib (needed for some pg extensions)

$ systemctl stop postgresql-11

$ su - postgres

$ /usr/pgsql-12/bin/initdb -D /var/lib/pgsql/12/data/

// Optional: psql -> CREATE EXTENSION pg_stat_statements;

// Notice the --link flag, without this the uprade will take much longer and duplicate data across the filesystem.
$ /usr/pgsql-12/bin/pg_upgrade --old-datadir /var/lib/pgsql/11/data/ --new-datadir /var/lib/pgsql/12/data/ --old-bindir /usr/pgsql-11/bin/ --new-bindir /usr/pgsql-12/bin/ --link

// Edit /var/lib/pgsql/12/data/pg_hba.conf and copy settings from previous version.
// Edit /var/lib/pgsql/12/data/postgresql.conf and copy settings from previous version - Or visit take the opportunity to use PGTune.
// Make sure to address the following:
// listen_addresses = '*' (this will allow remote connections, so make sure you know what you are doing).
// log_timezone = 'UTC'
// timezone = 'UTC'

(switch back to root)
$ systemctl start postgresql-12.service

$ su - postgres

// Depending on the size and nature of your database, this could take quite a while to run, but at this point the database is up and running!
$ ./

(switch back to root)
$ systemctl enable postgresql-12

$ su - postgres
$ ./
comments powered by Disqus