Migrating to PostgreSQL Version 13: Incompatibilities You Should Be Aware Of

The PostgreSQL 13 Beta is out in the testing phase with a rich feature set. It is a very good learning effort to participate in the testing of one of the finest databases in the world. It does not matter how much development, coding, and administration experience you have for the testing of the PostgreSQL version; you can participate in reviewing the documentation, validation of features, and on some small tasks. The complete testing guide is also available on the wiki page.

Soon, the PostgreSQL 13 GA will be available, and the people who require the new features of PostgreSQL will want to migrate to that version. This is a major release, so it requires some effort to upgrade.

Dump/Restore (pg_dump)

One of the safest and oldest methods to upgrade is a dump and restore. When we are saying safest, it means the database breakup is almost none after the restore to the new version, but it has its own limitations as it requires a lot of time and extra space to take the backup. PostgreSQL has the tools pg_dump and pg_restore, and the complete documentation can be found at the PostgreSQL official documentation site.

pg_upgrade

PostgreSQL has an in-place upgrade tool. It has the capability to upgrade the PostgreSQL major version without taking extra space and requires a lot less time to upgrade as compared to dump/restore.

The following is the list of observed incompatibilities:

1 – SIMILAR TO … ESCAPE NULL and substring(text FROM pattern ESCAPE text) return NULL.

In case id ESCAPE NULL, the application will get NULL instead of any value. Previously returned true, if ESCAPE NULL is specified. But in PostgreSQL 13, it returns the NULL which is correct behavior, but you need to modify your application if expecting true in that case.

PostgreSQL Version < 13

# SELECT 'abc' SIMILAR TO 'ab_' ESCAPE NULL AS text;
 text 
----------
 t
(1 row)

PostgreSQL Version  13

# SELECT 'abc' SIMILAR TO 'ab_' ESCAPE NULL AS text;  
text 
------


(1 row)

2 . Have jsonb_to_tsvector() properly check the “string” parameter.

Fixed a bug in the JSON function “jsonb_to_tsvector”, in case of the wrong parameter. Previously it was matching only five characters instead of six, which was wrong, and produced results instead of throwing an error. If an application has that issue, then instead of a result you will get an error and you need to fix that in your application.

PostgreSQL Version  < 13

# SELECT json_to_tsvector('"abc"'::json, '"strinX"');
 json_to_tsvector 
------------------
 'abc':1
(1 row)

PostgreSQL Version  13

# SELECT json_to_tsvector('"abc"'::json, '"strinX"');
ERROR:  wrong flag in flag array: "strinX"
HINT:  Possible values are: "string", "numeric", "boolean", "key", and "all".

3 – In ltree, when using adjacent asterisks with braces, e.g. “.*{2}.*{3}”, it properly interprets that as “.*{5}”.

This long-awaited bug fix took care of the lquery’s behavior for consecutive ‘*’ items with braces. The following example should produce true in both cases, but it produces false in case of *{2}.*{3}’, which is wrong. 

PostgreSQL Version  < 13

# SELECT '1.0.0.0.1'::ltree ~ '*{2}.*{3}'::lquery;
 ?column? 
----------
 f
(1 row)

# SELECT '1.0.0.0.1'::ltree ~ '*{5}'::lquery;
 ?column? 
----------
 t
(1 row)

PostgreSQL Version  13

# SELECT '1.0.0.0.1'::ltree ~ '*{2}.*{3}'::lquery;
 ?column? 
----------
 t
(1 row)

# SELECT '1.0.0.0.1'::ltree ~ '*{5}'::lquery;
 ?column? 
----------
 t
(1 row)

4 – There is a change in the non-default effective_io_concurrency. The old value needs to be calculated for the new value. Follow this formula to compute the new value from the old value:

SELECT round(sum(OLD / n::float)) FROM generate_series(1, OLD) s(n);

Set the value of effective_io_concurrency returned by the above select statement.

5 – Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views.

PostgreSQL Version < 13

# select a.pid, backend_type, ssl  from pg_stat_activity a, pg_stat_ssl s where s.pid = a.pid;
 pid  |         backend_type         | ssl 
------+------------------------------+-----
 2286 | autovacuum launcher          | f
 2289 | logical replication launcher | f
 2288 | pg_stat_monitor collector    | f
 2291 | client backend               | f
 2284 | background writer            | f
 2283 | checkpointer                 | f
 2285 | walwriter                    | f
(7 rows)

# select a.pid, backend_type, gss_authenticated  from pg_stat_activity a, pg_stat_gssapi s where s.pid = a.pid;
 pid  |         backend_type         | gss_authenticated 
------+------------------------------+-------------------
 2286 | autovacuum launcher          | f
 2289 | logical replication launcher | f
 2288 | pg_stat_monitor collector    | f
 2291 | client backend               | f
 2284 | background writer            | f
 2283 | checkpointer                 | f
 2285 | walwriter                    | f
(7 rows)

PostgreSQL Version 13

# select a.pid, backend_type, ssl  from pg_stat_activity a, pg_stat_ssl s where s.pid = a.pid;
 pid  |  backend_type  | ssl 
------+----------------+-----
 2329 | client backend | f
(1 row)

# select a.pid, backend_type, gss_authenticated  from pg_stat_activity a, pg_stat_gssapi s where s.pid = a.pid;
 pid  |  backend_type  | gss_authenticated 
------+----------------+-------------------
 2329 | client backend | f
(1 row)

 

6 – Fix pageinspect’s bt_metap() to return more appropriate data types that are less likely to overflow.

The function “bt_metap” won’t give an error in case of integer overflow. In my opinion, it will not break the migration, because it is a bug fix.

SELECT * FROM bt_metap(‘index’)\gx
ERROR: value “2180413846” is out of range for type integer

 

7 – Rename some recovery-related wait events.

Some recovery-related wait events have been changed and you need to replace that event. 

RecoveryWalAll -> RecoveryWalStream

RecoveryWalStream -> RecoveryRetrieveRetryInterval.

 

8 – Fix ALTER FOREIGN TABLE … RENAME COLUMN to return a more appropriate command tag. Previously it returned ALTER TABLE but now returns ALTER FOREIGN TABLE.

PostgreSQL Version < 13

# ALTER FOREIGN TABLE test RENAME COLUMN b TO c;
ALTER TABLE

PostgreSQL Version  13

# ALTER FOREIGN TABLE test RENAME COLUMN b TO c;
ALTER FOREIGN TABLE

 

9 – Fix ALTER MATERIALIZED VIEW … RENAME COLUMN to return a more appropriate command tag.

PostgreSQL Version < 13

# ALTER MATERIALIZED VIEW mv RENAME COLUMN a TO b;
ALTER TABLE

PostgreSQL Version  13

# ALTER MATERIALIZED VIEW mv RENAME COLUMN a TO b;
ALTER MATERIALIZED VIEW

 

The following support has been removed:

1 – Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax. 

2 – Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax

3 – Remove support for “opaque” pseudo-types used by pre-PostgreSQL 7.3 servers 

by Ibrar Ahmed via Percona Database Performance Blog