14.PG逻辑复制搭建

1.准备两台服务器：

172.16.191.140   发布者

172.16.191.139   订阅者

2.在这两台服务器都安装了pg数据库

3.分别在这三台服务器上pg_hba_conf配置文件新增

host  all       all       172.16.191.0/0      md5
host  replication   all       172.16.191.0/0      trust

4.查看服务器主备情况

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f                     # f代表是主库，t代表是从库
(1 row)

5.开始配置(发布者)

postgres=# create user rep11 replication login connection limit 8 password 'rep11';   #创建用户
CREATE ROLE
postgres=# create database pubdb11;      #创建数据库
CREATE DATABASE 
postgres=# \c pubdb11 
You are now connected to database "pubdb11" as user "postgres".^
pubdb11=# create table tt11(id int4 primary key ,name text);   #创建表
CREATE TABLE
pubdb11=# insert into tt11 values (1,'a');     #插入数据
INSERT 0 1pubdb11=# CREATE PUBLICATION pub11 FOR TABLE tt11;   #将表tt11发布出去
CREATE PUBLICATION
pubdb11=# 
pubdb11=# 
pubdb11=# select * from pg_publication;       # 查看发布
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16424 | pub11   |       10 | f            | t         | t         | t         | t           | f
(1 row)

发布节点为复制用户授权
grant  connect on database pubdb11 to rep11;
grant usage on schema public to rep11;
grant select on tt11 to rep11;

6.开始配置(订阅者)

postgres=# create database subdb11;
CREATE DATABASE
postgres=# create user rep11 replication login connection limit 8 password 'rep11';
CREATE ROLE
postgres=# \c subdb11
You are now connected to database "subdb11" as user "postgres".
subdb11=# create table tt11(id int4 primary key ,name text);
CREATE TABLE
subdb11=# 
subdb11=# 
subdb11=# create SUBSCRIPTION sub11 connection 'host=172.16.191.140 port=5432 dbname=pubdb11 user=rep11 password=rep11' PUBLICATION pub11;
NOTICE:  created replication slot "sub11" on publisher
CREATE SUBSCRIPTION
subdb11=# 
subdb11=# 
subdb11=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tt11 | table | postgres
(1 row)

subdb11=# select * from tt11;
 id | name 
----+------
  1 | a

查看订阅者信息(备库上查看)

subdb11=# select *  from pg_subscription;
-[ RECORD 1 ]-------+-----------------------------------------------------------------------
oid                 | 16455
subdbid             | 16446
subskiplsn          | 0/0
subname             | sub11
subowner            | 10
subenabled          | t
subbinary           | f
substream           | f
subtwophasestate    | d
subdisableonerr     | f
subpasswordrequired | t
subrunasowner       | f
subconninfo         | host=172.16.191.140 port=5432 dbname=pubdb11 user=rep11 password=rep11
subslotname         | sub11
subsynccommit       | off
subpublications     | {pub11}
suborigin           | any

在备库上查看逻辑复制监控

subdb11=# select * from pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid                 | 16455
subname               | sub11
pid                   | 27571
leader_pid            | 
relid                 | 
received_lsn          | 0/A430EB0
last_msg_send_time    | 2024-01-04 10:54:55.943492+08
last_msg_receipt_time | 2024-01-04 10:54:55.944612+08
latest_end_lsn        | 0/A430EB0
latest_end_time       | 2024-01-04 10:54:55.943492+08

 

在主库上查看如下信息

#复制信息
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 28958
usesysid         | 16471
usename          | rep11
application_name | sub11
client_addr      | 172.16.191.139
client_hostname  | 
client_port      | 37706
backend_start    | 2024-01-04 10:33:57.84377+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/A430EB0
write_lsn        | 0/A430EB0
flush_lsn        | 0/A430EB0
replay_lsn       | 0/A430EB0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2024-01-04 10:41:44.105355+08


#复制槽信息
postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+----------
slot_name           | sub11
plugin              | pgoutput
slot_type           | logical
datoid              | 16472
database            | pubdb11
temporary           | f
active              | t
active_pid          | 28958
xmin                | 
catalog_xmin        | 835
restart_lsn         | 0/A430E78
confirmed_flush_lsn | 0/A430EB0
wal_status          | reserved
safe_wal_size       | 
two_phase           | f
conflicting         | f