Upgrading PostgreSQL

2020-05-28

Time marches on and one day you find that the shiny new Postgres you originally installed is nearing its end of life. Time to upgrade. Since I always forget how to do this, I'm going to save the two pages of instructions needed here. Add the repository from the first page, then go through the steps on the second one. Now you're up to date.

Copying here because links go dead sometimes.

First add the repository to your apt sources, then install the specific version you want.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Note: apt-key is deprecated and the apt-key man page recommends the following:
# Instead of using this command a keyring should be placed directly in the 
# /etc/apt/trusted.gpg.d/ directory with a descriptive name and either "gpg" or "asc" as file extension.
sudo su root
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc > /etc/trusted.gpg.d/postgresql.asc
exit

sudo apt-get update
sudo apt-get -y install postgresql-15

Now we need to upgrade our existing cluster to the new version.

Stop the postgresql service

sudo service postgresql stop

Rename the new versions cluster

sudo pg_renamecluster 15 main main_pristine

Upgrade the old cluster

sudo pg_upgradecluster 14 main

Restart and check the status of the clusters

sudo service start postgresql
pg_lsclusters

Drop the old cluster and unused new version cluster

sudo pg_dropcluster 14 main --stop
sudo pg_dropcluster 15 main_pristine --stop

For Ubuntu the config files are stored at /etc/postgresql/15/main by default (replace 15 with the version number and main with the name of the cluster if they're different).

The pg_* commands seem to get copied / linked to /usr/bin so they should be available. The data directory should be at /var/lib/postgresql/15/main

CentOS

Things work a little differently on CentOS and other yum based systems. These notes are condensed from here

Install directly from the PostgreSQL yum repository. Replace 15, 15.1 with the appropriate version number.

# server
sudo yum install https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-server-15.1-1PGDG.rhel7.x86_64.rpm


# client programs and libraries
sudo yum install https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-15.1-1PGDG.rhel7.x86_64.rpm

# these two may be redundant if you've installed the previous version
# contrib
sudo yum install https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-contrib-15.1-1PGDG.rhel7.x86_64.rpm

# libs
sudo yum install https://yum.postgresql.org/15/redhat/rhel-7-x86_64/postgresql15-libs-15.1-1PGDG.rhel7.x86_64.rpm

Once installed find client programs under /usr/pgsql-15. Add this to your PATH for ease of use. Data dir and config files are stored at /var/lib/pgsql/15

Set up db for new version

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

Check compatibility:

/usr/pgsql-15/bin/pg_upgrade --old-bindir=/usr/pgsql-12/bin/ --new-bindir=/usr/pgsql-15/bin/ --old-datadir=/var/lib/pgsql/12/data/ --new-data
dir=/var/lib/pgsql/15/data/ --check