Migration to Version 12 v12

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 12 contains a number of changes that may affect compatibility with previous releases. Listed is the following incompatibilities:

• Remove the special behavior of oid columns.

  Previously, a normally-invisible oidcolumn could be specified during table creation using WITH OIDS; that ability has been removed. Columns can still be explicitly declared as type oid. Operations on tables that have columns created using WITH OIDS will need adjustment.

  The system catalogs that previously had hidden oidcolumns now have ordinary oid columns. Hence, SELECT * will now output those columns, whereas previously they would be displayed only if selected explicitly.

• Remove data types abstime, reltime, and tinterval.

  These are obsoleted by SQL-standard types such as timestamp.

• Remove the timetravel extension.

• Move recovery.conf settings into postgresql.conf.

  recovery.confis no longer used, and the server will not start if that file exists. recovery.signal and standby.signal files are now used to switch into non-primary mode. The trigger_file setting has been renamed to promote_trigger_file. The standby_mode setting has been removed.

• Do not allow multiple conflicting recovery_target* specifications.

  Specifically, only allow one of recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, and recovery_target_xid. Previously, multiple different instances of these parameters could be specified, and the last one was honored. Now, only one can be specified, though the same one can be specified multiple times and the last specification is honored.

• Cause recovery to advance to the latest timeline by default.

  Specifically, recovery_target_timeline now defaults to latest. Previously, it defaulted to current.

• Refactor code for geometric functions and operators.

  This could lead to more accurate, but slightly different, results compared to previous releases. Notably, cases involving NaN, underflow, overflow, and division by zero are handled more consistently than before.

• Improve performance by using a new algorithm for output of real and double precision values.

  Previously, displayed floating-point values were rounded to 6 (for real) or 15 (for double precision) digits by default, adjusted by the value of extra_float_digits. Now, whenever extra_float_digits is more than zero (as it now is by default), only the minimum number of digits required to preserve the exact binary value are output. The behavior is the same as before when extra_float_digits is set to zero or less.

  Also, formatting of floating-point exponents is now uniform across platforms: two digits are used unless three are necessary. In previous releases, Windows builds always printed three digits.

• random() and setseed() now behave uniformly across platforms.

  The sequence of random() values generated following a setseed() call with a particular seed value is likely to be different now than before. However, it will also be repeatable, which was not previously guaranteed because of interference from other uses of random numbers inside the server. The SQL random() function now has its own private per-session state to forestall that.

• Change SQL-style substring() to have standard-compliant greediness behavior.

  In cases where the pattern can be matched in more than one way, the initial subpattern is now treated as matching the least possible amount of text rather than the greatest; for example, a pattern such as %#"aa*#"% now selects the first group of a's from the input, not the last group.

• Do not pretty-print the result of xpath() or the XMLTABLE construct.

  In some cases, these functions would insert extra whitespace (newlines and/or spaces) in nodeset values. This is undesirable since depending on usage, the whitespace might be considered semantically significant.

• Rename command-line tool pg_verify_checksums to pg_checksums.

• In pg_restore, require specification of -f - to send the dump contents to standard output.

  Previously, this happened by default if no destination was specified, but that was deemed to be unfriendly.

• Disallow non-unique abbreviations in psql's \pset format command.

  Previously, for example, \pset format a chose aligned; it will now fail since that could equally well mean asciidoc.

• In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries.

  This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail.

• Cause DROP IF EXISTS FUNCTION/PROCEDURE/AGGREGATE/ROUTINE to generate an error if no argument list is supplied and there are multiple matching objects.

  Also improve the error message in such cases.

• Split the pg_statistic_ext catalog into two catalogs, and add the pg_stats_ext view of it.

  This change supports hiding potentially-sensitive statistics data from unprivileged users.

• Remove obsolete pg_constraint.consrc column.

  This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a check constraint's expression from pg_constraint is pg_get_expr(conbin, conrelid). pg_get_constraintdef() is also a useful alternative.

• Remove obsolete pg_attrdef.adsrc column.

  This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid).

• Mark table columns of type name as having “C” collation by default.

  The comparison operators for data type name can now use any collation, rather than always using “C” collation. To preserve the previous semantics of queries, columns of type name are now explicitly marked as having “C” collation. A side effect of this is that regular-expression operators on name columns will now use the “C” collation by default, not the database collation, to determine the behavior of locale-dependent regular expression patterns (such as \w). If you want non-C behavior for a regular expression on a name column, attach an explicit COLLATE clause. (For user-defined name columns, another possibility is to specify a different collation at table creation time; but that just moves the nonbackwards-compatibility to the comparison operators.)

• Treat object-name columns in the information_schema views as being of type name, not varchar.

  Per the SQL standard, object-name columns in the information_schema views are declared as being of domain type sql_identifier. In PostgreSQL, the underlying catalog columns are really of type name. This change makes sql_identifier be a domain over name, rather than varchar as before. This eliminates a semantic mismatch in comparison and sorting behavior, which can greatly improve the performance of queries on information_schema views that restrict an object-name column. Note however that inequality restrictions, for example:

  SELECT ... FROM information_schema.tables WHERE table_name < 'foo';

  will now use “C”-locale comparison semantics by default, rather than the database's default collation as before. Sorting on these columns will also follow “C” ordering rules. The previous behavior (and inefficiency) can be enforced by adding a COLLATE "default" clause.

• Remove the ability to disable dynamic shared memory.

  Specifically, dynamic_shared_memory_type can no longer be set to none.

• Parse libpq integer connection parameters more strictly.

  In previous releases, using an incorrect integer value for connection parameters connect_timeout, keepalives, keepalives_count, keep alives_idle, keepalives_interval and port resulted in libpq either ignoring those values or failing with incorrect error messages.

• The SELECT DISTINCT...ORDER BY clause of the SELECT DISTINCT query behavior differs after upgrade.

  If SELECT DISTINCT is specified or if a SELECT statement includes the SELECT DISTINCT ...ORDER BY clause then all the expressions in ORDER BY must be present in the select list of the SELECT DISTINCT query (applicable when upgrading from version 9.6 to any higher version of Advanced Server).