PostgreSQL逻辑订阅

测试环境：PostgreSQL 13.2

1、逻辑订阅简介

由于物理复制只能做到这个集群的复制，不能正对某个对象（表）进行复制，且物理复制的备库只能读，不能写。相反，逻辑订阅同时支持主备库读写，且可以做到针对某个对象进行复制。有更高的灵活性。但是逻辑订阅对主库性能影响较大。

2、发布端配置

pg_hba.conf 文件中加入，允许备用库访问。

host    mydbname     postgres        192.168.6.180/24        trust

postgresql,conf文件中，将wal_level修改为：

wal_level =  logical

登录发布端db，发布端角色必须具备replication权限，或超级用户权限，本文采用postgres用户。

postgres=#\c mydbname  //连接要操作的数据库

mydbname=# create table t1(id int primary key, info text, crt_time timestamp); //创建被发布表格

mydbname=# create publication testpub1 for table t1; //创建发布对象
ALTER TABLE t1 OWNER TO myuser; //将跟用户myuser增加访问权限

查看当前库有哪些发布：

mydbname=# select * from pg_publication;

 pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete

----------+----------+--------------+-----------+-----------+-----------

 testpub1 |       10 | f            | t         | t         | t

(1 row)

3、配置订阅端

创建subscription用户，必须是超级用户，本文使用postgres用户。

postgres=#\c mydbname  //连接要操作的数据库

mydbname=# create table t1(id int primary key, info text, crt_time timestamp); //创建被发布表格

ALTER TABLE t1 OWNER TO myuser; //将跟用户myuser增加访问权限
mydbname=# create subscription testsub1 connection 'hostaddr=192.168.6.180 port=5433 user=postgres dbname=mydbname' publication testpub1 with (enabled, create_slot, slot_name='sub1_from_pub1');

查询有哪些订阅

postgres=# select * from pg_subscription ;

 subdbid | subname  | subowner | subenabled |                          subconninfo                           |  subslotname   | subsynccommit | subpublications

---------+----------+----------+------------+----------------------------------------------------------------+----------------+---------------+-----------------

   16401 | testsub1 |       10 | t          | hostaddr=192.168.7.177 port=1921 user=postgres dbname=postgres | sub1_from_pub1 | off           | {testpub1}

(1 row)

postgres=# select * from pg_stat_subscription ;

 subid | subname  | pid  | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time

-------+----------+------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------

 44943 | testsub1 | 7877 |       | 1/76119308   | 2021-04-10 13:20:07.497634+08 | 2021-04-11 01:23:39.104443+08 | 1/76119308     | 2021-04-10 13:20:07.497634+08

(1 row)

4、发布端数据库插入测试

mydbname=# insert into t1 select t,md5(random()::text),clock_timestamp() from generate_series(1,1000) t;

INSERT 0 1000

5、订阅端查看：

mydbname=# select count(*) from t1;

 count

-------

  100

(1 row)

mydbname=# select * from t1 limit 10;

 id |               info               |          crt_time

----+----------------------------------+----------------------------

  1 | 31422ff05d990455a4632bf97cbcda45 | 2021-04-10 13:21:57.780586

  2 | 4a3f42ed6421f89cdf8c09e221431520 | 2021-04-10 13:21:57.780713

  3 | 88423917787f7c99340668c324fc35d2 | 2021-04-10 13:21:57.78072

  4 | 11dd182301956e9458460fc3f33cc5d6 | 2021-04-10 13:21:57.780724

  5 | bd0a78455bc400b39a697148a564e9eb | 2021-04-10 13:21:57.780727

  6 | 6b3b36fd0fe55d49932b990809913744 | 2021-04-10 13:21:57.78073

  7 | 3e13b1856a5f5623acfe607de775f0c1 | 2021-04-10 13:21:57.780733

  8 | f7b51908bf88974a7532ce0ec760d585 | 2021-04-10 13:21:57.780736

  9 | f9002e38c2fb8a3ed6c0453d518815aa | 2021-04-10 13:21:57.780739

 10 | ea68f6d87051fb8fef4586c0a2c04f5c | 2021-04-10 13:21:57.780741

(10 rows)

巴特西

PostgreSQL逻辑订阅

最新文章

热门文章