👋🏿 🤘 🕍 Mise à jour de la version de PostgreSQL, ou Comment ne pas supprimer la base de données lors de la mise à jour? 👆🏽 🧘 👨🏿‍🌾

Nous avons décidé de partager un mémo (liste de contrôle) de notre équipe de mise à niveau PostgreSQL. Dans ce document, nous avons pris comme base les fonctionnalités présentées dans la publication de la documentation officielle de PostgreSQL, effectué une analyse des problèmes de compatibilité et qui devrait prêter attention aux fonctionnalités mises à jour.

Je m'appelle Alexander Kotsyuruba, je gère le développement des services internes chez DomClick.ru.

L'article est divisé en parties avec des titres. Chaque titre fait référence à la mise à niveau de PostgreSQL d'une version à une autre. Dans chaque chapitre, nous avons mis en évidence:

Fonctionnalité
Incompatibilité potentielle de risque
- Interruption de l'application
- Baisse des performances
Qui rechercher lors de la mise à jour
- Administrateur du système
- Développeur
Commentaire

Alors, commençons.

Mettre à jour les méthodes

pg_upgradeCe n'est pas le moyen le plus fiable de mettre à jour PostreSQL. Par exemple, vous pourriez obtenir une erreur lors de l'exécution de REINDEX à partir d'une version précédente de PostgreSQL.
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 . . , , , , .

Résout le problème de «gonfler» la base de données en raison de «l'ouverture d'une transaction et de partir en vacances»; mises à jour irréfléchies qui prennent des heures; long selectsur maître et esclave. C'est l'une des deux options, grâce auxquelles il sera possible de résoudre le problème, en raison de laquelle ils ne peuvent pas surcharger de longues requêtes du maître à l'esclave.

PostgreSQL 9.6 -> 10.0

Fonctionnalité	Risque	À qui prêter attention	Commentaire
^{Après la mise à niveau depuis pg_upgraden'importe quelle version majeure précédente de PostgreSQL, les index de hachage doivent être reconstruits (Mithun Sai, Robert Haas, Amit Kapila). Cette exigence est due à des améliorations significatives du mécanisme des index de hachage. Pour faciliter la tâche de réindexation, il pg_upgradecréera un script d'aide.}	^{Interruption de l'application}	^{Administrateur du système}	^{Un autre argument contre 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, , . _.* _ , _.}			^{Ce sont les règles habituelles pour row_constructor. Il est inhabituel de l'utiliser de UPDATEcette manière. En pratique, je n'ai pas vu de tels cas, bien qu'une telle syntaxe soit apparue dans la documentation de PostgreSQL 8.2. Dans la version 10, il semble que cette syntaxe ait été amenée à un standard qui est utilisé dans d'autres versions de PostgreSQL. Aussi étrange que cela puisse paraître, il est possible d'insérer un tel morceau de code.}

Caractéristiques intéressantes

Publier / Souscrire la réplication logique

Une fonctionnalité utile qui peut être utilisée pour mettre à niveau avec un temps d'arrêt minimal.

Partitionnement déclaratif des tables

La nouvelle syntaxe déclarative simplifie le partitionnement des tables.

Amélioration de la parallélisation des requêtes

Au départ, la version 9.6 a introduit la prise en charge de l'exécution parallèle de requêtes volumineuses. Mais cela ne concernait que les scans, les jointures et les agrégations.

Dans 10.0, à son tour, la possibilité d'une exécution parallèle est apparue:

Balayage d'index B-Tree
Analyse Bitmap
Fusionner les jointures
Sous-requêtes non corrélées

Protection renforcée par mot de passe avec SCRAM-SHA-256

Peut être incompatible avec certaines bibliothèques de connexion PostgreSQL non standard (non-libpq).

PostgreSQL 10.0 -> 11.0

Fonctionnalité	Risque	À qui prêter attention	Commentaire
^{La manipulation correcte des expressions avec des chemins relatifs dans xmltable(), xpath()et d' autres fonctions que le travail avec XML (Markus Winand) Selon la norme SQL, les chemins relatifs doit être considéré à partir du nœud de document XML, et non à partir du nœud racine, comme ces fonctions oeuvraient .}			^{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, .

La recherche par préfixe est assez courante. Pour cela, nous avons ajouté un opérateur spécial prenant en charge les index spéciaux. Et ce qui est le plus intéressant, la documentation officielle ne dit pas un mot sur cet opérateur. Au lieu de cela, une fonction starts_withest mentionnée - une fonction interne sur laquelle cet opérateur est basé, mais son utilisation ne fournit pas d'accélération à l'aide d'un index.

Réduire une expression = à une expression IS NOT NULLoù elles sont équivalentes (Tom Lane)

Cela améliore le score de sélectivité.

Curieusement, nous avons rencontré une telle conception dans la pratique. Apparemment, de tels cas ne sont pas rares.

PostgreSQL 11.0 -> 12.0

Fonctionnalité	Risque	À qui prêter attention	Commentaire
^{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.

Un autre argument n'est pas en faveur pg_upgrade.

Conclusion

Cette liste de contrôle est notre extrait des ressources officielles de PostgreSQL. En pratique, selon les approches de développement, la criticité d'une certaine compatibilité pour vous peut être différente. Je serais ravi que l'article sauve quelqu'un de la perte de données ou réduise les coûts de main-d'œuvre de la mise à jour de PostgreSQL.

PS

Merci à Oleg Samoilov (Splarv) pour vous aider à préparer l'article.

Mise à jour de la version de PostgreSQL, ou Comment ne pas supprimer la base de données lors de la mise à jour?