Upgrade or Migrate Your PostgreSQL database through a lowest possible downtime

PostgreSQL的性能因不同版本而异。每一个新版本都提供了附加的特性和性能改进,帮助PostgreSQL不断增长的采用。所有这些持续不断的发展和改进促使我们计划升级PostgreSQL环境,但这并不总是一项简单的任务。在升级PostgreSQL时,一些常见的问题是延长停机时间、将使用触发器分区的表转换为声明性分区、搜索最安全的升级选项以及升级过程本身所需的工作。在本文中,我们将讨论各种可用的选项,这些选项可以帮助您以最好的方式升级PostgreSQL服务器。
这次谈话包括:
1。最近的PostgreSQL版本(包括PostgreSQL 11和10)中最显著的性能改进。
2。自PostgreSQL 9.1以来,每一个新版本都实现了的特性摘要。
3。升级PostgreSQL服务器之前需要考虑什么?
4。有哪些选项可以帮助您升级PostgreSQL服务器。
5。有哪些解决方案可以最大限度地减少升级过程中的停机时间。
6。将PostgreSQL升级到10或11时需要特别考虑的参数列表。

展开查看详情

1. Upgrade or Migrate Your PostgreSQL Database With The Least Possible Downtime Avinash Vallarapu Percona

2.Agenda ● Upgrade checklist ● Methods available to upgrade with and without downtime ● Demonstration

3.Pre-Upgrade Checklist ● Plan your hardware specifications ● Application to DB connectivity ● High Availability ● Performance testing ● Backup strategy ● Plan your postgresql.conf parameters ● Install all the required tools and extensions in advance

4.Methods Available to Upgrade Legacy PostgreSQL Using pg_dumpall Using pg_dump/pg_restore Using logical replication Using Slony-I Using pg_upgrade

5.Downtime? ● May involve a huge downtime ○ pg_dumpall ○ pg_dump and pg_restore ● May not involve a huge downtime ○ Logical replication or pg_logical ○ Slony-I ○ pg_upgrade with hard links.

6.pg_dumpall ● Text-format dump of whole database cluster ● Single thread ● Single step approach ● May require double the space if it is an in-place upgrade. ● Removes table bloat ● A complete downtime for business (write-traffic)

7.pg_dump/pg_restore with pg_dumpall ● pg_dump and pg_restore using parallel jobs ● Requires pg_dumpall for globals ● May require double the space if it is an in-place upgrade ● Removes table bloat ● Faster when compared to an upgrade with pg_dumpall only ● Involves downtime for business (write traffic).

8.Slony - Overview ● Logical replication (publisher-subscriber) ● Primary key should be defined on each replicated table ● Trigger-based, additional C daemons (slon) are required ● Any PostgreSQL versions from and to 8.4 ⇔ 11 ○ Useful for both upgrades and downgrades ● No support for: ○ DDL (CREATE/DROP/ALTER) - requires application change ○ BLOB (binary data supported, but not OID blobs) ● Application should be switched manually to subscriber

9.Slony - Additional features ● Monitoring and replication health checks ● Automation using altperl ● Ability to merge replication sets

10.Slony - Migration ● Migration by preserving existing replication chain: ○ Stop write transactions from the application and ensure no pending transactions ○ Use LOCK SET to lock the replication set against client updates ○ Use MOVE SET move replication set to new database which shifts the origin ○ Point the application to the new database ● Migration without preserving: ○ UNSUBSCRIBE SET which stops the subscriber from replicating the set ■ Table contents will be left and original triggers/rules/constraints will be restored

11.Logical replication and pglogical ● Uses publisher and subscriber model ● Logical Replication and Logical decoding ○ Replication between PostgreSQL 10.x and 11.x ● pglogical (extension) ○ Replication between PostgreSQL 9.4.x and PostgreSQL 11.x ● Requires primary key for tables to be replicated ● Switchover application to Subscriber upon replication ● May be a few minutes (or seconds) of downtime

12.pg_upgrade ● Time consuming when not using hard links ○ Similar to upgrade using pg_dump/pg_restore ○ Removes bloat from tables ○ Can work between 2 different file systems or servers ● Takes a few seconds when using hard links ○ Works on the same file system in the same server (not applicable for upgrade to a remote server). ○ No changes to the amount of bloat or fragmented space. ○ Does not require an application failover like pglogical or slony ○ May be a few seconds or minutes of downtime

13.Thank You to Our Sponsors

14.Rate My Session 14

15.Any Questions?