postgresqlpg-upgrade

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

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

  1. Update packages and install postgres

    sudo yum update
sudo yum install postgresql12

  2. Stop the postgresql services

    sudo systemctl stop postgresql-11.service
sudo systemctl stop postgresql-12.service

  3. Log in as the postgres user again

    sudo su postgres

  4. Change to the home directory

    cd ~

  5. 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'

  6. Switch to regular user

     exit

  7. 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_

  8. Start the postgresql service

     sudo systemctl start postgresql-12.service

  9. Log in as postgres user

    sudo su postgres
cd ~

  10. Check your new postgres version

    psql -c "SELECT version();"

  11. Run the generated new cluster script

     ./analyze_new_cluster.sh

  12. 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.

Solution

  • 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: