PostgresOpen 12 September 2019 1 Speaker Julien Riou DBA since - PowerPoint PPT Presentation

https://knowyourmeme.com/memes/all-the-things PostgresOpen – 12 September 2019 1

Speaker • Julien Riou • DBA since 2012 • Tech lead in the databases team at OVH since 2015 • pgterminate @ github Speaker PostgresOpen – 12 September 2019 2

Overview • Definitions • Context • Updates • Upgrades • Conclusion • What’s next? Overview PostgresOpen – 12 September 2019 3

Definitions PostgresOpen – 12 September 2019 4

Versioning policy • Starting from version 10 11.4 Major version Minor version Definitions PostgresOpen – 12 September 2019 5

Versioning policy • Before version 10 9.6.14 Major version Minor version Definitions PostgresOpen – 12 September 2019 6

Versioning policy • Major versions • Minor versions – Released about once a year – Released at least every 3 months – Includes new features – Includes bug and security fixes – Supported for 5 years – Critical fixes are released as soon as possible Definitions PostgresOpen – 12 September 2019 7

Definitions Update Installing a newer minor version of PostgreSQL ”Minor upgrade” accepted too Definitions PostgresOpen – 12 September 2019 8

Definitions Upgrade Installing a newer major version of PostgreSQL ”Major upgrade” accepted too Definitions PostgresOpen – 12 September 2019 9

Context PostgresOpen – 12 September 2019 10

Context PostgresOpen – 12 September 2019 11

Products Cloud Platform Baremetal Kubernetes VPS Logs & Metrics Data Platforms Public cloud Databases Private cloud Big data Storage AI & Machine Learning Web hosting Telecom Domain names Internet offers Website hosting Telephony E-mail solutions SMS / Fax SSL / CDN VDI Office & Microsoft solutions OverTheBox Context PostgresOpen – 12 September 2019 12

Perimeter Internal databases 60 3000 700 400 Clusters Applications Users Databases Context PostgresOpen – 12 September 2019 13

Cluster example • MySQL • PostgreSQL Context PostgresOpen – 12 September 2019 14

Updates PostgresOpen – 12 September 2019 15

Recommendations • “We always recommend that all users run the latest available minor release for whatever major version is in use.” • “For minor releases, the community considers not upgrading to be riskier than upgrading.” https://www.postgresql.org/support/versioning/ Updates PostgresOpen – 12 September 2019 16

https://knowyourmeme.com/memes/all-the-things Updates PostgresOpen – 12 September 2019 17

Method 1. Stop the service 2. Install new binaries 3. Start the service Updates PostgresOpen – 12 September 2019 18

Attention points • Always read the changelog • Downtime – Can be minimized by using pgbouncer and PAUSE/RESUME commands • Write intensive clusters – Run CHECKPOINT before stopping the service – Prepare for a switchover for extreme case • Patroni – Put the cluster on maintenance mode to avoid failovers Updates PostgresOpen – 12 September 2019 19

Initial state Updates PostgresOpen – 12 September 2019 20

Clustershell • “Event -driven open source Python library, designed to run local or distant commands in parallel on server farms or on large Linux clusters” http://cea-hpc.github.io/clustershell/ • Binaries – clush – nodeset • Python API Updates PostgresOpen – 12 September 2019 21

Clustershell • nodeset $ nodeset -ll @all node[1-6] @cluster1 node[1-3] @cluster2 node[4-6] @node node[1-2,4-5] @backup node[3,6] Updates PostgresOpen – 12 September 2019 22

Clustershell • clush $ clush -bw @all $ clush -bw @cluster1\&@backup $ clush -bw @cluster1,@cluster2 Updates PostgresOpen – 12 September 2019 23

Clustershell • clush clush> apt-get update Clush> apt-get upgrade Updates PostgresOpen – 12 September 2019 24

Clustershell • Backups first $ clush -bw @backup Updates PostgresOpen – 12 September 2019 25

Clustershell • Backups first $ clush -bw @backup Updates PostgresOpen – 12 September 2019 26

Clustershell • Backups first $ clush -bw @backup Updates PostgresOpen – 12 September 2019 27

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 28

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 29

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 30

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 31

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 32

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 33

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 34

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 35

Clustershell • Then nodes one node at a time (fanout) $ clush -f 1 -bw @node Updates PostgresOpen – 12 September 2019 36

Final state Updates PostgresOpen – 12 September 2019 37

Limitations • clush is great for one-shot human simple operations • Requires development investment to implement complex automation • At our scale, we use our own automation system – Mostly open: PostgreSQL, Flask, Ansible, Celery, … – And some internal systems Updates PostgresOpen – 12 September 2019 38

Upgrades PostgresOpen – 12 September 2019 39

Why? • Support • New features – Materialized views – Limited to 5 years – JSON • Better performance – Logical decoding – Upsert – Parallelism – SCRAM – Optimizations – And more… Upgrades PostgresOpen – 12 September 2019 40

https://knowyourmeme.com/memes/all-the-things Upgrades PostgresOpen – 12 September 2019 41

Method Upgrades PostgresOpen – 12 September 2019 42

Method Upgrades PostgresOpen – 12 September 2019 43

Method Upgrades PostgresOpen – 12 September 2019 44

Method Upgrades PostgresOpen – 12 September 2019 45

Method Upgrades PostgresOpen – 12 September 2019 46

Method Upgrades PostgresOpen – 12 September 2019 47

“Replication” methods • Application • pg_dump / pg_restore • pg_upgrade • Logical replication with pglogical Upgrades PostgresOpen – 12 September 2019 48

“Replication” methods Application Upgrades PostgresOpen – 12 September 2019 49

Application 1. Write objects to both clusters 2. Copy old objects to new cluster 3. Switchover Upgrades PostgresOpen – 12 September 2019 50

Application Pros Cons Developers are autonomous Different object management for too much teams No downtime Requires a single endpoint or inconsistencies RDBMS independent Not a developer priority Upgrades PostgresOpen – 12 September 2019 51

Application • Conclusion https://github.com/googlefonts/noto-emoji/blob/master/svg/emoji_u1f44e.svg Upgrades PostgresOpen – 12 September 2019 52

“Replication” methods pg_dump / pg_restore Upgrades PostgresOpen – 12 September 2019 53

pg_dump / pg_restore 1. Set old cluster to read-only mode 2. Dump old cluster with pg_dump 3. Restore on new cluster with pg_restore 4. Switchover Upgrades PostgresOpen – 12 September 2019 54

pg_dump / pg_restore Pros Cons DBA team is autonomous Extended period of downtime for large databases Easy to setup Wipe table and index bloat Upgrades PostgresOpen – 12 September 2019 55

pg_dump / pg_restore • Conclusion https://github.com/googlefonts/noto-emoji/blob/master/svg/emoji_u1f44d.svg Upgrades PostgresOpen – 12 September 2019 56

“Replication” methods pg_upgrade Upgrades PostgresOpen – 12 September 2019 57

pg_upgrade 1. Install both versions on new cluster 2. Setup streaming replication from old cluster to new cluster 3. Set old cluster to read-only mode 4. Run pg_upgrade on new cluster with hardlinks 5. Update statistics in stage on new cluster 6. Switchover Upgrades PostgresOpen – 12 September 2019 58

pg_upgrade Upgrades PostgresOpen – 12 September 2019 59

pg_upgrade Upgrades PostgresOpen – 12 September 2019 60

pg_upgrade Upgrades PostgresOpen – 12 September 2019 61

pg_upgrade Pros Cons DBA team is autonomous Requires multiple versions of binaries on the same host Very short downtime Rebuild streaming replication to have up-to-date data Easy to setup (the first time) Upgrades PostgresOpen – 12 September 2019 62

A word on statistics Upgrades PostgresOpen – 12 September 2019 63

A word on statistics https://knowyourmeme.com/memes/reality-hits-you-hard-bro Upgrades PostgresOpen – 12 September 2019 64