Solution for Postgresql Upgrade from 11 to 12 fails due to “type abstime does not exist”
is Given Below:
I’m running an old postgresql
database that was originally installed as v9.6 and I’m progressively upgrading it from one version to the next until I can get it up to date with version 13.3. The upgrades from 9.6 to 10, and 10 to 11 were successful. However when I try to upgrade to version 12 I get the following error:
bash-4.4$ tail pg_upgrade_dump_16421.log
pg_restore: creating TYPE "public.gtrgm"
pg_restore: creating FUNCTION "public.Seqnextval("abstime")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 662; 1255 16425 FUNCTION Seqnextval("abstime") effective
pg_restore: error: could not execute query: ERROR: type abstime does not exist
Command was: CREATE FUNCTION "public"."Seqnextval"("abstime") RETURNS bigint
LANGUAGE "sql"
AS $$select setval('units_id_seq', (select max(id) from units)+1) from units limit 1;$$;
Here are the steps that I’ve taken from 11 to 12 to get to where I am right now:
B) Upgrade to PostgreSQL 12
Update packages and install postgres
sudo yum update sudo yum install postgresql12
Stop the postgresql services
sudo systemctl stop postgresql-11.service sudo systemctl stop postgresql-12.service
Log in as the
postgres
user againsudo su postgres
Change to the home directory
cd ~
Migrate the data
/usr/pgsql-12/bin/pg_upgrade --old-datadir=/var/lib/pgsql/11/data --new-datadir=/var/lib/pgsql/12/data --old-bindir=/usr/pgsql-11/bin --new-bindir=/usr/pgsql-12/bin --old-options '-c config_file=/var/lib/pgsql/11/data/postgresql.conf' --new-options '-c config_file=/var/lib/pgsql/12/data/postgresql.conf'
Switch to regular user
exit
Swap the ports the old and new postgres versions.
sudo nano /var/lib/pgsql/12/data/postgresql.conf _change port to 5432_ sudo nano /var/lib/pgsql/11/data/postgresql.conf _change port to 5433_
Start the postgresql service
sudo systemctl start postgresql-12.service
Log in as postgres user
sudo su postgres cd ~
Check your new postgres version
psql -c "SELECT version();"
Run the generated new cluster script
./analyze_new_cluster.sh
Return as a normal (default user) user and cleanup up the old version’s mess
exit sudo yum remove postgresql11 sudo rm -rf /etc/postgresql/11/ sudo su postgres cd ~ ./delete_old_cluster.sh
I know for sure that the abstime
datatype has been removed in postgresql
12. Does anyone here know how to resolve this issue so I can continue with my upgrades? Please let me know, thanks! Also if you have any questions, I’m all ears.
What’s possible to do with function “Seqnextval”
CREATE FUNCTION "public"."Seqnextval"("abstime")
RETURNS bigint
LANGUAGE "sql"
AS $$
select setval('units_id_seq', (select max(id) from units)+1) from units limit 1;
$$;
Note that argument of type abstime
is never used in function.
I suppose it must be safe to change it to:
CREATE FUNCTION "public"."Seqnextval"(anyelement)
then rerun upgrade process.
What must be possible to do with abstime
in general
It could be possible there are other functions that use abstime
.
Look for other functions:
WITH funcs AS (
SELECT
P.proname,
p.pronamespace::regnamespace::text AS func_schema,
obj_description(p.oid),
pg_catalog.pg_get_function_arguments(p.oid) AS args,
pg_get_function_result(p.oid) AS rettype
FROM
pg_proc P
)
SELECT
*
FROM
funcs
WHERE
(args ~~ '%abstime%'
OR rettype="abstime"
)
AND func_schema <> 'pg_catalog'
Other tables/views:
SELECT * FROM information_schema."columns"
WHERE
data_type="abstime"
AND table_schema <> 'pg_catalog'
If there are no other functions and relations – you’re lucky.
Otherwise:
- inspect each function/relation
- decide how to modify them or delegate it to someone other
- change them
- restart upgrade process