🧘🏾 🐷 🐧 PostgreSQL version update, or How not to drop the database when updating? 👨‍🎨 🧝🏿 🙀

We decided to share our PostgreSQL upgrade team's checklist. In it, we took as a basis the features presented in the release of the official PostgreSQL documentation , conducted an analysis for compatibility issues and who should pay attention to the updated functionality.

My name is Alexander Kotsyuruba, I manage the development of internal services at DomClick.ru.

The article is divided into parts with headings. Each heading refers to upgrading PostgreSQL from one version to another. Within each chapter, we have highlighted:

Feature
Risk Potential Incompatibility
- Application disruption
- Drop in performance
Who to look for when updating
- System Administrator
- Developer
Comment

So, let's begin.

Update methods

pg_upgradeIs not the most reliable way to update PostreSQL. For example, you might get an error while REINDEX from a previous PostgreSQL version.
logical replication — , downtime . , logical replication 10. pglogical ( 2ndquadrant), 9.4 12. , PostgreSQL<10.0, .
pg_dumpall — . — downtime.
pg_dumpall --globals-only pg_dump --create. , , , PostgreSQL.

PostgreSQL 9.5 -> 9.6


^{to_char() ( ) , to_char('-4 years'::interval, 'YY') -04, -4.}			^{- to_char() , .}
^{extract() ( ) extract() «», . infinity -infinity , (, year, epoch), NULL (, day, hour). .}			^{extract() c , .}
^{pg_stat_activity , ( , ) , . pg_stat_activity . . waiting wait_event_type wait_event.}			^{- . .}
^{, email host ( ) , , tsvector, .}			^{, , . PostgreSQL . pg_dump, . .. pg_upgrade.}
^{CREATEUSER/NOCREATEUSER CREATE ROLE ( ) CREATEUSER SUPERUSER . , ( ), CREATEROLE. , , .}			^{, 9.6.}
^{, pg_, ( ) . , initdb.}
^{psql -c --no-psqlrc ( , ) --no-psqlrc ( -X). psql.}			^psql.
^{pg_restore -t, , ( )}
^{NextXID pg_controldata ( , ) --ID- : . / LSN, .}
^{, , ( ) , . , ( pg_upgrade), ALTER EXTENSION UPDATE ( ).}			^{pg_upgrade extension. pg_upgrade}

MVCC ( )

, , . , , , . old_snapshot_threshold , MVCC . . , , , , .

Solves the problem of "bloating" the database due to "opened a transaction and went on vacation"; thoughtless updates that take hours; long selecton both master and slave. This is one of two options, thanks to which it will be possible to solve the problem, due to which they cannot reload long requests from master to slave.

PostgreSQL 9.6 -> 10.0

Feature	Risk	Whom to pay attention to	Comment
^{After upgrading from pg_upgradeany previous major version of PostgreSQL, the hash indexes must be rebuilt (Mithun Sai, Robert Haas, Amit Kapila) This requirement is due to significant improvements in hash indexes. To facilitate the task of reindexing, it pg_upgradewill create a helper script.}	^{Application disruption}	^{System Administrator}	^{Another argument against pg_upgrade}
^{pg_xlog pg_wal, pg_clog pg_xact ( ) — , , , . .}
^{SQL, «xlog» «wal» ( ) , pg_switch_xlog() pg_switch_wal(), pg_receivexlog — pg_receivewal, --xlogdir — --waldir. pg_xlog ; «xlog» .}
^{WAL location lsn ( ) .}
^{pg_basebackup WAL, ( ) pg_basebackup -X/--wal-method stream. none. pg_basebackup -x ( -X fetch).}
^{pg_hba.conf ( ) replication . , , all. - replication. , .}
^{pg_ctl ( ) pg_ctl -w.}
^{log_directory pg_log log ( )}
^{ssl_dh_params_file OpenSSL DH ( ) dh1024.pem. , dh1024.pem ; , DH.}
^{DH, DH- OpenSSL, 2048 ( ) DH 1024 2048 , DH . SSL, Java Runtime Environment 6, DH 1024 , , SSL. , DH 1024 .}
^{( ) password_encryption off plain. UNENCRYPTED CREATE/ALTER USER ... PASSWORD. --unencrypted createuser. , , . password_encryption md5.}
^{min_parallel_table_scan_size min_parallel_index_scan_size ( , ) min_parallel_relation_size, .}			^{«» .}
^{shared_preload_libraries ( ) , SQL- .}
^{sql_inheritance ( ) , , . SQL , , PostgreSQL 7.1.}
^{/ ( ) configure --disable-integer-datetimes . , PostgreSQL 8.3.}			^{: rpm, deb, etc.}
^{- 1.0 ( ) PostgreSQL 6.3.}
^{contrib/tsearch2 ( ) , PostgreSQL 8.3.}
^{createlang droplang ( ) PostgreSQL 9.1. CREATE EXTENSION DROP EXTENSION.}
^{, , SELECT ( ) , , SELECT, , LATERAL FROM. , . , NULL. , , , . , , , CASE COALESCE.}			^{set-returning function (.. , ) FROM. , - SELECT. - , . .}
^{UPDATE ... SET (_) = _ ( ) _ ROW; . _ , _ ROW, , . _.* _ , _.}			^{These are the usual rules for row_constructor. It is only unusual to use it in UPDATEthis way. In practice, I have not seen such cases, although such syntax appeared in the documentation back in PostgreSQL 8.2. In version 10, it seems that this syntax was brought to a standard that is used in other versions of PostgreSQL. No matter how strange it may seem, it is possible to insert such a piece of code.}

Interesting features

Publish / Subscribe Logical Replication

A useful feature that can be used to upgrade with minimal downtime.

Declarative table partitioning

New declarative syntax simplifies table partitioning.

Improving Query Parallelization

Initially 9.6 introduced support for parallel execution of large queries. But it concerned only scans, joins and aggregations.

In 10.0, in turn, the possibility of parallel execution appeared:

B-Tree index scan
Bitmap scan
Merge joins
Uncorrelated subqueries

Stronger password protection using SCRAM-SHA-256

May be incompatible with some non-standard (non-libpq) PostgreSQL connection libraries.

PostgreSQL 10.0 -> 11.0

Feature	Risk	Whom to pay attention to	Comment
^{Correct handling of expressions with relative paths in xmltable(), xpath()and other functions that work with XML (Markus Winand) According to the SQL standard, relative paths should be considered from the XML document node, and not from the root node, as these functions used to do.}			^{SQL. , XML. , .}
^{pg_dump , ( ) , GRANT/REVOKE ALTER DATABASE SET, pg_dumpall. pg_dump --create pg_restore --create . pg_dumpall -g , . pg_dumpall ( -g) . pg_dump pg_restore --create / ; . pg_dumpall , , , . CREATE DATABASE , . pg_dumpall --clean postgres template1, , .}			^{. , .. . . , pg_dump/pg_restore --create . , , .}
^{( , ) , .}
^{adminpack pg_file_read(), pg_file_length() pg_logfile_rotate() ( ) . adminpack , ALTER EXTENSION ... UPDATE.}
^{replacement_sort_tuples ( ) , .}

text ^@ text SP-GiST ( )

LIKE '%' btree, .

Search by prefix is quite common. For this, we added a special operator with support for special indexes. And what is most interesting, the official documentation does not say a word about this operator. Instead, a function starts_withis mentioned - an internal function on which this operator is based, but its use does not provide acceleration using an index.

Reducing an expression = to an expression IS NOT NULLwhere they are equivalent (Tom Lane)

This improves the selectivity score.

Oddly enough, we have met such a design in practice. Apparently, such cases are not uncommon.

PostgreSQL 11.0 -> 12.0

Feature	Risk	Whom to pay attention to	Comment
^{substring(), SQL, , «» ( ) , , , ; , %#"aa*#"% , a.}			^{, , SQL. - substring() , , . , .}
^{recovery.conf postgresql.conf ( , , -, ) recovery.conf , , . recovery.signal standby.signal. trigger_file promote_trigger_file, standby_mode .}
^{recovery_target* ( ) , recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time recovery_target_xid. , . , , .}
^{( ) recovery_target_timeline latest. current.}
^{pg_verify_checksums pg_checksums ( )}
^{pg_restore -f — ( ) , , .}
^{\pset format psql ( ) , , \pset format a aligned; asciidoc, .}			^{\pset psql. , , psql \pset.}
^{btree 8 ( ) REINDEX , pg_upgrade, .}			^{. , . , . pg_upgrade.}
^{( ) , dynamic_shared_memory_type none.}
^(CTE),			^{. , , , , . 12- CTE . , , , . , . , pg_stat_statments . , template1.}

btree ( , )

/ , , . .

, pg_upgrade.

Another argument is not in favor pg_upgrade.

Conclusion

This checklist is our extract from the official PostgreSQL resources. In practice, depending on the development approaches, the criticality of certain compatibility for you may be different. I would be glad if the article will save someone from data loss or reduce the labor costs of updating PostgreSQL.

PS

Thanks to Oleg Samoilov (splarv) for help in preparing the article.

PostgreSQL version update, or How not to drop the database when updating?