Extensions

SELECT postgis_extensions_upgrade();

SELECT postgis_full_version();

SELECT DISTINCT p.probin
FROM pg_catalog.pg_proc AS p
WHERE p.probin = '$libdir/libpgrouting-3.5';

ALTER EXTENSION pgrouting UPDATE TO '4.0.1';

SQL functions in indexes, partition keys, and generated columns

During the conversion phase, an IPMVU re-creates the definition of every index, partition key, and stored generated column with an empty search path. This step fails if one of these objects uses a LANGUAGE sql function with a string body that names an object outside pg_catalog without its schema, directly or through a domain check constraint. Extended statistics have the same effect on a table that has a foreign key. In an index, a call that names the ibm_extension schema, where extensions are installed, also fails. The failure happens while the database is unavailable, and every retry fails the same way.

Run self-check query 12 in each database. For each listed function, add a search_path setting that names every schema that its body uses. Alternatively, replace its string body with an SQL-standard body. Run these statements as the function's owner. For example:

ALTER FUNCTION f_unaccent(text) SET search_path = ibm_extension, pg_catalog;

CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  RETURN ibm_extension.unaccent('ibm_extension.unaccent', $1);

Keep each function's results and other attributes unchanged, because indexes and generated columns store its results, and partitioned tables route rows by them. With a search_path setting, PostgreSQL no longer expands the function into the queries that call it, so those queries can be slower. An SQL-standard body does not protect the string-body functions that it calls, so fix those functions too. Run the query again until it returns no rows. If it lists a function that you do not own, contact support before you upgrade.

If you upgrade from PostgreSQL 16 or earlier to PostgreSQL 17 or later, also run self-check query 17. PostgreSQL 17 and later run maintenance commands with a restricted search path. A function in any language that an index, a materialized view, or extended statistics use then needs its own search_path setting. Otherwise, after the upgrade, ANALYZE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW fail for the affected objects. Set the search path of each listed function that names objects without their schema, as in the previous example. For more information, see the PostgreSQL 17 migration notes.

Objects that use removed system names

Each major version removes or renames some system catalog columns, functions, and configuration parameters. For example, PostgreSQL 17 removed the checkpoint columns from pg_stat_bgwriter, and PostgreSQL 16 renamed the force_parallel_mode parameter to debug_parallel_query. The prechecks do not detect objects that use these names:

• If a view, a materialized view, or an aggregate uses a removed name, an IPMVU fails during the conversion phase. A function with an SQL-standard body and a function, role, or database setting of a removed parameter have the same effect.
• Other functions and procedures, pg_cron jobs, and application queries that use a removed name fail when they first run on the new version. This can occur before writes resume.

Run self-check query 14 in each database. Change or drop each listed object whose removed_in version is at or below your target version. Check role and database settings with self-check query 7. Test your application and monitoring queries on a staging deployment of the target version.

Logical replication slots

IPMVU fails its prechecks while any logical replication slot exists, including inactive slots and slots that wal2json uses. You cannot recover a deleted slot, and a new slot does not include the changes that the old slot retained. Before you upgrade:

1. Record each slot's name, plug-in, and database with self-check query 2.

2. Pause source writes until you restore consumers after the upgrade, or plan to resynchronize downstream data.

3. Let consumers process pending changes, and then stop them. Keep them stopped, with automatic reconnection turned off, until the task shows Completed.

4. Delete each slot with the ibmcloud cdb postgresql replication-slot-delete command or the Delete a logical replication slot API. The admin user cannot drop replication slots with SQL.

   ibmcloud cdb postgresql replication-slot-delete <NAME|CRN> <SLOT_NAME>
   

5. After the upgrade, recreate the slots as described in Configuring wal2json.

wal2json is a logical decoding output plug-in, not an extension that you create with CREATE EXTENSION.

Logical subscriptions

A logical subscription receives changes from a publisher. Its replication slot is on the publisher, so the slot query does not list it.

If your deployment runs PostgreSQL 16 or earlier, IPMVU fails its prechecks while any logical subscription exists, including disabled subscriptions. Upgrades from these versions do not preserve a subscription's synchronization state. Before you upgrade, agree with the publisher's owner how to remove and recreate each subscription, and how to resynchronize the data. Then, delete the subscriptions with the subscriber functions. The delete_subscription function also drops the subscription's slot on the publisher, and it fails if the publisher is unreachable or the slot no longer exists. In that case, run disable_subscription and then subscription_slot_none before delete_subscription. Then, ask the publisher's owner to drop the slot, because it keeps retaining transaction logs on the publisher.

If your deployment runs PostgreSQL 17 or later, subscriptions can remain, but they must meet the target version's logical replication upgrade requirements. The upgrade supports at most 10 subscriptions. With more, it fails its compatibility checks before read-only mode begins. Disable the subscriptions with disable_subscription before you upgrade, and enable them with enable_subscription after the upgrade completes.

UNLOGGED tables and sequences

PostgreSQL does not write changes to UNLOGGED tables and sequences to the transaction log. It does not replicate them to other members, and it resets them after a failover or a restore. None of the upgrade paths keeps their contents:

• An IPMVU resets them when it restarts PostgreSQL on the new version, and again when it switches the primary role in the completion phase. A failed upgrade can also reset them.
• A promoted read-only replica and a restored backup start with empty UNLOGGED tables and reset UNLOGGED sequences.

After the upgrade, UNLOGGED tables are empty and UNLOGGED sequences are reset. Until the task shows Completed, any changes that you make to these objects can also be lost. Before you upgrade, use self-check query 11 to identify these objects in each database and plan how to handle each one.

• To keep the contents of a table, convert it with ALTER TABLE <table_name> SET LOGGED;. SET LOGGED also converts the table's indexes and the sequences that it owns. Convert a referenced table before converting any tables that reference it through foreign keys. Alternatively, export the contents and reload them after the task shows Completed.
• If a sequence generates values for a logged table, convert it with ALTER SEQUENCE <sequence_name> SET LOGGED;. Otherwise, stop the applications that use it until the task shows Completed. Then, set it past the highest value in use with the setval function.

SET LOGGED rewrites the table and blocks reads and writes until the operation finishes. It also generates approximately as much transaction log data as the table and its indexes occupy. Convert large tables well before you submit the upgrade, and use self-check query 5 to verify that the transaction logs were archived. After the upgrade, you can convert a table back with ALTER TABLE <table_name> SET UNLOGGED;.

For an IPMVU, an UNLOGGED table, even an empty one, also makes the service rebuild every HA member, which extends the time that writes are blocked. For more information, see Availability during an upgrade. To avoid this rebuild, convert every UNLOGGED table, or drop the ones that you no longer need. TRUNCATE does not avoid the rebuild. UNLOGGED sequences do not cause a rebuild. On PostgreSQL 16 and 17, SET LOGGED is itself a bulk operation that can still cause a rebuild, so plan for one.

Prechecks and preparation

The service runs the following checks before read-only mode begins. If a check fails, the task shows Failed, and the deployment stays on its current version without blocking writes. If any database has default_transaction_read_only set to on, a failed check also resets this setting on every database and ends all client sessions. The task does not report which check failed, so verify each item before you submit the upgrade.

Conditions that the prechecks do not detect

The prechecks do not detect the following conditions. Some of them make the upgrade fail after writes are blocked. If the cause is in the schema, such as a function or a database name, every retry fails the same way until you resolve it. Other conditions make the service copy the whole database to the HA members, which keeps writes blocked much longer, or make the upgrade fail after writes resume. A failure in the conversion or restart phase can also leave the HA members stopped until support restarts them. Resolve or plan for each condition before you submit the upgrade.

Do not change schemas, extensions, databases, logical replication slots, or subscriptions between submitting the upgrade and task completion.

Checking readiness yourself

Run the following queries as the admin user with psql before you submit the upgrade. They work on PostgreSQL 14 through 18. Queries 1 through 7 and query 15 cover the whole deployment, so run them once in any database. Run the other queries in each database.

1. Replication connections. Expect one row for each HA member, which is the number of data members minus one, each with the state streaming. Any other row is a read-only replica or another replication client. A row named pg_basebackup means that the service is copying the database to an HA member or to a read-only replica. Wait until the copy finishes, and then run the query again.

   SELECT application_name, client_addr, state
   FROM pg_catalog.pg_stat_replication
   ORDER BY application_name;
   

2. Logical replication slots. Expect no rows.

   SELECT slot_name, plugin, database, active
   FROM pg_catalog.pg_replication_slots
   WHERE slot_type = 'logical';
   

3. Logical subscriptions. On PostgreSQL 16 and earlier, expect no rows. On PostgreSQL 17, expect f in the subenabled column of every row.

   SELECT d.datname AS database_name, s.subname, s.subenabled
   FROM pg_catalog.pg_subscription AS s
   JOIN pg_catalog.pg_database AS d ON d.oid = s.subdbid;
   

4. Prepared transactions. Expect no rows.

   SELECT gid, owner, database, prepared
   FROM pg_catalog.pg_prepared_xacts;
   

5. Transaction-log archiving. Expect last_failed_time to be empty or earlier than last_archived_time, and waiting_files to be 0 or close to 0. A backlog that passes the precheck can still be too large to archive after read-only mode begins.

   SELECT failed_count, last_failed_time, last_archived_time
   FROM pg_catalog.pg_stat_archiver;
   

   SELECT count(*) AS waiting_files,
          pg_catalog.pg_size_pretty(count(*) * pg_catalog.pg_size_bytes(pg_catalog.current_setting('wal_segment_size'))) AS waiting_size
   FROM pg_catalog.pg_ls_archive_statusdir()
   WHERE name LIKE '%.ready';
   

6. Database connection settings. Expect no rows. Drop a listed database whose datconnlimit is -2, because it is invalid. For any other listed database, allow connections or drop it. If the query lists template0, contact support.

   SELECT datname, datallowconn, datconnlimit
   FROM pg_catalog.pg_database
   WHERE (datname <> 'template0' AND (NOT datallowconn OR datconnlimit = -2))
      OR (datname = 'template0' AND datallowconn);
   

7. Role and database settings. Make sure that every parameter in setconfig exists in the target version. Reset a parameter that the target version removed or renamed, and set its replacement after the upgrade. This also applies to extension-defined parameters that extensions, which contain a period in their names. Make sure that no role sets default_transaction_read_only to a false value, such as off, false, no, or 0. Record each database that sets default_transaction_read_only, because the upgrade resets this setting. Self-check query 14 checks settings on functions.

   SELECT r.rolname AS role_name, d.datname AS database_name, s.setconfig
   FROM pg_catalog.pg_db_role_setting AS s
   LEFT JOIN pg_catalog.pg_roles AS r ON r.oid = s.setrole
   LEFT JOIN pg_catalog.pg_database AS d ON d.oid = s.setdatabase;
   

8. Column data types that PostgreSQL cannot upgrade. Expect no rows. The query also lists columns of system composite types, and of domains, arrays, composite types, and ranges that are built on these types. The base_type column shows the type that blocks the upgrade. The aclitem type blocks only upgrades from PostgreSQL 14 or 15 to 16 or later.

   WITH RECURSIVE problem_types (type_oid, base_type) AS (
     SELECT t.oid, t.typname::text
     FROM pg_catalog.pg_type AS t
     WHERE t.typnamespace = 'pg_catalog'::pg_catalog.regnamespace
       AND t.typname IN ('regcollation', 'regconfig', 'regdictionary', 'regnamespace',
                         'regoper', 'regoperator', 'regproc', 'regprocedure', 'aclitem')
     UNION ALL
     SELECT t.oid, 'system composite type ' || t.oid::pg_catalog.regtype::text
     FROM pg_catalog.pg_type AS t
     LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = t.typnamespace
     WHERE t.typtype = 'c'
       AND (t.oid < 16384 OR n.nspname = 'information_schema')
     UNION ALL
     SELECT derived.type_oid, derived.base_type
     FROM (
       WITH found AS (SELECT type_oid, base_type FROM problem_types)
       SELECT t.oid AS type_oid, f.base_type
       FROM pg_catalog.pg_type AS t
       JOIN found AS f ON t.typtype = 'd' AND t.typbasetype = f.type_oid
       UNION ALL
       SELECT t.oid, f.base_type
       FROM pg_catalog.pg_type AS t
       JOIN found AS f ON t.typtype = 'b' AND t.typelem = f.type_oid
       UNION ALL
       SELECT t.oid, f.base_type
       FROM pg_catalog.pg_type AS t
       JOIN pg_catalog.pg_class AS c ON c.reltype = t.oid
       JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid AND NOT a.attisdropped
       JOIN found AS f ON a.atttypid = f.type_oid
       WHERE t.typtype = 'c'
       UNION ALL
       SELECT t.oid, f.base_type
       FROM pg_catalog.pg_type AS t
       JOIN pg_catalog.pg_range AS r ON r.rngtypid = t.oid
       JOIN found AS f ON r.rngsubtype = f.type_oid
       WHERE t.typtype = 'r'
     ) AS derived
   )
   SELECT DISTINCT n.nspname AS schema_name, c.relname AS relation_name, a.attname AS column_name,
          a.atttypid::pg_catalog.regtype AS data_type, p.base_type
   FROM pg_catalog.pg_attribute AS a
   JOIN pg_catalog.pg_class AS c ON c.oid = a.attrelid
   JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
   JOIN problem_types AS p ON p.type_oid = a.atttypid
   WHERE a.attnum > 0
     AND NOT a.attisdropped
     AND c.relkind IN ('r', 'm', 'i')
     AND n.nspname NOT IN ('pg_catalog', 'information_schema')
     AND n.nspname !~ '^pg_toast_temp_|^pg_temp_'
   ORDER BY 1, 2, 3;
   

9. Inherited columns without a NOT NULL constraint that their parent table has. Expect no rows. To fix a listed column, the table owner runs ALTER TABLE <table_name> ALTER COLUMN <column_name> SET NOT NULL;.

   SELECT n.nspname AS schema_name, c.relname AS table_name, child.attname AS column_name
   FROM pg_catalog.pg_inherits AS i
   JOIN pg_catalog.pg_attribute AS child ON child.attrelid = i.inhrelid
   JOIN pg_catalog.pg_attribute AS parent
     ON parent.attrelid = i.inhparent AND parent.attname = child.attname
   JOIN pg_catalog.pg_class AS c ON c.oid = i.inhrelid
   JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
   WHERE parent.attnum > 0
     AND parent.attnotnull
     AND NOT child.attnotnull
     AND NOT child.attisdropped;
   

10. Installed extensions. Complete the extension preparation. Then, confirm that every remaining extension is available in the target version, for example on a staging deployment of that version.

    SELECT extname, extversion
    FROM pg_catalog.pg_extension
    ORDER BY extname;
    

11. UNLOGGED tables and sequences. Their contents do not survive the upgrade, and a listed table makes the service rebuild every HA member. Before you upgrade, see UNLOGGED tables and sequences.

    SELECT n.nspname AS schema_name, c.relname AS relation_name, c.relkind
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
    WHERE c.relpersistence = 'u'
      AND c.relkind IN ('r', 'S');
    

12. SQL functions that can make the conversion fail. Expect no rows. The query lists LANGUAGE sql functions with a string body that an index, a partial-index predicate, a partition key, a stored generated column, or extended statistics use. It follows calls through operators, domain check constraints, and functions with an SQL-standard body. For each listed function, see SQL functions in indexes, partition keys, and generated columns.

    WITH RECURSIVE uses (refclassid, refobjid, used_by) AS (
      SELECT d.refclassid, d.refobjid,
             CASE WHEN c.relkind = 'p' THEN 'partition key of ' ELSE 'index ' END
               || c.oid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_class AS c
        ON d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass
       AND c.oid = d.objid
      WHERE c.relkind IN ('i', 'I') OR (c.relkind = 'p' AND d.objsubid = 0)
      UNION
      SELECT d.refclassid, d.refobjid,
             'generated column ' || a.attname || ' of ' || a.attrelid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_attrdef AS ad
        ON d.classid = 'pg_catalog.pg_attrdef'::pg_catalog.regclass
       AND ad.oid = d.objid
      JOIN pg_catalog.pg_attribute AS a
        ON a.attrelid = ad.adrelid
       AND a.attnum = ad.adnum
      WHERE a.attgenerated = 's'
      UNION
      SELECT d.refclassid, d.refobjid,
             'generated column ' || a.attname || ' of ' || a.attrelid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_attribute AS a
        ON d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass
       AND a.attrelid = d.objid
       AND a.attnum = d.objsubid
      WHERE a.attgenerated = 's'
      UNION
      SELECT d.refclassid, d.refobjid, 'statistics ' || s.stxname || ' on ' || s.stxrelid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_statistic_ext AS s
        ON d.classid = 'pg_catalog.pg_statistic_ext'::pg_catalog.regclass
       AND s.oid = d.objid
    ), used (fn, used_by) AS (
      SELECT x.fn, u.used_by
      FROM uses AS u
      CROSS JOIN LATERAL (
        SELECT u.refobjid AS fn
        WHERE u.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        UNION ALL
        SELECT o.oprcode::pg_catalog.oid
        FROM pg_catalog.pg_operator AS o
        WHERE u.refclassid = 'pg_catalog.pg_operator'::pg_catalog.regclass
          AND o.oid = u.refobjid
        UNION ALL
        SELECT dc.refobjid
        FROM pg_catalog.pg_constraint AS con
        JOIN pg_catalog.pg_depend AS dc
          ON dc.classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass
         AND dc.objid = con.oid
         AND dc.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        WHERE u.refclassid = 'pg_catalog.pg_type'::pg_catalog.regclass
          AND con.contypid = u.refobjid
      ) AS x
      UNION
      SELECT x.fn, u.used_by
      FROM used AS u
      JOIN pg_catalog.pg_proc AS p
        ON p.oid = u.fn
       AND p.prosqlbody IS NOT NULL
       AND p.proconfig IS NULL
       AND NOT p.prosecdef
      JOIN pg_catalog.pg_depend AS d
        ON d.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
       AND d.objid = u.fn
      CROSS JOIN LATERAL (
        SELECT d.refobjid AS fn
        WHERE d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        UNION ALL
        SELECT o.oprcode::pg_catalog.oid
        FROM pg_catalog.pg_operator AS o
        WHERE d.refclassid = 'pg_catalog.pg_operator'::pg_catalog.regclass
          AND o.oid = d.refobjid
      ) AS x
    )
    SELECT DISTINCT u.fn::pg_catalog.regprocedure AS function_name, u.used_by
    FROM used AS u
    JOIN pg_catalog.pg_proc AS p ON p.oid = u.fn
    JOIN pg_catalog.pg_language AS l ON l.oid = p.prolang
    WHERE l.lanname = 'sql'
      AND p.prosqlbody IS NULL
      AND p.proconfig IS NULL
      AND NOT p.prosecdef
      AND p.pronamespace <> 'pg_catalog'::pg_catalog.regnamespace
      AND NOT EXISTS (SELECT 1
                      FROM pg_catalog.pg_depend AS e
                      WHERE e.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
                        AND e.objid = p.oid
                        AND e.deptype = 'e')
    ORDER BY 1, 2;
    

13. Indexes and stored generated columns that use earthdistance version 1.1, directly or through functions with an SQL-standard body. Expect no rows. For each listed object, see earthdistance.

    WITH RECURSIVE used (fn, via, used_by) AS (
      SELECT d.refobjid, d.refobjid, 'index ' || c.oid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_class AS c
        ON d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass
       AND c.oid = d.objid
       AND c.relkind IN ('i', 'I')
      WHERE d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
      UNION
      SELECT d.refobjid, d.refobjid,
             'generated column ' || a.attname || ' of ' || a.attrelid::pg_catalog.regclass::text
      FROM pg_catalog.pg_depend AS d
      LEFT JOIN pg_catalog.pg_attrdef AS ad
        ON d.classid = 'pg_catalog.pg_attrdef'::pg_catalog.regclass
       AND ad.oid = d.objid
      JOIN pg_catalog.pg_attribute AS a
        ON a.attgenerated = 's'
       AND ((a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
            OR (d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass
                AND a.attrelid = d.objid
                AND a.attnum = d.objsubid))
      WHERE d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
      UNION
      SELECT d.refobjid, u.via, u.used_by
      FROM used AS u
      JOIN pg_catalog.pg_proc AS p
        ON p.oid = u.fn
       AND p.prosqlbody IS NOT NULL
       AND p.proconfig IS NULL
      JOIN pg_catalog.pg_depend AS d
        ON d.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
       AND d.objid = u.fn
       AND d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
    )
    SELECT e.extversion, u.via::pg_catalog.regprocedure AS used_function, u.used_by
    FROM used AS u
    JOIN pg_catalog.pg_depend AS m
      ON m.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
     AND m.objid = u.fn
     AND m.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
     AND m.deptype = 'e'
    JOIN pg_catalog.pg_extension AS e ON e.oid = m.refobjid
    JOIN pg_catalog.pg_proc AS p ON p.oid = u.fn
    JOIN pg_catalog.pg_language AS l ON l.oid = p.prolang
    WHERE e.extname = 'earthdistance'
      AND e.extversion IN ('1.0', '1.1')
      AND l.lanname = 'sql'
      AND p.proconfig IS NULL
    ORDER BY 2, 3;
    

14. Views, materialized views, aggregates, and functions that use system catalog names or configuration parameters that a later PostgreSQL version removed. The removed_in column shows the first version without the name. Expect no rows with a removed_in version at or below your target version. A name can match text that is not a catalog reference, so review each listed object. See Objects that use removed system names.

    WITH removed (relation_name, identifier, removed_in) AS (
      VALUES (NULL, 'pg_is_in_backup', 15), (NULL, 'pg_backup_start_time', 15),
             (NULL, 'pg_start_backup', 15), (NULL, 'pg_stop_backup', 15),
             ('pg_database', 'datlastsysoid', 15),
             (NULL, 'force_parallel_mode', 16),
             ('pg_stat_bgwriter', 'checkpoints_timed', 17), ('pg_stat_bgwriter', 'checkpoints_req', 17),
             ('pg_stat_bgwriter', 'checkpoint_write_time', 17), ('pg_stat_bgwriter', 'checkpoint_sync_time', 17),
             ('pg_stat_bgwriter', 'buffers_checkpoint', 17), ('pg_stat_bgwriter', 'buffers_backend', 17),
             ('pg_stat_bgwriter', 'buffers_backend_fsync', 17),
             (NULL, 'pg_stat_get_bgwriter_timed_checkpoints', 17),
             (NULL, 'pg_stat_get_bgwriter_requested_checkpoints', 17),
             (NULL, 'pg_stat_get_checkpoint_write_time', 17), (NULL, 'pg_stat_get_checkpoint_sync_time', 17),
             (NULL, 'pg_stat_get_bgwriter_buf_written_checkpoints', 17),
             (NULL, 'pg_stat_get_buf_written_backend', 17), (NULL, 'pg_stat_get_buf_fsync_backend', 17),
             ('pg_stat_progress_vacuum', 'max_dead_tuples', 17),
             ('pg_stat_progress_vacuum', 'num_dead_tuples', 17),
             ('pg_database', 'daticulocale', 17), ('pg_collation', 'colliculocale', 17),
             ('element_types', 'domain_default', 17),
             (NULL, 'interval_accum', 17), (NULL, 'interval_accum_inv', 17), (NULL, 'interval_combine', 17),
             ('pg_stat_wal', 'wal_write', 18), ('pg_stat_wal', 'wal_sync', 18),
             ('pg_stat_wal', 'wal_write_time', 18), ('pg_stat_wal', 'wal_sync_time', 18),
             ('pg_stat_io', 'op_bytes', 18), ('pg_backend_memory_contexts', 'parent', 18),
             ('pg_attribute', 'attcacheoff', 18)
    ), definitions (object_name, definition) AS (
      SELECT 'view ' || c.oid::pg_catalog.regclass::text, pg_catalog.pg_get_viewdef(c.oid)
      FROM pg_catalog.pg_class AS c
      WHERE c.relkind IN ('v', 'm')
        AND c.relnamespace NOT IN ('pg_catalog'::pg_catalog.regnamespace,
                                   'information_schema'::pg_catalog.regnamespace)
      UNION ALL
      SELECT 'function ' || p.oid::pg_catalog.regprocedure::text,
             pg_catalog.concat_ws(' ', pg_catalog.pg_get_function_sqlbody(p.oid),
                                  pg_catalog.array_to_string(p.proconfig, ' '))
      FROM pg_catalog.pg_proc AS p
      WHERE (p.prosqlbody IS NOT NULL OR p.proconfig IS NOT NULL)
        AND p.pronamespace NOT IN ('pg_catalog'::pg_catalog.regnamespace,
                                   'information_schema'::pg_catalog.regnamespace)
      UNION ALL
      SELECT 'aggregate ' || a.aggfnoid::pg_catalog.regprocedure::text,
             pg_catalog.concat_ws(' ', a.aggtransfn, a.aggfinalfn, a.aggcombinefn,
                                  a.aggmtransfn, a.aggminvtransfn, a.aggmfinalfn)
      FROM pg_catalog.pg_aggregate AS a
      JOIN pg_catalog.pg_proc AS p ON p.oid = a.aggfnoid
      WHERE p.pronamespace NOT IN ('pg_catalog'::pg_catalog.regnamespace,
                                   'information_schema'::pg_catalog.regnamespace)
    )
    SELECT d.object_name, r.identifier, r.removed_in
    FROM definitions AS d
    JOIN removed AS r
      ON d.definition ~ ('\m' || r.identifier || '\M')
     AND (r.relation_name IS NULL OR d.definition ~ ('\m' || r.relation_name || '\M'))
    ORDER BY 3, 1, 2;
    

15. Database names that the upgrade cannot use. Expect no rows. To fix a listed database, its owner must connect to another database and run ALTER DATABASE "<database_name>" RENAME TO <new_name>; while no sessions are connected to to the database being renamed. The owner must also have the CREATEDB attribute. Then, update any applications that connect to the database.

    SELECT datname
    FROM pg_catalog.pg_database
    WHERE pg_catalog.strpos(datname, '''') > 0
       OR pg_catalog.strpos(datname, pg_catalog.chr(92)) > 0
       OR pg_catalog.strpos(datname, '=') > 0
       OR pg_catalog.strpos(datname, pg_catalog.chr(10)) > 0
       OR pg_catalog.strpos(datname, pg_catalog.chr(13)) > 0
    ORDER BY datname;
    

16. Relations that the upgrade locks, large objects, and the deployment's lock capacity. Add up locked_relations for the four databases with the highest values. On PostgreSQL 14 and 15, locked_relations includes views and materialized views, because the upgrade also locks them. If the total exceeds lock_capacity, increase max_locks_per_transaction until lock_capacity exceeds the total. The change restarts the database, so make it before you submit the upgrade. Many tables or large objects also lengthen the conversion, so rehearse the upgrade with your real schema and data.

    SELECT pg_catalog.current_database() AS database_name,
           (SELECT pg_catalog.count(*)
            FROM pg_catalog.pg_class AS c
            WHERE (c.relkind IN ('r', 'p', 'S')
                   OR (c.relkind IN ('v', 'm')
                       AND pg_catalog.current_setting('server_version_num')::integer < 160000))
              AND c.relnamespace NOT IN ('pg_catalog'::pg_catalog.regnamespace,
                                         'information_schema'::pg_catalog.regnamespace)) AS locked_relations,
           (SELECT pg_catalog.count(*)
            FROM pg_catalog.pg_largeobject_metadata) AS large_objects,
           pg_catalog.current_setting('max_locks_per_transaction')::integer
             * (pg_catalog.current_setting('max_connections')::integer
                + pg_catalog.current_setting('max_prepared_transactions')::integer) AS lock_capacity;
    

17. Functions without a search_path setting that indexes, materialized views, and extended statistics use. The query follows calls through operators, domain check constraints, and functions with an SQL-standard body. Run this query if you upgrade from PostgreSQL 16 or earlier to PostgreSQL 17 or later. A listed function whose body names objects outside pg_catalog without their schema makes maintenance commands fail after the upgrade. A function is unaffected only if its body, and every function that it calls, names every object outside pg_catalog with its schema. For each other listed function, its owner adds a search_path setting, as described in SQL functions in indexes, partition keys, and generated columns.

    WITH RECURSIVE refreshed (relid, used_by) AS (
      SELECT c.oid, c.oid::pg_catalog.regclass
      FROM pg_catalog.pg_class AS c
      WHERE c.relkind = 'm'
      UNION
      SELECT v.oid, f.used_by
      FROM refreshed AS f
      JOIN pg_catalog.pg_rewrite AS r ON r.ev_class = f.relid
      JOIN pg_catalog.pg_depend AS d
        ON d.classid = 'pg_catalog.pg_rewrite'::pg_catalog.regclass
       AND d.objid = r.oid
       AND d.refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass
      JOIN pg_catalog.pg_class AS v ON v.oid = d.refobjid AND v.relkind = 'v'
    ), refs (refclassid, refobjid, used_by) AS (
      SELECT d.refclassid, d.refobjid, c.oid::pg_catalog.regclass
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_class AS c ON c.oid = d.objid AND c.relkind IN ('i', 'I')
      WHERE d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass
      UNION
      SELECT d.refclassid, d.refobjid, f.used_by
      FROM refreshed AS f
      JOIN pg_catalog.pg_rewrite AS r ON r.ev_class = f.relid
      JOIN pg_catalog.pg_depend AS d
        ON d.classid = 'pg_catalog.pg_rewrite'::pg_catalog.regclass
       AND d.objid = r.oid
      UNION
      SELECT d.refclassid, d.refobjid, s.stxrelid::pg_catalog.regclass
      FROM pg_catalog.pg_depend AS d
      JOIN pg_catalog.pg_statistic_ext AS s ON s.oid = d.objid
      WHERE d.classid = 'pg_catalog.pg_statistic_ext'::pg_catalog.regclass
    ), used (fn, used_by) AS (
      SELECT x.fn, r.used_by
      FROM refs AS r
      CROSS JOIN LATERAL (
        SELECT r.refobjid AS fn
        WHERE r.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        UNION ALL
        SELECT o.oprcode::pg_catalog.oid
        FROM pg_catalog.pg_operator AS o
        WHERE r.refclassid = 'pg_catalog.pg_operator'::pg_catalog.regclass
          AND o.oid = r.refobjid
        UNION ALL
        SELECT dc.refobjid
        FROM pg_catalog.pg_constraint AS con
        JOIN pg_catalog.pg_depend AS dc
          ON dc.classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass
         AND dc.objid = con.oid
         AND dc.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        WHERE r.refclassid = 'pg_catalog.pg_type'::pg_catalog.regclass
          AND con.contypid = r.refobjid
      ) AS x
      UNION
      SELECT x.fn, u.used_by
      FROM used AS u
      JOIN pg_catalog.pg_proc AS p
        ON p.oid = u.fn
       AND p.prosqlbody IS NOT NULL
       AND NOT EXISTS (SELECT 1
                       FROM pg_catalog.unnest(p.proconfig) AS setting
                       WHERE setting LIKE 'search\_path=%')
      JOIN pg_catalog.pg_depend AS d
        ON d.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
       AND d.objid = u.fn
      CROSS JOIN LATERAL (
        SELECT d.refobjid AS fn
        WHERE d.refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
        UNION ALL
        SELECT o.oprcode::pg_catalog.oid
        FROM pg_catalog.pg_operator AS o
        WHERE d.refclassid = 'pg_catalog.pg_operator'::pg_catalog.regclass
          AND o.oid = d.refobjid
      ) AS x
    )
    SELECT DISTINCT p.oid::pg_catalog.regprocedure AS function_name, u.used_by
    FROM used AS u
    JOIN pg_catalog.pg_proc AS p ON p.oid = u.fn
    JOIN pg_catalog.pg_language AS l ON l.oid = p.prolang
    WHERE l.lanname NOT IN ('c', 'internal')
      AND p.prosqlbody IS NULL
      AND p.pronamespace NOT IN ('pg_catalog'::pg_catalog.regnamespace,
                                 'information_schema'::pg_catalog.regnamespace)
      AND NOT EXISTS (SELECT 1
                      FROM pg_catalog.unnest(p.proconfig) AS setting
                      WHERE setting LIKE 'search\_path=%')
      AND NOT EXISTS (SELECT 1
                      FROM pg_catalog.pg_depend AS e
                      WHERE e.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass
                        AND e.objid = p.oid
                        AND e.deptype = 'e')
    ORDER BY 1, 2;
    

Passing these checks does not guarantee that the upgrade succeeds or that your applications are compatible with the target version. Only a rehearsal on a staging deployment runs the whole conversion.