👩‍❤️‍👨 👨🏿‍⚖️ ➕ Atualização da versão do PostgreSQL, ou Como não descartar o banco de dados ao atualizar? 👨🏾‍⚕️ 🍞 🆑

Decidimos compartilhar nossa lista de verificação da equipe de atualização do PostgreSQL. Nele, tomamos como base as funcionalidades apresentadas no lançamento da documentação oficial do PostgreSQL, realizamos uma análise para questões de compatibilidade e quem deve ficar atento à funcionalidade atualizada.

Meu nome é Alexander Kotsyuruba, gerencio o desenvolvimento de serviços internos na DomClick.ru.

O artigo está dividido em partes com títulos. Cada título se refere à atualização do PostgreSQL de uma versão para outra. Em cada capítulo, destacamos:

Característica
Incompatibilidade potencial de risco
- Interrupção do aplicativo
- Queda no desempenho
Quem procurar ao atualizar
- Administrador do sistema
- Desenvolvedor
Comente

Então, vamos começar.

Métodos de atualização

pg_upgradeNão é a maneira mais confiável de atualizar o PostreSQL. Por exemplo, você pode obter um erro ao REINDEX de uma versão anterior do 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 . . , , , , .

Resolve o problema de "inchar" o banco de dados devido a "abrir uma transação e sair de férias"; atualizações precipitadas que levam horas; muito tempo selectem mestre e escravo. Esta é uma de duas opções, graças à qual será possível resolver o problema, devido ao qual não podem recarregar pedidos longos do mestre para o escravo.

PostgreSQL 9.6 -> 10.0

Característica	Risco	Quem deve prestar atenção	Comente
^{Após a atualização de pg_upgradequalquer versão principal anterior do PostgreSQL, os índices hash devem ser reconstruídos (Mithun Sai, Robert Haas, Amit Kapila) Este requisito é devido a melhorias significativas nos índices hash. Para facilitar a tarefa de reindexação, ele pg_upgradecriará um script auxiliar.}	^{Interrupção do aplicativo}	^{Administrador do sistema}	^{Outro argumento contra 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, , . _.* _ , _.}			^{Estas são as regras usuais para row_constructor. É incomum usá-lo UPDATEdessa forma. Na prática, não vi tais casos, embora essa sintaxe tenha aparecido na documentação do PostgreSQL 8.2. Na versão 10, parece que essa sintaxe foi trazida para um padrão que é usado em outras versões do PostgreSQL. Por mais estranho que pareça, é possível inserir um código assim.}

Características interessantes

Replicação lógica por esquema de publicação / assinatura

Um recurso útil que pode ser usado para atualizar com o mínimo de inatividade.

Particionamento declarativo de tabela

A nova sintaxe declarativa simplifica o particionamento de tabelas.

Melhorando a paralelização de consultas

Inicialmente, o 9.6 introduziu suporte para execução paralela de grandes consultas. Mas se referia apenas a varreduras, associações e agregações.

No 10.0, por sua vez, surgiu a possibilidade de execução paralela:

Varredura de índice B-Tree
Verificação de bitmap
Unir junções
Subconsultas não correlacionadas

Proteção de senha mais forte usando SCRAM-SHA-256

Pode ser incompatível com algumas bibliotecas de conexão PostgreSQL não padrão (não libpq).

PostgreSQL 10.0 -> 11.0

Característica	Risco	Quem deve prestar atenção	Comente
^{Manuseio correto de expressões com caminhos relativos no xmltable(), xpath()e outras funções que trabalham com XML (Markus Winand) De acordo com o padrão SQL, caminhos relativos devem ser considerados a partir do nó de documento XML, e não a partir do nó raiz, como essas funções costumava fazer.}			^{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, .

A pesquisa por prefixo é bastante comum. Para isso, adicionamos um operador especial com suporte para índices especiais. E o que é mais interessante, a documentação oficial não diz uma palavra sobre este operador. Em vez disso, uma função starts_withé mencionada - uma função interna na qual esse operador se baseia, mas seu uso não fornece aceleração usando um índice.

Reduzir uma expressão = a uma expressão IS NOT NULLonde são equivalentes (Tom Lane)

Isso melhora a pontuação de seletividade.

Curiosamente, encontramos esse tipo de design na prática. Aparentemente, esses casos não são incomuns.

PostgreSQL 11.0 -> 12.0

Característica	Risco	Quem deve prestar atenção	Comente
^{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.

Outro argumento não é a favor pg_upgrade.

Conclusão

Esta lista de verificação é nosso extrato dos recursos oficiais do PostgreSQL. Na prática, dependendo das abordagens de desenvolvimento, a criticidade de certa compatibilidade para você pode ser diferente. Eu ficaria feliz se o artigo salvasse alguém da perda de dados ou reduzisse os custos de mão de obra de atualização do PostgreSQL.

PS

Obrigado a Oleg Samoilov (splarv) para obter ajuda na preparação do artigo.

Atualização da versão do PostgreSQL, ou Como não descartar o banco de dados ao atualizar?