Upgrading Postgres and PostGIS

I always have trouble upgrading Postgres versions when PostGIS is involved.. I end up dumping the entire database, upgrading the version, and loading the data back in afterwards. After repeating this process a few times, I decided to put together a blog post that included some of my notes.

1. Backing up Database Configs

First backup any important system configs that you may need to refer to later. For Postgres, be sure to save:

• postgresql.conf – defines connection information (ports, interfaces), memory usage, replication, etc.
• pg_hba.conf – set security/access information for local and network users

2. Exporting Database Structure and Content

There are several ways to export Postgres data including the pg_dumpall and pg_dump commands. Here and example command that exports the mydatabase into a single compressed file:

pg_dump -U postgres --format=c --file=mydatabase-backup.sqlc mydatabase

3. Upgrading Postgres Versions

Next it’s time to install the updated version of Postgres. For easy package installs, you can add the Postgres repository to APT (applies to Ubuntu/Debian). For more information, check out this guide.

sudo apt-get install curl ca-certificates gnupg
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update

Once the repository has been added to APT, use a command like the following to install Postgres and PostGIS (update to the desired version number of course):

apt-get install postgresql-10 postgresql-10-postgis-3 postgresql-10-postgis-3-scripts

Note: Different versions of Postgres/PostGIS combinations are available for different versions of Ubuntu/Linux. For example, postgresql-10-postgis-2.5 is available for Ubuntu 18.04, but may not be available for Ubuntu 20.04.

Once Postgres has been installed, use the pg_lsclusters command to see the running clusters:

Verify there is only a single running cluster after the upgrade. If multiple versioned clusters are returned, be sure to remove the extra versions in the nest section.

4. Cleaning up Old Versions

Use the dpkg -l | grep postgres command to list all installed versions of Postgres and PostGIS:

Fortunately this server is looking clean, but if packages for a previous versions of Postges were present, for example postgresql-9-postgis-2.5, remove them with the following command:

apt purge postgresql-9-postgis-2.5

5. Ensuring PostGIS is Working

After removing any residual packages, verify PostGIS is installed and usable by creating a sample database with the postgis extension:

psql -U postgres
CREATE DATABASE sampledb;
\connect sampledb
CREATE EXTENSION postgis;

If an error is returned, use the dpkg command referenced in the last section to ensure the Postgres and PostGIS versions match, including the scripts package, (e.g. postgresql-10 postgresql-10-postgis-3 postgresql-postgis-3-scripts). Try reinstalling the Postgis packages if still having issues.

5. Importing Data

Once PostGIS is confirmed to be working, it’s time to import the data into the upgraded version of Postgres. The following command will import a compressed archive (similar to the one created earlier):

pg_restore -U postgres --dbname=mydatabase ./mydatabase-backup.sqlc

6. Verify Applications and Data

Finally it’s time to confirm that existing applications are able to connect to the new version of Postgres. Postgres and web server logs inside of /var/log are a good place to check if encountering issues. Don’t forget to refer to the backed up Postgres configuration files mentioned in section 1 that may have been overwritten during the upgrade.