My last blog introduced the issues one can face when upgrading PostGIS and PostgreSQL at the same time. The purpose of this blog is to walk through the steps with an example.
For our purposes, we will confine ourselves to working with the community versions of 9.6 and 11 respectively, and use LXD in order to create a working environment prototyping the steps, and profiling the issues.
The first step is creating a template container with the requisite packages and configurations. This template is a basic distribution of Ubuntu 18.04, which has already been installed in the development environment.
|
1 |
# creating the template container<br>lxc cp template-ubuntu1804 template-BetterView<br>lxc start template-BetterView<br>lxc exec template-BetterView bash |
These packages install the necessary supporting packages, installing PostgreSQL from the community repository:
|
1 |
apt install -y wget gnupg2 |
These steps are copied from the community download page for Ubuntu distributions:
|
1 |
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list <br>wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - |
The aforementioned repository is now updated thus making it possible to install our two versions of PostgreSQL (i.e. 9.6 and 11, respectively). Installing pg_repack pulls in the requisite packages while installing this very useful package at the same time too:
|
1 |
apt update<br>apt install -y postgresql-11-repack postgresql-9.6-repack |
These next packages are useful. Midnight Commander, mc, is a terminal-based shell navigator and file manager while the other package installs utilities, such as netstat, to monitor the status of all network-based services on the localhost:
|
1 |
apt install -y mc net-tools |
This last step merely updates the man pages database and the mlocate database. It makes it easier to locate files on the host. Beware this can be a security risk if used on a production host.
|
1 |
mandb && updatedb |
This little snippet of code creates our simulated production host. Creating the instance from a template container makes it much easier to try different variations in quick order:
|
1 |
# creating the POC upgrade container<br>lxc rm --force pg1-BV<br>lxc cp template-BetterView pg1-BV<br>lxc start pg1-BV<br>lxc exec pg1-BV bash |
As per our scenario, upgrading PostGIS requires two different versions to be installed on the host. Notice that PostgreSQL version 9.6 has the older version of PostGIS, while version 11 has the newer one.
For our purposes, this presentation assumes upgrading both PostgreSQL and PostGIS is the method to be used.
ATTENTION: Executing this upgrade operation into two distinct phases is preferred. Either upgrade PostgreSQL and then upgrade PostGIS or upgrade PostGIS on the old version to match the new version on PostgreSQL and then upgrade the PostgreSQL data cluster.
The underlying assumption is that application code can break between PostGIS version upgrades therefore pursuing an incremental process can mitigate potential issues.
https://PostGIS.net/docs/PostGIS_Extensions_Upgrade.html
https://PostGIS.net/workshops/PostGIS-intro/upgrades.html
|
1 |
apt install -y postgresql-9.6-postgis-2.4 postgresql-11-postgis-3 |
Available versions of PostGIS, as per the community repository at the time of this blog’s publication:
ATTENTION: Azure supports only PostgreSQL 9.6 with PostGIS 2.3.2.
This query lists all user-defined functions that have been installed in your database. Use it to summarize not only what you’ve created but the entire suite of PostGIS function calls:
|
1 |
--<br>-- get list of all PostGIS functions<br>--<br>select nspname, proname<br>from pg_proc<br>join pg_namespace on pronamespace=pg_namespace.oid<br>where nspname not in ('pg_catalog','information_schema')<br>order by 1,2; |
In order to validate your functions, you need to know which ones are being used, therefore tracking the functions prior to the upgrade process will identify them. Please note there are two settings i.e. pl, all. Out of an abundance of caution, it is suggested initially using all for an extended period of time:
|
1 |
--<br>-- postgresql.conf<br>-- track_functions = none # none, pl, all<br>--<br>alter system set track_functions=all;<br>select pg_reload_conf(); |
This view collects all the statistics related to function calls:
|
1 |
<span style="font-size: 11px;">--<br>-- track function activity<br>--<br> View "pg_catalog.pg_stat_user_functions"<br> Column | Type | Collation | Nullable | Default<br>------------+------------------+-----------+----------+---------<br> funcid | oid | | |<br> schemaname | name | | |<br> funcname | name | | |<br> calls | bigint | | |<br> total_time | double precision | | |<br> self_time | double precision | | |</span> |
This is a simple example demonstrating tracking function call usage. Note there are two function calls and one of them is invoked in the other:
|
1 |
CREATE OR REPLACE FUNCTION f1 (<br> in a integer,<br> out b integer<br>) AS<br>$$<br>BEGIN<br> raise notice 'function f1 is called';<br> perform pg_sleep(1);<br> b = a+1;<br>END<br>$$<br>LANGUAGE plpgsql; |
|
1 |
CREATE OR REPLACE FUNCTION f2 (<br> in c integer,<br> out d integer<br>) as<br>$$<br>BEGIN<br> raise notice 'function f2 is called';<br> perform f1(c);<br> raise notice 'returning from f2';<br> d := 0;<br>END<br>$$<br>language plpgsql; |
This SQL statement resets all statistics being tracked in the PostgreSQL database. Please note there are other functions that can be used to reset specific statistics while preserving others:
|
1 |
select * from pg_stat_reset(); |
And here’s our functions’ invocations:
|
1 |
<span style="font-size: 10px;">db01=# select * from f1(4);<br>NOTICE: function f1 is called<br> b<br>---<br> 5<br>db01=# select * from f2(4);<br>NOTICE: function f2 is called<br>NOTICE: function f1 is called<br>NOTICE: returning from f2<br> d<br>---<br> 0<br>db01=# select * from pg_stat_user_functions;<br> funcid | schemaname | funcname | calls | total_time | self_time<br>--------+------------+----------+-------+------------+-----------<br> 17434 | public | f1 | 2 | 2002.274 | 2002.274<br> 17437 | public | f2 | 1 | 1001.126 | 0.599<br></span> |
There are two discrete upgrades:
An Ubuntu-based upgrade requires removing the target data cluster because installing PostgreSQL packages onto a Debian-based distro always includes creating a data cluster:
|
1 |
<span style="font-size: 11px;">pg_lsclusters<br>Ver Cluster Port Status Owner Data directory<br>9.6 main 5432 online postgres /var/lib/postgresql/9.6/main<br>11 main 5434 online postgres /var/lib/postgresql/11/main <br><br>pg_dropcluster --stop 11 main</span> |
For our purposes we are simply adding the extension, no user-defined functions have been included:
|
1 |
su - postgres<br>createdb -p 5432 db01<br>psql -p 5432 db01 -c "create extension PostGIS"<br>exit |
Shutting down the source data cluster is the last step before the upgrade process can begin:
|
1 |
systemctl stop postgresql@9.6-main |
Debian based distros provide a convenient CLI, making upgrades easy:
|
1 |
<span style="font-size: 10px;"># /usr/bin/pg_upgradecluster [OPTIONS] <old version> <cluster name> [<new data directory>]<br>pg_upgradecluster -v 11 9.6 main</span> |
It’s important to check the upgrade logs before starting PostgreSQL version 11. This is a one-way process and once it’s active the old PostgreSQL 9.6 cluster is no longer available and must be destroyed:
|
1 |
systemctl start postgresql@11-main<br>pg_dropcluster --stop 9.6 main |
Here’s confirmation of the PostgreSQL and PostGIS upgrade respectively:
|
1 |
<span style="font-size: 10px;">su - postgres<br>psql -p 5432 db01<br>show server_version;<br> server_version<br>------------------------------------<br> 11.14 (Ubuntu 11.14-1.pgdg18.04+1)<br>select * from PostGIS_version();<br> PostGIS_version<br>---------------------------------------<br> 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1</span> |
This is critical; the process validates that the application logic works or that it must be updated.
METHOD: inspect each function call used between all versions:
TIP: 3.1 documentation encapsulates all previous versions i.e. section 9.12
REFERENCES:
Be advised, cloud environments are not ideal upgrade candidates. The aforementioned process is quite detailed and will facilitate a successful upgrade process.
References:
https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-extensions
As more companies look at migrating away from Oracle or implementing new databases alongside their applications, PostgreSQL is often the best option for those who want to run on open source databases.
Read Our New White Paper:
Resources
RELATED POSTS
