Migration to Version 13 v13
A dump/restore using pg_dumpall
or pg_upgrade
or logical replication is required for migrating data from any previous release. See Upgrading an Installation With pg_upgrade for information on migrating to new major releases.
Version 13 contains a number of changes that may affect compatibility with previous releases. Listed is the following incompatibilities:
Change
SIMILAR TO ... ESCAPE NULL
to returnNULL
.This new behavior matches the SQL specification. Previously a null
ESCAPE
value was taken to mean using the default escape string (a backslash character). This also applies tosubstring
(text FROM pattern ESCAPE text)
. The previous behavior has been retained in old views by keeping the original function unchanged.Make
json[b]_to_tsvector()
fully check the spelling of itsstring
option.Change the way non-default
effective_io_concurrency
values affect concurrency.Previously, this value was adjusted before setting the number of concurrent requests. The value is now used directly. Conversion of old values to new ones can be done using:
Prevent display of auxiliary processes in
pg_stat_ssl
andpg_stat_gssapi
system views. Queries that join these views topg_stat_activity
and wish to see auxiliary processes will need to use left joins.Rename various
wait events
to improve consistency.Fix
ALTER FOREIGN TABLE ... RENAME COLUMN
to return a more appropriate command tag. Previously it returnedALTER TABLE
; now it returnsALTER FOREIGN TABLE
.Fix
ALTER MATERIALIZED VIEW ... RENAME COLUMN
to return a more appropriate command tag. Previously it returnedALTER TABLE
; now it returnsALTER MATERIALIZED VIEW
.Rename configuration parameter
wal_keep_segments
towal_keep_size
.This determines how much WAL to retain for standby servers. It is specified in megabytes, rather than number of files as with the old parameter. If you previously used
wal_keep_segments
, the following formula will give you an approximately equivalent setting:Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax.
Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax.
Remove support for "opaque" pseudo-types used by pre-PostgreSQL 7.3 servers.
Remove support for upgrading unpackaged (pre-9.1) extensions.
The
FROM
option ofCREATE EXTENSION
is no longer supported. Any installations still using unpackaged extensions should upgrade them to a packaged version before updating to PostgreSQL 13.Remove support for
posixrules
files in the timezone database.IANA's timezone group has deprecated this feature, meaning that it will gradually disappear from systems' timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL's support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom
posixrules
file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.In ltree, when an
lquery
pattern contains adjacent asterisks with braces, e.g.,*{2}.*{3}
, properly interpret that as*{5}
.Fix pageinspect's
bt_metap()
to return more appropriate data types that are less likely to overflow.The
SELECT DISTINCT...ORDER BY
clause of theSELECT DISTINCT
query behavior differs after upgrade.If
SELECT DISTINCT
is specified or if aSELECT
statement includes theSELECT DISTINCT …ORDER BY
clause then all the expressions inORDER BY
must be present in the select list of theSELECT DISTINCT
query (applicable when upgrading from version 9.6 to any higher version of Advanced Server).All objects depending on collation must be rebuilt using
pg_upgrade
to migrate from earlier versions (12, 11, 10, 9.6) using ICU to the latest Advanced Server version.A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when using
pg_upgrade
to upgrade to server binaries linked with a newer version of ICU. When this happens, all objects depending on the collation should be rebuilt, for example, usingREINDEX
. When that is done, the collation version can be refreshed using the commandALTER COLLATION ... REFRESH VERSION
. This will update the system catalog to record the current collator version and will make the warning go away. Note that this does not actually check whether all affected objects have been rebuilt correctly.