Postgresql Upgrade from 11 to 12 fails due to “type abstime does not exist”

Question 1

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 again
```
sudo 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.

Question 2

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

What’s possible to do with function “Seqnextval”

What must be possible to do with abstime in general

What must be possible to do with `abstime` in general