當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

postgresql update使用别名_PostgreSQL逻辑复制之pglogical

發布時間：2024/1/23 数据库 37 豆豆

生活随笔收集整理的這篇文章主要介紹了 postgresql update使用别名_PostgreSQL逻辑复制之pglogical 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

朱貴平(lottu)? ?中國PG分會認證專家

宜搜科技資深DBA，擅長Oracle、PostgreSQL，目前從事Oracle、PostgreSQL

相關的運維管理及遷移等工作。

一、pglogical介紹

pglogical 是 PostgreSQL 的拓展模塊, 為 PostgreSQL 數據庫提供了邏輯流復制發布和訂閱的功能。pglogical 重用了 BDR 項目中的一部分相關技術。pglogical 是一個完全作為PostgreSQL 擴展實現的邏輯復制系統。完全集成，它不需要觸發器或外部程序。這種物理復制的替代方法是使用發布/訂閱模型復制數據以進行選擇性復制的一種高效方法。支持 PG10、9.6、9.5、9.4 ，提供比 Slony、Bucardo 或 Londiste 更快的復制速度，以及跨版本升級。
我們使用的下列術語來描述節點和數據流之間的關系，重用了一些早期的 Slony 技術中的術語:

節點 - PostgreSQL 數據庫實例
發布者和訂閱者 - 節點的角色名稱
復制集 - 關系表的集合

pglogical 是新技術組件，使用了最新的 PostgreSQL 數據庫中的一些核心功能，所以存在一些數據庫版本限制:

數據源發布和訂閱節點需要運行 PostgreSQL 9.4 +
復制源過濾和沖突檢測需要 PostgreSQL 9.5 +

支持的使用場景:

主版本數據庫之間的升級(存在上述的版本限制)
完整的數據庫復制
利用復制集，選擇性的篩選的關系表
可從多個上游服務器,做數據的聚集和合并

更多介紹；大家請閱讀
英文版
中文版

二、安裝操作

本節介紹了pglogical 擴展模塊復制的基本用法。
下載地址，安裝步驟

tar -zxvf pglogical-REL2_2_0.tar.gz cd pglogical-REL2_2_0. /home/postgres/.bash_profilepg_configUSE_PGXS=1 make cleanUSE_PGXS=1 makeUSE_PGXS=1 make install

首先 PostgreSQL服務器必須正確配置才能夠支持邏輯解碼︰

wal_level = 'logical'# one per database needed on (provider/subscriber)provider nodemax_worker_processes = 10 # one per node needed on provider nodemax_replication_slots = 10 # one per node needed on provider nodemax_wal_senders = 10 shared_preload_libraries = 'pglogical'

如果你想要處理解決與上一次/第一次更新之間的沖突 wins(參閱沖突章節), 你的數據庫版本需要為PostgreSQL 9.5+ (在9.4中無效) 您可以向 PostgreSQL.conf 添加此額外的選項:

# needed for last/first update wins conflict resolution property available in Postgretrack_commit_timestamp = on

pg_hba.conf 需要配置成允許從本地主機復制，用戶擁有有復制權限,連接權限；并重啟數據庫服務

host replication postgres 網段ip/24 trust

在所有節點上所對應數據庫安裝pglogical拓展模塊:

CREATE EXTENSION pglogical;

三、pglogical復制配置

現有實驗環境

數據庫版本IP角色

psql (PostgreSQL) 9.6.0	192.168.1.221	provider
psql (PostgreSQL) 10.5	192.168.1.235	subscriber

3.1、時間同步

服務器時間同步(主備庫都需操作)

echo "*/20 * * * * /usr/sbin/ntpdate -u ntp.api.bz >/dev/null" >> /var/spool/cron/root

3.2、提供者節點配置

1、創建節點

在一個數據庫里創建提供者節點

# 創建節點SELECT pglogical.create_node( node_name := 'provider1', dsn := 'host=192.168.1.221 port=5432 dbname=lottu');

2、創建復制集

將public架構中的所有表添加到default復制集中

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

復制集default的表都必需要primary key

3.3、訂閱者節點配置

1、創建節點

在另一個數據庫創建訂閱者節點

SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=192.168.1.235 port=5432 dbname=lottu');

2、創建復制集

訂閱提供者節點，該訂閱將在后臺啟動同步和復制過程

SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu');

3.4、驗證復制

前面我們已經完成安裝/配置 pglogical 操作。

1、創建測試表

create table tbl_lottu01(id int primary key, name text, reg_time timestamp);

由于需要驗證insert/update/delete/truncate操作是否同步；所以創建的表要有主鍵。當然只對發布者必須要主鍵約束。

2、添加測試數據

lottu=# insert into tbl_lottu01 select generate_series(1,10000),'lottu',now();INSERT 0 10000

3、將表添加對應的復制集

對新建的表；并沒有為其分配對應的復制集；需要手動添加。當然可以利用觸發器自動添加；后續補充。

lottu=# select * from pglogical.replication_set_table ; set_id | set_reloid | set_att_list | set_row_filter --------+------------+--------------+----------------(0 rows)

方法1：

前面講解創建復制集中；3.2.2中“將public架構中的所有表添加到default復制集中”

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

方法二：

將表添加到對應的復制集中；詳細介紹可以查看前面文檔。

pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean, columns text []，row_filter text)

兩種方法都可以；我們采用第二種方法。

lottu=# select pglogical.replication_set_add_table( set_name := 'default', relation := 'tbl_lottu01',synchronize_data := true); replication_set_add_table --------------------------- t(1 row)

我們查看復制集

同時，數據也同步到 subscriber 節點。因為在第二種方法有同步的操作。若使用第一種方法；還需要在subscriber 節點同步表的操作。

#重新同步一個表pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass) #將所有的表都同步pglogical.alter_subscription_synchronize(subscription_name name, truncate bool)

4、查看subscriber節點

查看表 tbl_lottu01 信息

lottu=# select * from pglogical.show_subscription_table('subscription1','tbl_lottu01'); nspname | relname | status ---------+-------------+-------------- public | tbl_lottu01 | synchronized(1 row)lottu=# select count(1) from tbl_lottu01; count ------- 10000(1 row)

在復制集default中：update/delete/truncate 操作也是同步復制。不作演示

復制集INSERTUPDATEDELETETRUNCATE

default	√	√	√	√
default_insert_only	√	×	×	×

四、復制特性擴展

4.1、延遲復制

pglogical.create_subscription(subscription_name name, provider_dsn text, replication_sets text[], synchronize_structure boolean, synchronize_data boolean, forward_origins text[], apply_delay interval)

參數:

subscription_name - 訂閱的名稱，必須是唯一的
provider_dsn - 提供者的連接字符串
replication_sets - 要訂閱的復制集數組，這些必須已存在，默認為“{default，default_insert_only，ddl_sql}”
synchronize_structure - 指定是否將提供者與訂閱者之間的結構同步，默認為false
synchronize_data - 指定是否將數據從提供者同步到訂閱者，默認為true
forward_origins - 要轉發的原始名稱數組，當前只支持的值是空數組，意味著不轉發任何不是源自提供者節點的更改，或“{all}”這意味著復制所有更改，無論它們的來源是什么，默認是全部}”
apply_delay - 延遲復制多少，默認為0秒

示例：數據表結構同步；且延遲復制1分鐘

SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu',synchronize_structure := true,apply_delay := '00:01:00'::interval);

4.2、對源端進行行/列過濾

過濾機制需要 PostgreSQL 9.5 +

pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean, columns text []，row_filter text)

參數：

set_name - 現有復制集的名稱
relation - 要添加到集合中的表的名稱或OID
synchronize_data - 如果為true，則表數據將在訂閱給定復制集的所有訂戶上同步，默認為false
columns - 要復制的列的列表。通常，當應復制所有列時，這將設置為NULL，這是默認值
row_filter - 行過濾表達式，默認為NULL(無過濾)，有關詳細信息，請參閱(行過濾)。警告：在使用有效行篩選器同步數據時要小心。使用synchronize_data=true有效row_filter就像對表的一次性操作。使用修改后再次執行它將row_filter不會將數據同步到訂戶。訂閱者可能需要pglogical.alter_subscription_resynchronize_table()來修復它。

示例：對表tbl_lottu02中字段{id, name, job} 字段列過濾；且對條件 ‘id > 10’ 進行行過濾 **# provider 節點創建表并插入測試數據create table tbl_lottu02 (id int primary key, name text, job text, reg_time timestamp );insert into tbl_lottu02 select generate_series(1,20) id,'lottu'||generate_series(1,20),'pg', now();# subscriber節點創建表；可以只創建復制的列的數據表create table tbl_lottu02 (id int primary key, name text, job text, reg_time timestamp );# orcreate table tbl_lottu02 (id int primary key, name text, job text);#provider 節點將表加入復制集中；并同步記錄lottu=# select pglogical.replication_set_add_table(set_name := 'default', relation := 'tbl_lottu02', synchronize_data := true, columns := '{id, name, job}',row_filter := 'id < 10'); replication_set_add_table --------------------------- t(1 row)# subscriber節點查看表tbl_lottu02記錄lottu=# select * from tbl_lottu02; id | name | job ----+--------+----- 1 | lottu1 | pg 2 | lottu2 | pg 3 | lottu3 | pg 4 | lottu4 | pg 5 | lottu5 | pg 6 | lottu6 | pg 7 | lottu7 | pg 8 | lottu8 | pg 9 | lottu9 | pg(9 rows)

4.3、為新表自動分配復制集

事件觸發器工具可用于描述為新創建的表定義復制集的規則。

CREATE OR REPLACE FUNCTION pglogical_assign_repset()RETURNS event_trigger AS $$DECLARE obj record;BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP IF obj.object_type = 'table' THEN IF obj.schema_name = 'config' THEN PERFORM pglogical.replication_set_add_table('configuration', obj.objid); ELSIF NOT obj.in_extension THEN PERFORM pglogical.replication_set_add_table('default', obj.objid); END IF; END IF; END LOOP;END;$$ LANGUAGE plpgsql;CREATE EVENT TRIGGER pglogical_assign_repset_trg ON ddl_command_end WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS') EXECUTE PROCEDURE pglogical_assign_repset();

4.4、沖突檢測

沖突檢測需要 PostgreSQL 9.5 +
如果節點訂閱多個提供程序，或當本地寫入在訂閱服務器上發生，可能會發生沖突，尤其是對傳入的變化。這些都自動檢測，并可以就此采取行動取決于配置。
解決沖突的辦法是通過配置 pglogical.conflict_resolution 參數。
pglogical.conflict_resolution 支持的配置參數選項為︰

error - 復制將停止上錯誤如果檢測到沖突和手動操作需要解決
apply_remote - 總是應用與本地數據有沖突的更改，這是默認值
keep_local - 保留數據的本地版本，并忽略來自遠程節點相互沖突的更改
last_update_wins - 時間戳為提交最新的版本(newest commit timestamp)的數據將會被保存(這可以是本地或遠程版本)
first_update_wins - 時間戳為最舊的版本(oldest timestamp)的數據將會被保存(這可以是本地或遠程版本)

當參數track_commit_timestamp被禁用時，唯一允許的配置值是 apply_remote。PostgreSQL 9.4 不支持 track_commit_timestamp 配置參數只能配置參數apply_remote(該參數是默認值)。

# 在訂閱者節點配置;我們保留最新的數據track_commit_timestamp = onpglogical.conflict_resolution = 'last_update_wins'# 在訂閱者節點創建測試表tbl_lottu03lottu=# create table tbl_lottu03(id int primary key, name text);CREATE TABLElottu=# insert into tbl_lottu03 values (1001,'subscriber');INSERT 0 1# 在發布者節點創建測試表create table tbl_lottu03(id int primary key, name text);select pglogical.replication_set_add_table( set_name := 'default', relation := 'tbl_lottu03',synchronize_data := true);insert into tbl_lottu03 values (1001,'provider');# 在訂閱者節點查看數據lottu=# select * from tbl_lottu03; id | name ------+---------- 1001 | provider

后記：?在訂閱者的表需要主鍵約束；不然檢測不到沖突；是否需要主鍵約束當然這個也是根據需求而定。

五、場景介紹

5.1、可從多個上游服務器,做數據的聚集和合并

發布者跟訂閱者的關系；一個發布者可以被多個訂閱者訂閱。多個發布者可以被同一個訂閱者訂閱。

數據庫版本IP數據庫角色

psql (PostgreSQL) 9.6.0	192.168.1.221	lottu	provider1
psql (PostgreSQL) 9.6.0	192.168.1.221	lottu02	provider2
psql (PostgreSQL) 10.5	192.168.1.235	lottu	subscriber

為了加以區分；我們定制SQL提示符；例如

lottu=# \set PROMPT1 '%`echo provider1=`'provider1=

5.1.1、創建測試表

# 每個節點創建測試表; 訂閱者創建的表可以無主鍵；若訂閱者有主鍵，可利用序列自增來解決沖突。(例如：本例是兩個發布者，則發布者1可取奇數;發布者二可取偶數)。若無主鍵;數據不受影響。provider1=create table tbl_lottu05(id int primary key,name text);CREATE TABLEprovider1=CREATE SEQUENCE seq_lottu05_id INCREMENT BY 2 START WITH 1;CREATE SEQUENCEprovider2=create table tbl_lottu05(id int primary key,name text);CREATE TABLEprovider2=CREATE SEQUENCE seq_lottu05_id INCREMENT BY 2 START WITH 2;CREATE SEQUENCEsubscriber=create table tbl_lottu05(id int primary key,name text);CREATE TABLE

5.1.2、搭建模擬場景

更多介紹查看第三節；或者查考《PostgreSQL 邏輯復制文檔 (pglogical 文檔 )》

# provider 節點1provider1=SELECT pglogical.create_node(node_name := 'provider1', dsn := 'host=192.168.1.221 port=5432 dbname=lottu'); create_node ------------- 2976894835 provider1=select pglogical.replication_set_add_table( set_name := 'default', relation := 'tbl_lottu05',synchronize_data := true); replication_set_add_table --------------------------- t# provider 節點2provider2=SELECT pglogical.create_node(node_name := 'provider2', dsn := 'host=192.168.1.221 port=5432 dbname=lottu02'); create_node ------------- 1828187473provider2=select pglogical.replication_set_add_table( set_name := 'default', relation := 'tbl_lottu05',synchronize_data := true); replication_set_add_table --------------------------- t# subscriber 節點subscriber=SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=192.168.1.235 port=5432 dbname=lottu'); create_node ------------- 2941155235 subscriber=SELECT pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu'); create_subscription --------------------- 1763399739 subscriber=SELECT pglogical.create_subscription(subscription_name := 'subscription2', provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu02'); create_subscription --------------------- 1871150101

5.1.3、插入數據驗證

provider1=insert into tbl_lottu05 select nextval('seq_lottu05_id'),'lottu' || generate_series(1,10,2);INSERT 0 5provider2=insert into tbl_lottu05 select nextval('seq_lottu05_id'),'lottu' || generate_series(1,10,2);INSERT 0 5subscriber=select * from tbl_lottu05; id | name ----+-------- 1 | lottu1 3 | lottu3 5 | lottu5 7 | lottu7 9 | lottu9 2 | lottu1 4 | lottu3 6 | lottu5 8 | lottu7 10 | lottu9(10 rows)

5.2、數據庫版本升級

pglogical 對 PostgreSQL 版本升級是一個很實用的工具。能實現以幾乎為零的停機時間遷移和升級PostgreSQL。局限性在于pglogical支持的 PostgreSQL 版本。
本例簡單模擬下pglogical 對 PostgreSQL 版本升級；忽略插件、存儲空間、表空間、以及業務SQL和自定義函數創建。

數據庫版本IP數據庫角色

psql (PostgreSQL) 9.6.0	192.168.1.221	lottu	provider
psql (PostgreSQL) 10.5	192.168.1.235	lottu	subscriber

5.2.1、新建升級數據庫

以一個全新的數據庫進行操作

PG10-235=drop database if exists lottu;NOTICE: database "lottu" does not exist, skippingDROP DATABASEPG10-235=create database lottu owner lottu;CREATE DATABASE

5.2.2、pglogical插件安裝

本環境已經安裝pglogical；只要到對應數據庫創建pglogical插件即可

5.2.3、配置pglogical

發布者節點

這個要根據真實環境來設置；考慮到真實環境數據庫中表不一定都有主鍵約束，可將表放到復制集 "default_insert_only"。

PG96-221=SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=192.168.1.221 port=5432 dbname=lottu'); create_node ------------- 3171898924(1 row)PG96-221=SELECT pglogical.replication_set_add_all_tables('default_insert_only', ARRAY['public']); replication_set_add_all_tables -------------------------------- t(1 row)

該函數可實現主鍵和非主鍵分別放到'default'和'default_insert_only'復制集

CREATE OR REPLACE FUNCTION "public"."pglogical_relhaspkey_repset"() RETURNS "pg_catalog"."void" AS $BODY$ DECLARE obj record;BEGIN FOR obj IN (SELECT n.nspname, c.relname, c.relhaspkey FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1, 2) LOOP IF obj.relhaspkey THEN PERFORM pglogical.replication_set_add_table(set_name := 'default', relation := obj.relname :: regclass); ELSE PERFORM pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := obj.relname :: regclass); END IF; END LOOP;END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100

訂閱者節點

PG10-235=SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=192.168.1.235 port=5432 dbname=lottu'); create_node ------------- 2941155235

5.2.4、遷移DDL

pglogical 可以同步表/序列結構；在創建訂閱者 'pglogical.create_subscription' ; 里面參數synchronize_structure - 指定是否將提供者與訂閱者之間的結構同步，默認為false。可以同步表/序列/索引。

PG10-235=SELECT pglogical.create_subscription(subscription_name := 'subscription', provider_dsn := 'host=192.168.1.221 port=5432 dbname=lottu', synchronize_structure := true, synchronize_data := false); create_subscription --------------------- 2875150205(1 row)

5.2.5、業務代碼改寫優化

上一步我們沒同步數據。所以參數synchronize_data我們選擇false。雖然把表/序列/索引結構同步過來；但是業務代碼(函數/插件)沒同步過來；還要考慮這些業務代碼是否需要改寫優化。因為新的版本往往有新特性。

5.2.6、全量復制

pglogical有將所有未同步表都在單個操作中同步
語法：

pglogical.alter_subscription_synchronize(subscription_name name, truncate bool)

參數：

subscription_name - 現有訂閱的名稱
truncate - 如果為true，表將在復制前被截斷，默認為false

PG10-235=SELECT pglogical.alter_subscription_synchronize(subscription_name := 'subscription', truncate := false); alter_subscription_synchronize -------------------------------- t(1 row)

5.2.7、對比數據一致

經過上一步，兩個數據庫數據達到一致。

查看表同步狀態

PG10-235=select * from pglogical.show_subscription_table(subscription_name := 'subscription', relation := 'tbl_lottu01'::regclass); nspname | relname | status ---------+-------------+-------------- public | tbl_lottu01 | synchronized(1 row)

比對兩個數據庫表的數據

PG96-221=select count(1) from tbl_lottu01; count ------- 10000(1 row)PG10-235=select count(1) from tbl_lottu01; count ------- 10000(1 row)

5.2.8、業務切換

比對數據一致；可以將業務切換到升級后的數據庫。

5.2.9、刪除pglogical配置

這步是可選的；保證升級后的數據庫正常支持業務。不存在數據丟失的情況下。可以刪除pglogical配置。
刪除步驟：

刪除訂閱信息
刪除兩個數據庫pglogical節點

PG10-235=select pglogical.drop_subscription(subscription_name := 'subscription',ifexists := true); drop_subscription ------------------- 1(1 row)PG10-235=select pglogical.drop_node(node_name := 'subscriber', ifexists := true); drop_node ----------- t(1 row)PG96-221=select pglogical.drop_node(node_name := 'provider', ifexists := true); drop_node ----------- t(1 row)

分類:?PostgreSQL,?postgresql高可用

歡迎投稿

????????中國開源軟件推進聯盟PostgreSQL分會，歡迎大家積極投稿，向PGer分享自己的實踐經驗、心得體會，共建PG中國生態。

投稿郵箱：

partner@postgresqlchina.com

超強干貨來襲云風專訪：近40年碼齡，通宵達旦的技術人生

總結

以上是生活随笔為你收集整理的postgresql update使用别名_PostgreSQL逻辑复制之pglogical的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： sql能查到数据 dataset对象里面
下一篇： python 3.6.0新语法_详解Py