Upgrading PostGIS: A Proof Of Concept

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Percona Events Hub

See all of Percona’s upcoming events and view materials like webinars and forums from past events

Percona Live 2023

Percona is an open source database software, support, and services company that helps make databases and applications run better.

Learn More

See Percona’s recent news coverage, press releases and industry recognition for our open source software and support.

News coverage Press Releases

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.

Creating the Demo Environment Using LXD

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.

# creating the template container lxc cp template-ubuntu1804 template-BetterView lxc start template-BetterView lxc exec template-BetterView bash

These packages install the necessary supporting packages, installing PostgreSQL from the community repository:

apt install -y wget gnupg2

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 -

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:

apt update 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:

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 .

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:

# creating the POC upgrade container lxc rm --force pg1-BV lxc cp template-BetterView pg1-BV lxc start pg1-BV lxc exec pg1-BV bash

# creating the POC upgrade container

lxc rm -- force pg1 - BV

lxc cp template - BetterView pg1 - BV

lxc start pg1 - BV

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

apt install -y postgresql-9.6-postgis-2.4 postgresql-11-postgis-3

About PostGIS

Available versions of PostGIS, as per the community repository at the time of this blog’s publication:

postgresql-9.6-postgis-2.4
postgresql-9.6-postgis-2.5
postgresql-9.6-PostGIS-3
postgresql-11-postgis-2.5
postgresql-11-postgis-3
PostGIS supported versions matrix

ATTENTION : Azure supports only PostgreSQL 9.6 with PostGIS 2.3.2.

Before You Upgrade

About

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:

-- get list of all PostGIS functions select nspname, proname from pg_proc join pg_namespace on pronamespace=pg_namespace.oid where nspname not in ('pg_catalog','information_schema') order by 1,2;

-- get list of all PostGIS functions

select nspname, proname

from pg_proc

join pg_namespace on pronamespace = pg_namespace.oid

where nspname not in ( 'pg_catalog' , 'information_schema' )

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:

-- postgresql.conf -- track_functions = none # none, pl, all alter system set track_functions=all; select pg_reload_conf();

--

-- track function activity

View "pg_catalog.pg_stat_user_functions"

Column | Type | Collation | Nullable | Default

------------+------------------+-----------+----------+---------

funcid | oid | | |

schemaname | name | | |

funcname | name | | |

calls | bigint | | |

total_time | double precision | | |

self_time | double precision | | |

Example

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:

CREATE OR REPLACE FUNCTION f1 ( in a integer, out b integer BEGIN raise notice 'function f1 is called'; perform pg_sleep(1); b = a+1; 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:

select * from pg_stat_reset();

db01=# select * from pg_stat_user_functions; funcid | schemaname | funcname | calls | total_time | self_time --------+------------+----------+-------+------------+----------- 17434 | public | f1 | 2 | 2002.274 | 2002.274 17437 | public | f2 | 1 | 1001.126 | 0.599

db01=# select * from f1(4);

NOTICE: function f1 is called

---

db01=# select * from f2(4);

NOTICE: function f2 is called

NOTICE: function f1 is called

NOTICE: returning from f2

---

db01=# select * from pg_stat_user_functions;

--------+------------+----------+-------+------------+-----------

17434 | public | f1 | 2 | 2002.274 | 2002.274

17437 | public | f2 | 1 | 1001.126 | 0.599

An Upgrade Example Using pg_upgrade

SYNOPSIS

There are two discrete upgrades:

pg_upgrade: pg 9.6 -> pg 11
PostGIS upgrade: postgis-2.4 -> postgis2.5 -> postgis-3

HOUSE CLEANING

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:

pg_lsclusters Ver Cluster Port Status Owner Data directory 9.6 main 5432 online postgres /var/lib/postgresql/9.6/main 11 main 5434 online postgres /var/lib/postgresql/11/main pg_dropcluster --stop 11 main

pg_lsclusters

Ver Cluster Port Status Owner Data directory

9.6 main 5432 online postgres /var / lib / postgresql / 9.6 / main

11 main 5434 online postgres /var / lib / postgresql / 11 / main

pg_dropcluster --stop 11 main

For our purposes we are simply adding the extension, no user-defined functions have been included:

su - postgres createdb -p 5432 db01 psql -p 5432 db01 -c "create extension PostGIS"

Shutting down the source data cluster is the last step before the upgrade process can begin:

systemctl stop postgresql@9.6-main

# /usr/bin/pg_upgradecluster [OPTIONS] <old version> <cluster name> [<new data directory>] pg_upgradecluster -v 11 9.6 main

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:

systemctl start postgresql@11-main pg_dropcluster --stop 9.6 main

------------------------------------ 11.14 (Ubuntu 11.14-1.pgdg18.04+1) select * from PostGIS_version(); PostGIS_version --------------------------------------- 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

su - postgres

psql -p 5432 db01

show server_version;

server_version

------------------------------------

11.14 (Ubuntu 11.14 - 1.pgdg18.04 + 1)

select * from PostGIS_version();

PostGIS_version

---------------------------------------

3.1 USE_GEOS = 1 USE_PROJ = 1 USE_STATS = 1

PostGIS Function API, Inspection, and Review

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:

from 2.4 -> 2.5
from 2.5 -> 3.0
from 3.0 -> 3.1

TIP : 3.1 documentation encapsulates all previous versions i.e. section 9.12

REFERENCES:

Regression Testing

In the current setup, pg 9.6
Identify all functions used in PostGIS
Execute a simple function call with every type of parameter typically used in your environment
Collect, record all variables returned
In the target setup, pg 11 or pg 13
Execute a simple function call with every type of parameter typically used in your environment
Collect, record all variables returned
Analysis
Compare values: similar values mean you don’t have a problem

Working With Cloud Provider Technology

Be advised, cloud environments are not ideal upgrade candidates. The aforementioned process is quite detailed and will facilitate a successful upgrade process.

AZURE
pg 9.6: PostGIS 2.3.2
pg 11: PostGIS 2.5.1
AMAZON
pg 11, 13: PostGIS 3.1.4
pg 9.6.*: PostGIS 2.3.[0247], 2.5.[25]

References:

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions#postgres-96-extensions

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions#postgres-11-extensions

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.PostGIS.html

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:

Why Customers Choose Percona for PostgreSQL

Percona Events Hub

Creating the Demo Environment Using LXD

About PostGIS

Before You Upgrade

About

Example

An Upgrade Example Using pg_upgrade

SYNOPSIS

HOUSE CLEANING

PostGIS Function API, Inspection, and Review

Regression Testing

Working With Cloud Provider Technology

Related

Featured

Products

Services

Learn

Discover

About

Percona Events Hub

Creating the Demo Environment Using LXD

About PostGIS

Before You Upgrade

About

Example

An Upgrade Example Using pg_upgrade

SYNOPSIS

HOUSE CLEANING

PostGIS Function API, Inspection, and Review

Regression Testing

Working With Cloud Provider Technology

Related

Share This Post!

Follow us for updates

Featured

Products

Services

Learn

Discover

About