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:
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
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!
Subscribe
Login
0 Comments
