Upgrading Postgres and PostGIS
Published
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.
Comments
No responses yet