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