跨PostgreSQL大版本複製怎麼做? | 邏輯複製

• 2019 年 11 月 21 日
• 筆記

作者

Avinash Vallarapu，Fernando Laudares Camargos，Nickolay Ihalainen，Percona公司工程師,Jobin Augustine :OpenSCG工作了2年，在戴爾做了10年的數據庫專家。

譯者簡介

崔鵬，任職于海能達通信股份有限公司哈爾濱平台中心,數據庫開發高級工程師,致力於postgresql數據庫在專網通信領域、公共安全領域的應用與推廣,個人興趣主要集中在:分佈式數據庫系統設計、高並發高可用數據庫架構設計與開源數據庫的源碼研究。

校對者簡介

Qinghui.Guo， DBA 負責公司Cloud DB的維護，痴迷於開源架構解決方案，是PG忠實粉絲，現致力於PG分享和推廣。

當需要升級PostgreSQL時，可以使用多種方法。為了避免應用程序停機，不是所有升級postgres的方法都適合,如果避免停機是必須的，那麼可以考慮使用複製作為升級方法，並且根據方案，可以選擇使用邏輯複製或物理（流）複製來處理此任務。我們來看看PostgreSQL中邏輯和物理複製之間的區別。然後，我們將更詳細地探討如何使用邏輯複製完成升級，並通過這樣做，避免應用程序停機。在後面的文章中，我們將研究物理複製。我們已經在之前的系列文章中討論了幾種可用於升級或遷移舊版PostgreSQL到新版PostgreSQL版本的方法,使用pg_dumpall進行PostgreSQL升級和使用pg_dump/pg_restore進行PostgreSQL升級。但是,這兩種方法都涉及到應用程序的停機時間。

1 邏輯複製的類型

這裡我們介紹兩種您可以實現的複製類型：

1.使用內置邏輯複製在PostgreSQL 10和11版本之間進行複製。

2.使用pglogical的擴展在PostgreSQL 11和PostgreSQL 9.4或低於PostgreSQL11版本之間進行複製。

我們可能會選擇通過複製作為升級方法，以最大限度地減少應用程序的停機時間。一旦所有數據都被複制到最新版本的另一個PostgreSQL服務器上，您只需將應用程序切換到新服務器，停機時間最短,但這當然取決於應用程序的複雜性。

PostgreSQL中的邏輯複製允許用戶執行表級別的複製，並允許備庫可以寫入。而PostgreSQL中的物理複製是塊級複製。在這種情況下，主服務器中的每個數據庫都將複製到備用數據庫，並且備庫以只讀方式打開並不可以寫入，我們將物理複製稱為流複製。

通過邏輯複製，備用數據庫可以從多個主服務器啟用複製。在需要將數據從多個PostgreSQL數據庫（OLTP）複製到單個PostgreSQL服務器以進行報表和數據倉庫業務的情況下，這麼做會對業務有所幫助。

邏輯複製的最大優勢之一是邏輯複製允許我們將舊版本的PostgreSQL的DML複製到更高版本。物理流複製只有在主數據庫版本和備用數據庫版本都是相同的時才有效。最佳的實施方案,是主庫和備庫使用相同的版本。

2 PostgreSQL 10和11版本之間的複製

從PostgreSQL 10開始，默認情況下PostgreSQL支持邏輯複製。因此，您可以輕鬆地將PostgreSQL 10數據庫複製到PostgreSQL 11.邏輯複製使用發佈和訂閱模式。發送更改的節點稱為發佈者。訂閱這些更改的節點稱為訂閱者。可以有一個或多個訂閱者。

2.1 發佈者

發佈者是從一組表生成的一組更改。它被稱為更改集或複製集。發佈者只能包含表，不能包含任何其他對象。只支持複製這些表上的DML操作變更，但不能複製DDL操作變更。

在發佈者中，可以選擇要複製的DML類型：INSERT或DELETE或UPDATE或ALL。默認情況下複製全部。您必須在要發佈的表上設置標識，以將UPDATES和DELETES複製到訂閱者。標識有助於識別要更新或刪除的行。

表的主鍵是其默認標識。還可以使用NO NULL值創建唯一索引作為標識。如果沒有主鍵或具有NO NULL的唯一索引，則可以將replica_identity設置為FULL。當標識設置為FULL時，postgres將整行用作鍵。當然，這可能使複製效率下降。

如果在UPDATE或DELETE操作之後將沒有主鍵和非默認標識的表添加到發佈中，則可能會發生錯誤。

2.2 訂閱者

訂閱者可以訂閱一個或多個發佈者。在添加訂閱之前，必須確保已在訂閱節點中創建了要複製的表，當然你可以通過在發佈節點轉儲模式元數據到訂閱節點實現。

2.3 邏輯複製的一個示例

以下示例步驟僅適用於PostgreSQL 10和11版本之間的邏輯複製。在發佈節點上，創建發佈。您可以添加所有表,也可以選擇將選定的表添加到發佈中。

-- For adding ALL Tables in Database    CREATE PUBLICATION percpub FOR ALL TABLES;    -- For adding Selected Tables in Database    CREATE PUBLICATION percpub FOR TABLE scott.employee scott.departments;

在訂閱者節點上，創建引用發佈者節點上的發佈的訂閱。如上所述，在創建訂閱之前，將表的DDL轉儲執行到訂閱者節點，

$ pg_dump -h publisher_server_ip -p 5432 -d percona -Fc -s -U postgres | pg_restore -d percona -h subscriber_node_ip -p 5432 -U postgres    CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=secret port=5432' PUBLICATION percpub;

上述命令還會複製表中預先存在的數據。如果要禁用預先存在的數據的副本，可以使用以下語法。然後，它將在您運行此命令後才開始將更改複製到發佈者。

CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=oracle port=5432' PUBLICATION percpub WITH (copy_data = false);

在發佈節點上使用以下命令監視複製。

$ psql  x  select * from pg_stat_replication;

3 PostgreSQL 9.4和PostgreSQL 11之間的複製

現在，那些比PostgreSQL 10更舊的版本呢？為此，有一個名為pglogical的擴展 ，適用於從9.4到11的版本。使用pglogical，您可以輕鬆地將PostgreSQL 9.4複製到PostgreSQL 11。

以下步驟演示了使用pglogical擴展在PG 9.4和PG 11之間設置複製的過程。

步驟1：將 pgserver_94視為具有數據庫的源服務器：在PostgreSQL 9.4上運行的percona_94。創建以下擴展名。

[pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical_origin"  CREATE EXTENSION  [pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical"  CREATE EXTENSION

步驟2：現在，您可以繼續添加選定的表或模式中的所有表或多個模式以進行複製。在以下示例中，當其中一個表上沒有主鍵時，可以看到錯誤。

[pgserver_94:] $psql -d percona_94  psql (9.4.21)  Type "help" for help.  percona_94=# SELECT pglogical.create_node(node_name := 'provider1',dsn := 'host=192.168.0.24 port=5432 dbname=percona_94');  create_node  -------------  2976894835  (1 row)  percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);  ERROR: table pgbench_history cannot be added to replication set default  DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs  HINT: Add a PRIMARY KEY to the table  percona_94=# ALTER TABLE pgbench_history ADD PRIMARY KEY (tid,aid,delta);  ALTER TABLE  percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);  replication_set_add_all_tables  --------------------------------  t  (1 row)

步驟3：在訂閱者節點（即PostgreSQL 11數據庫）上，您可以運行類似的命令，如下所示。

創建

pgserver_11,創建以下擴展名。

[pgserver_11:] $psql -d percona_94 -c "CREATE EXTENSION pglogical_origin"  CREATE EXTENSION  [pgserver_11:] $psql -d percona_94 -c "CREATE EXTENSION pglogical"  CREATE EXTENSION    [pgserver_11:] $psql -d percona_11  psql (11.2)  Type "help" for help.  percona_11=# SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=127.0.0.1 port=5432 dbname=percona_11 password=secret');   create_node  -------------     330520249  (1 row)  percona_11=# SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=192.168.0.24 port=5432 dbname=percona_94 password=secret');   create_subscription  ---------------------            1763399739  (1 row)  

步驟4：然後，您可以通過查詢pglogical始終更新的幾個表來驗證複製狀態：

percona_11=# select * from pglogical.local_sync_status;   sync_kind | sync_subid | sync_nspname |   sync_relname   | sync_status | sync_statuslsn  -----------+------------+--------------+------------------+-------------+----------------   f         | 1763399739 | public       | pgbench_accounts | r           | 0/2EB7D48   f         | 1763399739 | public       | pgbench_history  | r           | 0/2EB7D48   f         | 1763399739 | public       | pgbench_tellers  | r           | 0/2EB7D48   f         | 1763399739 | public       | pgbench_branches | r           | 0/2EB7D48   d         | 1763399739 |              |                  | r           | 0/0  (5 rows)  percona_11=# select * from pglogical.subscription;     sub_id   |   sub_name    | sub_origin | sub_target | sub_origin_if | sub_target_if | sub_enabled |             sub_slot_name              |         sub_rep  lication_sets          | sub_forward_origins | sub_apply_delay  ------------+---------------+------------+------------+---------------+---------------+-------------+----------------------------------------+----------------  -----------------------+---------------------+-----------------   1763399739 | subscription1 | 2976894835 |  330520249 |    2402836775 |    2049915666 | t           | pgl_percona_11_provider1_subscription1 | {default,defaul  t_insert_only,ddl_sql} | {all}               | 00:00:00  (1 row)

3.1 主鍵選擇

在第2步中，您了解了模式的所有表：public沒有主鍵的表通過創建主鍵，從而添加到複製集。我選擇的主鍵可能不符合業務邏輯，因為它僅用於演示。但是，當您選擇主鍵時，請確保選擇符合程序邏輯的鍵。它必須始終是唯一的，並使用通常不包含NULL的列。如果不徹底研究主鍵選擇，可能會導致應用程序停機。以下是您可能遇到的示例錯誤：

[pgserver_94:] $pgbench -c 10 -T 300 -n percona_94  Client 7 aborted in state 12: ERROR: duplicate key value violates unique constraint "pgbench_history_pkey"  DETAIL: Key (tid, aid, delta)=(7, 63268, 2491) already exists.

到目前為止，我們已經看到了如何使用pglogical在較舊版本和較新版本PostgreSQL之間創建複製。設置複製後，您可以輕鬆地將應用程序切換到最新版本，同時降低停機時間。

翻譯者 崔鵬

原文出處

請點擊「文章底部」閱讀原文進行查看。

https://www.percona.com/blog/2019/04/04/replication-between-postgresql-versions-using-logical-replication/