7,612
edits
Changes
Jump to navigation
Jump to search
Research Computing Configuration (view source)
Revision as of 20:06, 23 October 2020
, 20:06, 23 October 2020→Address the upgrade issues
All my extensions report back.
Update: It seems something did go wrong. Just because the extensions report back doesn't mean they work! When I try to run queries that use PostGIS, I get: SQL Error [58P01]: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory I tried updating the extension (I'm pretty sure that I'm running 2.4.3): ALTER EXTENSION postgis UPDATE TO "2.4.3"; But that didn't fix anything. I checked the versions:
select version();
PostgreSQL 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
CONTEXT: SQL statement "SELECT public.postgis_lib_version()"
PL/pgSQL function postgis_full_version() line 25 at SQL statement
So I backed off the data from the two dbases that I'd used since the upgrade:
pg_dump -Fc stockmarket > stockmarket_Fc_20201023.dump
pg_dump -Fc vcdb4 > vcdb4_Fc_20201023.dump
The second backoff failed:
pg_dump: [archiver (db)] query failed: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef,
a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, a.attidentity, pg_catalog.array_to_string(ARRAY(SELECT
pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name),
E',
') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '19998614'::pg_catalog.oid AND
a.attnum > 0::pg_catalog.int2 ORDER BY a.attnum
My changes weren't substantial, so I proceeded with an upgrade. First I checked to see if I had postgres12 installed and listening on another port or not:
locate postgres
ls /usr/bin/postgres
dpkg --get-selections | grep postgres
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /data/postgres /var/log/postgresql/postgresql-10-main.log
12 main 5433 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
pg_upgradecluster 10 main
#This failed:
pg_dump: error: query failed: ERROR: could not access file "$libdir/postgis-2.4": No such file or directory
So... I can't automatically upgrade without first fixing the issue with v10 and postgis.
add-apt-repository http://apt.postgresql.org/pub/repos/apt
But that put the following into /etc/apt/sources-list:
deb http://apt.postgresql.org/pub/repos/apt focal main
vi it to (see https://wiki.postgresql.org/wiki/Apt):
deb http://apt.postgresql.org/pub/repos/apt focal-pgdg
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
apt-get update
#Throws a warning:
N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
But the wretched thing still doesn't seem to be available:
apt-get install postgresql-10-postgis-2.4
Package postgresql-10-postgis-2.4 is not available, but is referred to by another package.
Trying a manual approach. [https://packages.ubuntu.com/bionic/i386/postgresql-10-postgis-2.4/download Get the file], put it in /bulk/temp and cd there. Then:
apt-get install ./postgresql-10-postgis-2.4_2.4.3+dfsg-4_i386.deb
#This failed too - there are unmet dependencies and they are 'not installable'.
So, option 2 is to shutdown version 10, move version 12 to the right port, and load up the dbases... Then wipe out the old datastructure and move the new one to the right location.
pg_lsclusters
#The cluster
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /data/postgres /var/log/postgresql/postgresql-10-main.log
12 master 5433 down postgres /var/lib/postgresql/12/master /var/log/postgresql/postgresql-12-master.log
====Other Fixes====
