Starting with PestgresSQL 12, pg_upgrade fails if the database created in a previous version contains any tables using the WITH OIDS feature.
The resolution is to either set the tables as WITHOUT OIDS before the upgrade, or use native logical replication as a workaround to upgrade to PostgreSQL equal or higher than version 12.
The WITH OIDS table feature was deprecated on Postgres 12. Using pg_upgrade, it's possible to perform a major upgrade from a version of Postgres older than 12 to another version of Postgres equals or higher than 12. However, if there are any tables WITH OIDS in the database, pg_upgrade fails with:
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
The simplest approach is to run, on each table:
ALTER TABLE ... SET WITHOUT OIDS;
However this will take an exclusive lock on the table and might not be possible depending on the transaction backlog on the table.
Another alternative would be to, instead of using pg_upgrade, use pg_dump followed by pg_restore, provided that the pg_dump is being executed in the target Postgres >= 12 database.
Yet another alternative would be to perform a schema-only pg_dump followed by a setup using native logical replication. Please find more details about this strategy below.
1- The first step is to determine, from all tables WITH OIDS, which ones have the oid column referenced by your application. You can use the following query to get all tables WITH OIDS on the source database (PG < 12):
SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname)
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relhasoids
ORDER BY 1;
Consider for example we have the following tables:
testdb=# \dt
List of relations
Schema | Name | Type | Owner
public | categories | table | testuser
public | cust_hist | table | testuser
public | customers | table | testuser
public | departments | table | testuser
public | dept_emp | table | testuser
public | dept_manager | table | testuser
public | employees | table | testuser
public | inventory | table | testuser
public | orderlines | table | testuser
public | orders | table | testuser
public | products | table | testuser
public | reorder | table | testuser
public | salaries | table | testuser
public | titles | table | testuser
(14 rows)
Now consider for example that using the query above we verify that all of them are WITH OIDS:
testdb=# SELECT quote_ident(n.nspname) || '.' || quote_ident(c.relname)
testdb-# FROM pg_class c
testdb-# JOIN pg_namespace n
testdb-# ON n.oid = c.relnamespace
testdb-# WHERE n.nspname = 'public'
testdb-# AND c.relhasoids
testdb-# ORDER BY 1;
?column?
public.categories
public.cust_hist
public.customers
public.departments
public.dept_emp
public.dept_manager
public.employees
public.inventory
public.orderlines
public.orders
public.products
public.reorder
public.salaries
public.titles
(14 rows)
The oid column is not shown when you try a SELECT *, for example:
testdb=# SELECT * FROM categories;
category | categoryname
1 | Action
2 | Animation
3 | Children
4 | Classics
5 | Comedy
6 | Documentary
7 | Drama
8 | Family
9 | Foreign
10 | Games
11 | Horror
12 | Music
13 | New
14 | Sci-Fi
15 | Sports
16 | Travel
(16 rows)
But you can see the values if you explicitly request the oid column in the SELECT:
testdb=# SELECT *, oid FROM categories;
category | categoryname | oid
1 | Action | 16532
2 | Animation | 16533
3 | Children | 16534
4 | Classics | 16535
5 | Comedy | 16536
6 | Documentary | 16537
7 | Drama | 16538
8 | Family | 16539
9 | Foreign | 16540
10 | Games | 16541
11 | Horror | 16542
12 | Music | 16543
13 | New | 16544
14 | Sci-Fi | 16545
15 | Sports | 16546
16 | Travel | 16547
(16 rows)
So it's important to understand which tables really have the oid column referenced in your application, i.e., the tables where the oid column really should be kept in the target database.
2- Before the upgrade, for each table having the oid field referenced in your application, you need to do the following:
2.1- Start a new transaction:
BEGIN;
2.2- Inside the transaction, add a new oid column (it can't be NOT NULL because the table already has data), for example:
ALTER TABLE categories ADD COLUMN newoid BIGINT;
2.3- Copy the values from the oid field into the newoid field:
UPDATE categories SET newoid = oid;
2.4- Add a trigger to the table, so every time a new row is added, field newoid will receive the contents of oid:
CREATE OR REPLACE FUNCTION categories_oid_trig_func()
RETURNS TRIGGER AS
$$
BEGIN
UPDATE categories SET newoid = NEW.oid WHERE oid = NEW.oid;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER categories_oid_trig
AFTER INSERT ON categories
FOR EACH ROW EXECUTE FUNCTION categories_oid_trig_func();
2.5- Commit the transaction:
COMMIT;
You can leave the system running like that on the source database, confirming that for every new row, the newoid field receives the new value.
Make sure you have another Postgres >= 12 instance running, where the new database will be migrated to. Here you can consider an operating system upgrade, hardware upgrades, etc as well.
If your source database is Postgres 10 or 11, then you can have a native logical replication setup to flow the data between them.
4.1- Make sure pg_hba.conf on the source database allows both regular and replication connections from the target database.
4.2- Using the pg_dump from the target server, connect to the source server and take a schema-only dump. You will see how pg_dump version >= 12 will show warnings for each table WITH OIDS it finds on the source database, for example:
enterprisedb@dec27cd27c14:~$ pg_dump -h 0a250df3e85e -p 5432 -d testdb -s -f as11_schema_only.sql
pg_dump: warning: WITH OIDS is not supported anymore (table "categories")
pg_dump: warning: WITH OIDS is not supported anymore (table "cust_hist")
pg_dump: warning: WITH OIDS is not supported anymore (table "customers")
pg_dump: warning: WITH OIDS is not supported anymore (table "departments")
pg_dump: warning: WITH OIDS is not supported anymore (table "dept_emp")
pg_dump: warning: WITH OIDS is not supported anymore (table "dept_manager")
pg_dump: warning: WITH OIDS is not supported anymore (table "employees")
pg_dump: warning: WITH OIDS is not supported anymore (table "inventory")
pg_dump: warning: WITH OIDS is not supported anymore (table "orderlines")
pg_dump: warning: WITH OIDS is not supported anymore (table "orders")
pg_dump: warning: WITH OIDS is not supported anymore (table "products")
pg_dump: warning: WITH OIDS is not supported anymore (table "reorder")
pg_dump: warning: WITH OIDS is not supported anymore (table "salaries")
pg_dump: warning: WITH OIDS is not supported anymore (table "titles")
But the CREATE TABLE and all needed DDL for the table will be there in the schema-only dump file.
IMPORTANT: It's crucial to take the dump using pg_dump from the target host, connecting to the database on the source host.
4.3- Restore the schema-only dump on the target database:
psql -p 5432 -d testdb -f as11_schema_only.sql
Notice how it succeeds and the tables will be created on the target database.
4.4- On the source database, create a publication for all tables:
CREATE PUBLICATION upgradepub FOR ALL TABLES;
4.5- On the target database, create a subscription for this publication:
CREATE SUBSCRIPTION upgradesub
CONNECTION 'host=0a250df3e85e port=5432 dbname=testdb'
PUBLICATION upgradepub;
Of course changing the connection string above. You will see a message like this:
testdb=# CREATE SUBSCRIPTION upgradesub CONNECTION 'host=0a250df3e85e port=5432 dbname=testdb' PUBLICATION upgradepub;
NOTICE: created replication slot "upgradesub" on publisher
CREATE SUBSCRIPTION
And sure enough, you can see this slot is there on the source database:
testdb=# SELECT * FROM pg_replication_slots;
slot_name | upgradesub
plugin | pgoutput
slot_type | logical
datoid | 16385
database | testdb
temporary | f
active | t
active_pid | 583
xmin |
catalog_xmin | 1286
restart_lsn | 0/412CC218
confirmed_flush_lsn | 0/412CC250
4.6- Wait for the initial copy to finish. You can monitor logical replication progress via the logs and using the system views pg_replication_slots and pg_stat_replication (on the provider) and pg_stat_subscription (on the subscriber). The catchup time depends on how large is your database.
After the initial copy phase, you can keep the logical replication for as long as you want, and all data written to source database will also be written to the target database.
IMPORTANT: You need to monitor the replication slot, as if there is replication lag, WAL files will start to accumulate on the provider.
IMPORTANT: Any DDL you execute on the provider also needs to be executed on the subscriber, as this is not done automatically by native logical replication.
IMPORTANT: Sequence values are not replicated. As described in the cutover below, you will need to update the sequence values on the subscriber as they are on the provider.
When your scheduled cutover from the source to the target database arrives, then you will need to:
5.1- Stop applications (DOWNTIME STARTS);
5.2- Wait for any replication lag to decrease;
5.3- On the subscriber, drop the subscription:
DROP SUBSCRIPTION upgradesub;
5.4- On the subscriber, update the sequence values as they are on the provider. This can be done by first connecting to the provider and running:
cat << EOF | psql -h 0a250df3e85e -d testdb -At -o update_sequence_values.sql
SELECT format(
\$\$SELECT setval('%s', %s);
\$\$, x.sequence_name, x.next_sequence_value)
FROM (
SELECT quote_ident(n.nspname) || '.' ||
quote_ident(c.relname) AS sequence_name,
nextval(c.oid) AS next_sequence_value
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
) x
EOF
This will create a SQL file called update_sequence_values.sql with one setval command per sequence. Then on the local target database you can run this script with:
psql -d testdb -Af update_sequence_values.sql
5.5- For each table handled in step 2 above, perform the following steps, for example:
DROP TRIGGER categories_oid_trig;
DROP FUNCTION categories_oid_trig_func();
ALTER TABLE categories RENAME COLUMN newoid TO oid;
CREATE SEQUENCE categories_oid_seq;
SELECT setval('categories_oid_seq', max(oid)+1) FROM categories;
ALTER TABLE categories ALTER COLUMN oid SET DEFAULT nextval('categories_oid_seq');
ALTER TABLE categories ALTER COLUMN oid SET NOT NULL;
CREATE UNIQUE INDEX CONCURRENTLY ON categories (oid);
5.6- Point applications to the subscriber and start applications (DOWNTIME ENDS).
6 - Large object migration during logical replication upgrade process between old and new versions of PostgreSQL
Large objects must be migrated manually in this approach, and there are some peculiarities when dumping/restoring large objects between the old and the new PostgreSQL.
6.1- In the source database, old version:
edb=# SELECT lo_create(0);
lo_create
16963
(1 row)
edb=# SELECT lo_import('/etc/os-release');
lo_import
16964
(1 row)
edb=# SELECT oid,* FROM pg_largeobject_metadata;
oid | lomowner | lomacl
16963 | 10 |
16964 | 10 |
(2 rows)
edb=# SELECT count(*) FROM pg_largeobject;
count
1
(1 row)
edb=# SELECT count(*) FROM pg_largeobject_metadata;
count
2
(1 row)
edb=# SELECT * FROM pg_largeobject;
loid | pageno | data
16964 | 0 | \x4e414d453d2243656e744f53204c696e7578220a56455253494f4e3d22372028436f726529220a49443d2263656e746f73220a49445f4c494b453d227268656c206665646f7261220a56455253494f4e5f49443d2237220a5052455454595f4e414d453d2243656e744f53204c696e757820372028436f726529220a414e53495f434f4c4f523d22303b3331220a4350455f4e414d453d226370653a2f6f3a63656e746f733a63656e746f733a37220a484f4d455f55524c3d2268747470733a2f2f7777772e63656e746f732e6f72672f220a4255475f5245504f52545f55524c3d2268747470733a2f2f627567732e63656e746f732e6f72672f220a0a43454e544f535f4d414e54495342545f50524f4a4543543d2243656e744f532d37220a43454e544f535f4d414e54495342545f50524f4a4543545f56455253494f4e3d2237220a5245444841545f535550504f52545f50524f445543543d2263656e746f73220a5245444841545f535550504f52545f50524f445543545f56455253494f4e3d2237220a0a
(1 row)
edb=# SELECT pg_size_pretty(pg_table_size('pg_largeobject'));
pg_size_pretty
8192 bytes
(1 row)
6.2- Dump the large object from old version and slightly edit the output. The --oids option is required for pg_largeobject_metadata content, and it needs manual modification. On the server containing the old version:
[enterprisedb@old-epas ~]$ pg_dump --blobs --data-only -f $(date +%Y%m%d)_lo_data_export.dmp -Fc --table=pg_largeobject --oids --table=pg_largeobject_metadata -d edb
[enterprisedb@old-epas ~]$ pg_dump --data-only -f $(date +%Y%m%d)_lo_data_metadata_export.sql -Fp --oids --table=pg_largeobject_metadata -d edb
[enterprisedb@old-epas ~]$ file /tmp/20230209_lo_data_export.dmp
/tmp/20230209_lo_data_export.dmp: PostgreSQL custom database dump - v1.13-0
Now, on the server containing the new version:
[enterprisedb@-new-epas ~] file lo_data_export.dmp
$lo_data_export.sql: PostgreSQL custom database dump - v1.13-0
Back to the server containing the old version:
[enterprisedb@old-epas ~]$ grep COPY 20230209_lo_data_metadata_export.sql
COPY pg_catalog.pg_largeobject_metadata (lomowner, lomacl) WITH OIDS FROM stdin;
[enterprisedb@old-epas ~]$ sed -e 's/(lomowner, lomacl) WITH OIDS/(oid, lomowner, lomacl)/g' -i 20230209_lo_data_metadata_export.sql
6.3- Copy the dumped files to the new environment and restore to the upgrade target database:
[enterprisedb@new-epas ~]$ psql -d edb -f /tmp/20230209_lo_data_metadata_export.sql
SET
SET
SET
SET
SET
SET
SET
set_config
(1 row)
SET
SET
SET
SET
COPY 2
[enterprisedb@new-epas14 ~]$ pg_restore -Fc --data-only --table=pg_largeobject -d edb /tmp/20230209_lo_data_export.dmp
[enterprisedb@new-epas14 ~]$ psql -d edb
psql (14.3.0, server 14.3.0)
Type "help" for help.
edb=# SELECT count(*) FROM pg_largeobject_metadata;
count
2
(1 row)
edb=# SELECT count(*) FROM pg_largeobject;
count
1
(1 row)
edb=# SELECT * FROM pg_largeobject_metadata ;
oid | lomowner | lomacl
16963 | 10 |
16964 | 10 |
(2 rows)
edb=# SELECT * FROM pg_largeobject ;
loid | pageno | data
16964 | 0 | \x4e414d453d2243656e744f53204c696e7578220a56455253494f4e3d22372028436f726529220a49443d2263656e746f73220a49445f4c494b453d227268656c206665646f7261220a56455253494f4e5f49443d2237220a5052455454595f4e414d453d2243656e744f53204c696e757820372028436f726529220a414e53495f434f4c4f523d22303b3331220a4350455f4e414d453d226370653a2f6f3a63656e746f733a63656e746f733a37220a484f4d455f55524c3d2268747470733a2f2f7777772e63656e746f732e6f72672f220a4255475f5245504f52545f55524c3d2268747470733a2f2f627567732e63656e746f732e6f72672f220a0a43454e544f535f4d414e54495342545f50524f4a4543543d2243656e744f532d37220a43454e544f535f4d414e54495342545f50524f4a4543545f56455253494f4e3d2237220a5245444841545f535550504f52545f50524f445543543d2263656e746f73220a5245444841545f535550504f52545f50524f445543545f56455253494f4e3d2237220a0a
(1 row)