We all know that PostgreSQL 10 has reached end of support, so if you’re still running it, the time to upgrade is now. Staying on an unsupported version means no more security fixes or bug patches, which puts your applications and data at risk.
There are a few different upgrade paths to consider. For example, you can use pg_dump/pg_restore, or go with a logical replication approach as shown in Replication Between PostgreSQL Versions Using Logical Replication.
In this post, we’ll walk through an example upgrade from PostgreSQL 10 to PostgreSQL 13 using the native pg_upgrade utility, including how to handle tables with OID columns and installed extensions along the way.
We have PostgreSQL 10 running with the below list of extensions and some tables with OID columns:
|
1 2 3 4 5 6 7 8 9 |
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) |
|
1 2 3 4 5 6 7 8 9 |
Table "public.oid_test" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-------------------+-----------+----------+---------+----------+--------------+------------- id | integer | | | | plain | | name | character varying | | | | extended | | address | character varying | | | | extended | | phone | integer | | | | plain | | <strong>Has OIDs: yes </strong> |
|
1 2 3 4 5 6 |
Table "public.craft" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+---------+----------+--------------+------------- name | character varying(90) | | | | extended | | <strong>Has OIDs: yes </strong> |
|
1 2 3 4 5 6 7 8 |
Table "public.ports" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+---------+----------+--------------+------------- name | character varying(90) | | | | extended | | Indexes: "ports_pkey" PRIMARY KEY, btree (oid) <strong>Has OIDs: yes </strong> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres@xx.xx.xx.xx:~$ /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 --check 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 |
|
1 2 3 4 5 6 |
postgres@xx.xx.xx.xx:~$ 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.
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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.
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
postgres@xx.xx.xx.xx:~$ /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 |
|
1 2 3 |
postgres@xx.xx.xx.xx:~$ 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.
|
1 |
root@xx.xx.xx.xx:/home/ubuntu# sudo apt-get install postgresql-13-postgis-3 |
Re-run the pg_upgrade checks:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres@xx.xx.xx.xx:~$ /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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
postgres@xx.xx.xx.xx:~$ /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 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 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 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 ok Copying user relation files ok 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:
|
1 |
postgres@xx.xx.xx.xx:~$ /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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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:
|
1 2 3 4 |
postgres@xx.xx.xx.xx:~$ /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:
|
1 2 3 |
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:
|
1 2 3 4 |
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
Upgrading PostgreSQL can feel straightforward until you run into details like OID columns, extension compatibility, or missing libraries. As this example shows, even a direct pg_upgrade requires careful preparation and post-upgrade adjustments.
That’s why the bigger challenge isn’t just performing one upgrade; it’s managing PostgreSQL at scale over time. Every upgrade, patch, and configuration change takes focus away from other priorities.
If you’re starting to feel the weight of managing PostgreSQL in-house, you’re not alone. Take a look at our guide, PostgreSQL in the Enterprise: The Real Cost of Going DIY, to understand why running PostgreSQL internally demands more time, expertise, and resources than most teams expect—and what it means for IT and the business.
PostgreSQL in the Enterprise: The Real Cost of Going DIY
About the Author
Abdul SayeedAbdul Sayeed is a PostgreSQL enthusiastic and Cassandra DBA and love to explore Open Source Database Technologies and has more than 11+ years of working experience as Support & Remote DBA, Consultant, Technical Specialist, Administrator and Trainer in PostgreSQL & Cassandra database Technologies. Abdul Sayeed holds Masters in Computer Applications and joined Percona in Oct, 2022 as Sr DBA in MSP Team. Prior to joining Percona, he worked at Datavail Corporation as Technical Manager and supported clients across AWS (RDS & Aurora), Microsoft Azure (Single and Flexi Server) & On-Prem Enviorments and managed 12 Members Team at offshore. Previous to Datavail, he worked with Tech Mahindra, Cognizant as Lead DBA and Senior System Engineer at EnterpriseDB over 5+ years and explored all EDB tools and support multiple clients across world. Apart from Open Source Database, he has interest in watching & playing cricket and support Indian Cricket Team.
Thanks for the post. Why didn’t you use –link option prior to upgrade?