- 快召唤伙伴们来围观吧
- 微博 QQ QQ空间 贴吧
- 文档嵌入链接
- 复制
- 微信扫一扫分享
- 已成功复制到剪贴板
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?