I suggest that you familiarize yourself with the decoding of the 2018 report by Andrei Salnikov, "The practice of updating versions of PostgreSQL"
For the most part, system administrators and the DBA are afraid of making major updates to the database versions (RDBMS) as a fire, especially if this database is in operation and has a fairly high load. The main reason for this is some database downtime, which is always implied in the planning of such work.
In practice, this kind of upgrade takes quite a long time and often administrators with little experience in such operations have to roll back to the old version of the databases because of the rather trivial errors that could have been avoided at the preparation stage.
In Data Egret, we have accumulated vast experience in conducting major PostgreSQL upgrades in projects where there is no room for error. I will share my experience and talk about the next steps in the process: how to prepare for PostgreSQL upgrade? What needs to be done at the preparation stage? How to plan the sequence of actions for upgrade itself? how to carry out upgrade procedure successfully, without returning to the previous version of the database? how to minimize or even avoid downtime of the entire system during upgrade? What steps do I need to complete after successfully upgrading PostgreSQL? I will also talk about the two most popular PostgreSQL upgrade procedures - pg_upgrade and pg_dump / pg_restore, the pros and cons of each method and tell about all the typical problems at all stages of this procedure, and how to avoid them.
The report will be interesting both for beginners and those DBAs who have long worked with PostgreSQL, but want to learn more about how to properly plan and carry out upgrade as painlessly as possible.
Hello! I work at Data Egret. We are committed to supporting PostgreSQL servers and providing PostgreSQL consulting services. And practice has shown that very few people update the database. They launched the project, install the current version at that time and are still working.
The report will consist of three parts. The first is an introductory one in order to arrive at a common terminology. The second is about minor updates. And the third one will be about major.
The purpose of the report is to answer questions.
- ? , Postgres . , - . , . , . .
- , .
- , , .
- , : « ?». .
- , . , production .
, Postgres.
, , . 10 . , , , – .
10- . , . – , – .
?
. , – . 9.6 , , , . . .
10- . , .
. . , .
. .
– , . 6. . , . . . 9.2. , , 9.2 . 9.2, .
: .
. , , production :
- release notes. ? , release notes, release notes , . , . - . , , . , 10- , -. , , .
- ? . , . release notes, . , - . , Postgres , , . , Postgres, .
- - , , , , production .
- , – , . — , , , . .
, , , .
. .
, . , Postgres. Postgres.
:
- PostgreSQL. , - , , , Postgres . . . , . Ubuntu, , , . start.conf. . .
- PostgreSQL . , , common- . .
- – checkpoint. checkpoint? , . , , , . , , , , 250 GB, . checkpoint, Postgres.
- ? pgbouncer, , . pgbouncer, pgbouncer . pgbouncer’ – , - . latency . . . , pgbouncer, . checkpoint, pgbouncer, .
- , . . , .
- , extensions, - extensions. – extensions . . . . . .
- ( , release notes) release notes. – - . , 9.6.1 9.6.6, , release notes 9.6.2 – 9.6.6. - , , , .
- standby , , . , 14- , 2.3 . . , . .
, release notes.
Postgres.org. 9.6.2. , .
? , , . , . .
. . . , , release notes. .
, . . , , 9.6.5. 9.6.1. , . - . , – . – .
, . . . bash- , . alter extension, update .
?
- . . , , .
- . , , , release notes.
- . , - , . . . .
- , . . . , checkpoint, 30 .
– .
. , . .
- Pg_dump restore – . , . - .
- Pg_upgrade – , , , 95 % , . .
- .
.
pg_dump ?
- . , . 9.5, , 10. Ubuntu , RedHat – Postgres. , .
- locale, .
- Postgres, , , , , .
- , pg_dumop. PostgreSQL. ? pg_dump , , , . , , . . PostgreSQL, . . , . , .
- . pg_dump, . . , .
- . .
- , , , PostgreSQL.
, , , .
pg_dump:
- . – . .
- . .
- . : , , . , .
- , , . .
- . , . . SSD-, SSD- . , , . .
- . , . , . , , . , , . .
, pg_upgrade.
- Pg_upgrade . , . . , . .
- . , , .
- PostgreSQL. -, .
- . .
, pg_upgrade. : , , .
, pg_upgrade, , , . , , . .
, – pg_upgrade dump restore , pg_dump, restore , , , . .
. , , 8.4. Postgres .
, (, 9.0 10) -. , , . . , .
, production. . . , – .
- PostgreSQL.
- locace. .
- pg_upgrade. «check». Check , . , - , , , , . , . , .
- Pg_dumpall — schema-only. check . pg_dumpall – schema-only, . , . , , check , dump .
- extensions . – PostGIS, PostGIS Postgres. extension Postgres, Postgres. changelog – , pg_upgrade , . dump restore .
, , , .
locale, pg_dumpall only, restore.
. , pgbouncer , . . , .
checkpoints, , .
pg_upgrade. , . . 45 . , 45 , . 15 , . .
, . hard links. .
PostgreSQL. . PostgreSQL? . , pg_upgrade .
.
. 10 . , .
:
pg_upgrade . , : , 10 .
, , .
9.5 . , .
. . , , . , vacuumdb 1, 10 . , . - . - . , , .
. , . - . , , . , , .
.
- Pg_upgrade extensions. , .
- release notes .
- , , extensions – alter extension EXTENSION_NAME update. . pg_stat_statements, - . pg_upgrade, pg_stat_statements. . .
– ? :
- - - .
- , , , , , . . , , .
- ? Postgres. . . , , , 10 .
- PostgreSQL.
- pg_basebackup . .
- PostgreSQL.
- , rsync, . , , , . rsync , .
. :
9.4 . . , 10 11 .
, . Slony-l, Londiste, Bucardo . . .
?
- PostgreSQL.
- - Postgres Postgres.
- , , . .
- , . . , Postgres. , , . . . , .
- Postgres, , .
- Postgres, . , , . - .
?
- , . - .
- , . . . . , .
- sequences, -.
- DDL, . DDL , , . , .
- , , .
, . pg_upgrade , .
? , 3 TB, 2 SSD RAID 3 TB. . .
. .
, , 99 - 9- . 15 . 15 . . - .
, pg_upgrade. . .
.
! pgbouncer , , , , , . ?
, SSD . SSD , . . . . , , , , . , , . , Postgres. , .
pg_worm pg_hibernate, ?
, , .
! pg_upgrade?
, . , . , . , , - , . , , . , .
rsync -?
rsync? Rsync . , , , , . ? pg_upgrate . , . – pg_start_backup. rsync . - , rsync , . , , , tablespace HDD, rsync , . – , . , . – pg_basebackup.
! , …
This applies to minor updates.