LdDl/pg_upgrade_postgis.md

## pg_upgrade_postgis.md

      
    Raw
  

              pg_upgrade_postgis.md
            
          
    My case

Current environment

Ubuntu 18.04
PostgreSQL 13.1
PostGIS 3.x.x + obsolete SFCGAL(somehow it was installed with PostGIS)

Target

PostgreSQL 14.x
Postgis 3.x.x

Backup

Always do backups. It take some time, but you won't regret it if something really bad happens
time pg_dump -h localhost -Fc -x -O -U my_pg_user -W -d my_db  > my_db.dump
Install new Postgres and PostGIS packages

Probably you need to update repositories:
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 | apt-key add -
sudo apt-get update
Install packages:
sudo apt-get install postgresql-14 postgresql-server-dev-14
sudo apt-get install postgresql-14-postgis-3 postgresql-14-postgis-3-scripts
Check newly created cluster
pg_ctlcluster 14 main status
# or simpy:
# pg_lsclusters 

Checks before upgrading

Stop clusters:
sudo pg_ctlcluster 13 main stop
sudo pg_ctlcluster 14 main stop

Check if everything is fine for upgrading:
sudo su - postgres
cd ~
# Notice flag `--check`
/usr/lib/postgresql/14/bin/pg_upgrade \
-- check \
--old-datadir=/var/lib/postgresql/13/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
'incompatible_polymorphics' error.

In my case I had aggregate function:
CREATE OR REPLACE FUNCTION _final_median(anyarray) RETURNS float8 AS $$
WITH q AS
(
	SELECT val
	FROM unnest($1) val
	WHERE VAL IS NOT NULL
	ORDER BY 1
),
cnt AS
(
	SELECT COUNT(*) as c FROM q
)
SELECT AVG(val)::float8
FROM
(
	SELECT val FROM q
	LIMIT  2 - MOD((SELECT c FROM cnt), 2)
	OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
) q2;
$$ LANGUAGE sql IMMUTABLE;


CREATE OR REPLACE AGGREGATE median(anyelement) (
	SFUNC=array_append,
	STYPE=anyarray,
	FINALFUNC=_final_median,
	INITCOND='{}'
);
which had led me to error:
Your installation contains user-defined objects that refer to internal
polymorphic functions with arguments of type "anyarray" or "anyelement".
These user-defined objects must be dropped before upgrading and restored
afterwards, changing them to refer to the new corresponding functions with
arguments of type "anycompatiblearray" and "anycompatible".
A list of the problematic objects is in the file:
    incompatible_polymorphics.txt

Thus I've needed to temporary drop them:
DROP AGGREGATE median(anyelement);
DROP FUNCTION _final_median(anyarray);
'loadable_libraries' error.

Other error, I've faced is:
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt

Since I've installed both postgresql-14-postgis-3 and postgresql-14-postgis-3-scripts I've determined that the problem was in obsolete SFCGAL, so I've just removed it:
drop extension postgis_sfcgal;
Upgrading

Now, remove --check flag from pg_upgrade binary call:
sudo su - postgres
cd ~
/usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/13/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/13/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/13/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
You could use --link flag. Instead of copying all the files, pg_upgrade will create hard links for those data files. In my case I've wanted "real" copy of all old cluster, so I've not used it.
If something goes wrong pg_upgrade won't break old cluster, it just breaks new cluster. You can use initdb to refresh new cluster installation.
sudo su - postgres
pg_dropcluster --stop 14 main
rm -rf /var/lib/postgresql/14/main
/usr/lib/postgresql/14/bin/initdb /var/lib/postgresql/14/main
pg_createcluster -u postgres 14 main
systemctl daemon-reload
sudo pg_ctlcluster 14 main start
Before re-initalizing new cluster it is better to check why it happened and fix possible errors.
Final

When it is all done you will see:
Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

In my case update_extensions.sql would not work completely because of postgis_tiger_geocoder and postgis_topology. I've decided to just ignore them since I've needed those extensions.
Do no forget to run vacuumdb!
After all do all possible tests for you cases and if everything is fine you can drop old cluster via delete_old_cluster.sh
Ah, forgot to mention some...Do you remember 'incompatible_polymorphics' error? In my case I've updated aggregate functions to work on Postgres 14 like this:
CREATE OR REPLACE FUNCTION _final_median(anycompatiblearray) RETURNS float8 AS $$ 
WITH q AS
(
	SELECT val
	FROM unnest($1) val
	WHERE VAL IS NOT NULL
	ORDER BY 1
),
cnt AS
(
	SELECT COUNT(*) as c FROM q
)
SELECT AVG(val)::float8
FROM 
(
	SELECT val FROM q
	LIMIT  2 - MOD((SELECT c FROM cnt), 2)
	OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)  
) q2;
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE AGGREGATE median(anycompatible) (
	SFUNC=array_append,
	STYPE=anycompatiblearray,
	FINALFUNC=_final_median,
	INITCOND='{}'
);

basically I've changed anyarray to anycompatiblearray and anyelement to anycompatible

Now that is it.