Upgrading or migrating your legacy postgresql to never postgres versions

本次网络研讨会从PostgreSQL内置的解决方案列表开始,帮助我们以最少的停机时间升级PostgreSQL的旧版本。还将讨论选择这种方法的优点。您将注意到每个解决方案的先决条件列表,从而减少了可能的错误范围。从旧版本的PostgreSQL服务器升级时,将停机时间减到最少是很重要的。因此,我们将提供3个开源解决方案,帮助我们最小化或完全避免停机。
此外,我们将发布一系列5篇博客文章,帮助我们了解可用于执行PostgreSQL升级的解决方案。我们的演示将展示将一组PostgreSQL服务器升级到最新可用版本的完整过程。此外,我们将展示我们使用的每种方法的优缺点。
本次网络研讨会涉及的主题包括:
1.使用pg_dump/pg_restore进行postgresql升级(有停机时间)
2.PostgreSQL使用pg_dumpall升级(有停机时间)
3.使用slony从旧的PostgreSQL版本连续复制到新的版本。
4.使用逻辑复制在主要PostgreSQL版本之间进行复制
5.以最少的停机时间快速升级旧的PostgreSQL。
我们将为您讲解45分钟的方法,并演示一些您可能会发现在数据库环境中有用的方法。我们将见证使用这些方法执行升级是多么简单和快速。

展开查看详情

1. Upgrading or Migrating Your Legacy PostgreSQL to Newer PostgreSQL Versions Avinash Vallarapu, Jobin Augustine, Fernando Laudares and Nickolay Ilhanien PostgreSQL Webinar, April 2019 1 ●© 2018 Percona

2.Agenda ● Upgrade checklist ● Methods available to upgrade with and without downtime ● Demo 2 © 2019 Percona

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 3 © 2019 Percona

4.Methods available to upgrade a legacy PostgreSQL ❏ Using pg_dumpall ❏ Using pg_dump/pg_restore ❏ Using logical replication ❏ Using Slony-I ❏ Using pg_upgrade 4 © 2019 Percona

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. 5 © 2019 Percona

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). 6 © 2019 Percona

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). 7 © 2019 Percona

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 8 © 2019 Percona

9.Slony - Additional features ❏ Monitoring and replication health checks. ❏ Automation using altperl. ❏ Ability to merge replication sets. 9 © 2019 Percona

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. 10 © 2019 Percona

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. 11 © 2019 Percona

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. 12 © 2019 Percona

13. Questions ??? 13 © 2019 Percona