PostgreSQL Upgrade: Tricks With OID Columns and Extensions

April 3, 2023

We are all aware that PostgreSQL 10 has reached end of support, and there is a need to upgrade to the latest version or any application-compatible PG version.

Below are some of the upgrade options available to upgrade to PG11 or above.

Pg_dump and pg_restore:

We can refer to PostgreSQL Upgrade Using pg_dump/pg_restore on dump/restore methods.

Using Logical Method:

We can refer to Replication Between PostgreSQL Versions Using Logical Replication on upgrading using a logical replication method.

In this blog, we will upgrade PG10, which has tables with OID columns and some extensions to PG13, as an example, using the native pg_upgrade utility.

We have PostgreSQL 10 running with the below list of extensions and some tables with OID columns:

Shell

List of installed extensions:

Name | Version | Schema | Description

--------------------+---------+------------+------------------------------------------------------------

pg_buffercache | 1.3 | public | examine the shared buffer cache

pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgis | 3.2.3 | public | PostGIS geometry and geography spatial types and functions

postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers

(5 rows)

Shell

Table "public.oid_test"

---------+-------------------+-----------+----------+---------+----------+--------------+-------------

Has OIDs: yes

Shell

Table "public.craft"

--------+-----------------------+-----------+----------+---------+----------+--------------+-------------

Has OIDs: yes

Shell

Table "public.ports"

--------+-----------------------+-----------+----------+---------+----------+--------------+-------------

Indexes:

"ports_pkey" PRIMARY KEY, btree (oid)

Has OIDs: yes

What is OID?

Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables. Type OID represents an object identifier.

If we have tables with OID created, then the pg_upgrade check will fail with the below error FATAL message:

Shell

Performing Consistency Checks on Old Live Server

------------------------------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for tables WITH OIDS fatal

Your installation contains tables declared WITH OIDS, which is not

supported anymore. Consider removing the oid column using

ALTER TABLE ... SET WITHOUT OIDS;

A list of tables with the problem is in the file:

tables_with_oids.txt

Failure, exiting

Shell

[email protected]:~$ more tables_with_oids.txt

In database: abs_test

public.craft

public.ports

In database: postgres

public.oid_test

We can use the below SQL to find tables with OID, and it also generates the DDL to remove.

Shell

SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE 1=1

AND c.relkind = 'r'

AND c.relhasoids = true

AND n.nspname <> 'pg_catalog'

order by n.nspname, c.relname;

Connect to each database in the cluster and run the above SQL query:

Shell

abs_test=# SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'

abs_test-# FROM pg_catalog.pg_class c

abs_test-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

abs_test-# WHERE 1=1

abs_test-# AND c.relkind = 'r'

abs_test-# AND c.relhasoids = true

abs_test-# AND n.nspname <> 'pg_catalog'

abs_test-# order by n.nspname, c.relname;

?column?

------------------------------------------------

ALTER TABLE "public"."craft" SET WITHOUT OIDS;

ALTER TABLE "public"."ports" SET WITHOUT OIDS;

(2 rows)

======================================================

postgres=# SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'

postgres-# FROM pg_catalog.pg_class c

postgres-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

postgres-# WHERE 1=1

postgres-# AND c.relkind = 'r'

postgres-# AND c.relhasoids = true

postgres-# AND n.nspname <> 'pg_catalog'

postgres-# order by n.nspname, c.relname;

?column?

---------------------------------------------------

ALTER TABLE "public"."oid_test" SET WITHOUT OIDS;

(1 row)

Now we can execute the DDL to ALTER these tables.

Shell

postgres=# ALTER TABLE "public"."oid_test" SET WITHOUT OIDS;

ALTER TABLE

postgres=# c abs_test

You are now connected to database "abs_test" as user "postgres".

abs_test=# ALTER TABLE "public"."craft" SET WITHOUT OIDS;

ALTER TABLE

abs_test=# ALTER TABLE "public"."ports" SET WITHOUT OIDS;

ALTER TABLE

Once ALTER is completed, we can perform once again pg_upgrade checks:

Shell

[email protected]:~$ /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/10/bin/ --new-bindir=/usr/lib/postgresql/13/bin --old-datadir=/var/lib/postgresql/10/main --new-datadir=/var/lib/postgresql/13/main --old-options='-c config_file=/etc/postgresql/10/main/postgresql.conf' --new-options='-c config_file=/etc/postgresql/13/main/postgresql.conf' --old-port=5432 --new-port=5433 --check

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for tables WITH OIDS ok

Checking for invalid "sql_identifier" user columns ok

Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the

new installation. You can add these libraries to the new installation,

or remove the functions using them from the old installation. A list of

problem libraries is in the file:

loadable_libraries.txt

Failure, exiting

Shell

[email protected]:~$ more loadable_libraries.txt

could not load library "$libdir/postgis-3": ERROR: could not access file "$libdir/postgis-3": No such file or directory

In database: postgres

It shows an error related to PostGIS Extension; hence we need to download the PostGIS package for PostgreSQL-13.

Shell

[email protected]:/home/ubuntu# sudo apt-get install postgresql-13-postgis-3

Re-run the pg_upgrade checks:

Shell

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for tables WITH OIDS ok

Checking for invalid "sql_identifier" user columns ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

Checking for new cluster tablespace directories ok

*Clusters are compatible*

Now we will perform the actual upgrade as shown below:

Shell

Performing Consistency Checks

-----------------------------

Checking cluster versions ok

Checking database user is the install user ok

Checking database connection settings ok

Checking for prepared transactions ok

Checking for system-defined composite types in user tables ok

Checking for reg* data types in user tables ok

Checking for contrib/isn with bigint-passing mismatch ok

Checking for tables WITH OIDS ok

Checking for invalid "sql_identifier" user columns ok

Creating dump of global objects ok

Creating dump of database schemas

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

Checking for new cluster tablespace directories ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

------------------

Analyzing all rows in the new cluster ok

Freezing all rows in the new cluster ok

Deleting files from new pg_xact ok

Copying old pg_xact to new server ok

Setting oldest XID for new cluster ok

Setting next transaction ID and epoch for new cluster ok

Deleting files from new pg_multixact/offsets ok

Copying old pg_multixact/offsets to new server ok

Deleting files from new pg_multixact/members ok

Copying old pg_multixact/members to new server ok

Setting next multixact ID and offset for new cluster ok

Resetting WAL archives ok

Setting frozenxid and minmxid counters in new cluster ok

Restoring global objects in the new cluster ok

Restoring database schemas in the new cluster

Copying user relation files

Setting next OID for new cluster ok

Sync data directory to disk ok

Creating script to analyze new cluster ok

Creating script to delete old cluster ok

Checking for extension updates notice

Your installation contains extensions that should be updated

with the ALTER EXTENSION command. The file

update_extensions.sql

when executed by psql by the database superuser will update

these extensions.

Upgrade Complete

----------------

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

./delete_old_cluster.sh

Start the Target DB cluster:

Shell

[email protected]:~$ /usr/lib/postgresql/13/bin/pg_ctl -D /var/lib/postgresql/13/main --options='-c config_file=/etc/postgresql/13/main/postgresql.conf' start

Connect to DB and run the UPDATE Extension SQL command:

Shell

postgres=# dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+------------------------------------------------------------

pg_buffercache | 1.3 | public | examine the shared buffer cache

pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgis | 3.2.3 | public | PostGIS geometry and geography spatial types and functions

postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers

(5 rows)

postgres=# ALTER EXTENSION "postgis" UPDATE;

ALTER EXTENSION

postgres=# ALTER EXTENSION "pg_stat_statements" UPDATE;

ALTER EXTENSION

postgres=# dx

List of installed extensions

Name | Version | Schema | Description

--------------------+---------+------------+------------------------------------------------------------

pg_buffercache | 1.3 | public | examine the shared buffer cache

pg_stat_statements | 1.8 | public | track execution statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

postgis | 3.3.2 | public | PostGIS geometry and geography spatial types and functions

postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers

(5 rows)

Now we can perform vacuum analyze to update all statistics:

Shell

[email protected]:~$ /usr/lib/postgresql/13/bin/vacuumdb --all --analyze-only -p 5433

vacuumdb: vacuuming database "abs_test"

vacuumdb: vacuuming database "postgres"

vacuumdb: vacuuming database "template1"

Note: In case we want to retain OID post upgrade, then we need to follow a couple of steps:

Before the upgrade, on the source database:

Shell

ALTER TABLE has_oids ADD newoid bigint NOT NULL;

UPDATE has_oids SET newoid = oid;

ALTER TABLE has_oids SET WITHOUT OIDS;

After the upgrade, on the target database:

Shell

ALTER TABLE has_oids RENAME newoid TO oid;

CREATE SEQUENCE has_oids_oid_seq OWNED BY has_oids.oid;

ALTER TABLE has_oids ALTER oid SET DEFAULT nextval('has_oids_oid_seq');

SELECT setval('has_oids_oid_seq', ???);

Conclusion

We can use logical replication to upgrade from PostgreSQL 10 to PostgreSQL 13. However, there are some pros and cons associated, like the primary key on each table. Hence, we have performed the upgrade using pg_upgrade. Though it required downtime, it helped us to understand the entire cluster, and as per need, we can ALTER the OID column table and re-ALTER post upgrade if we really need OID or if we have made changes in the application code then we need to keep these tables without OID. Also, we have manually upgraded the extensions.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

Share This Post!

Connect with

Label

0 Comments

PostgreSQL Upgrade: Tricks With OID Columns and Extensions

PostgreSQL Upgrade: Tricks With OID Columns and Extensions

What is OID?

Conclusion

Share This Post!

Recommend

DSCC: No ProMotion (120Hz) on vanilla iPhones until 2025

科阳半导体完成超5亿元融资

Amazon gave 'small business' and 'Black-owned business' badges to companies that...

Is TestOps the Future of Software Testing?

Dozer exits stealth to help any developer build real-time data apps 'in minutes'

打得过索尼PS6？微软新一代Xbox主机意外曝光：有惊喜

Easier way to configure flexible number ranges with prefixes for Sales Documents...

These Y Combinator-backed startups are trying to build 'ChatGPT for X'

iOS 16.4 update may be coming soon to fix your iPhone's Weather and Wi-Fi issues

Wealthiest People in Norway (April 4, 2023)

About Joyk