Upgrading PostgreSQL

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.

• The PostgreSQL docs

• This guide

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

MacOS

For MacOS, it depends on whether you installed PostgreSQL via homebrew, or via the installer from EDB. I used the later, so here is how to upgrade in that instance. For the purposes of this doc, we'll assume you're upgrading from version 14 to version 16 (because that's what I just did). Replace with the appropriate version numbers as necessary.

Download and install the new version from EDB

The installer will place all the Postgres files in /Library/PostgreSQL/16 - where 16 is the version number. Make sure the postgres user owns everything in this folder. The previous version will be in /Library/PostgreSQL/14.

sudo chown -R postgres:daemon /Library/PostgreSQL

Make sure all Postgres servers are stopped.

sudo su postgres
cd /Library/PostgreSQL/16/bin
./pg_ctl stop -D "/Library/PostgreSQL/14/data/"
./pg_ctl stop -D "/Library/PostgreSQL/16/data/"

Make sure local access is by "ident"

For both versions, update pg_hba.conf to add "ident" access for local accounts on sockets. This will allow the upgrade tool to access the servers without a password. You will need to do this as the postgres user - sudo su postgres.

Run the upgrade tool.

Go to the bin directory of the new version. Switch to the postgres user and run the upgrade tool.

cd /Library/PostgreSQL/16/bin
sudo su postgres
./pg_upgrade --old-datadir="/Library/PostgreSQL/14/data/" \ 
--new-datadir="/Library/PostgreSQL/16/data/" \ 
--old-bindir="/Library/PostgreSQL/14/bin/" \ 
--new-bindir="/Library/PostgreSQL/16/bin/" \ 

Update ports

If the old server was running on the default port (5432) when you ran the installer for the new version, then the new version's port was set to 5433 (or the next available port). Swap it so the new version is on the default port, so any applications will access it instead. Edit postgresql in the data directory to update the port.

Update PATH

Update your .bashrc or .bash_profile to include the bin directory of the new version in your PATH. Remove the old version.

export PATH="/Library/PostgreSQL/16/bin:${PATH}"

Start the new version

pg_ctl start -D "/Library/PostgreSQL/16/data/"

That should do it!