逻辑复制(发布-订阅)

\dRp list replication publications

\dRs list replication subscriptions

listen_addresses = '*'
wal_level = logical ####修改postgres.conf，wal_level参数重启生效
max_replication_slots = 8  ###设置数据库复制槽的数量，值必须大于订阅的数量
max_wal_senders = 10       ###设置值必须大于max_replication_slots + 物理备库数

host pubdb rep 192.168.88.0/32 md5

createuser rep replication login connectionlimit8password'rep';
createdatabase pubdb;
GRANT ALL PRIVILEGESONDATABASE  pubdb to rep;

\c pubdb --如果复制用户不是表的owner，应该授权grantselecton trep1 to replicate1
grantselecton trep1 to rep;
createtable trep1(a int primary key,b char(10));
insertinto trep1 values(1,'abc');
altertable trep1 owner to rep ;

\c pubdb rep
create publication pub fortable trep1;

select * from pg_publication;
pubdb=> select * from pg_publication;
  oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
 24674 | pub | 24662 | f | t | t | t | t
\dRp
pubdb=> \dRp
                        List of publications
 Name | Owner | All tables | Inserts | Updates | Deletes | Truncates 
------+-------+------------+---------+---------+---------+-----------
 pub | rep | f | t | t | t | t
select * from pg_publication_tables;
pubdb=> select * from pg_publication_tables; 
 pubname | schemaname | tablename 
---------+------------+-----------
 pub | public | trep1
pubdb=> select * from pg_publication_rel;
  oid | prpubid | prrelid 
-------+---------+---------
24675| 24674 |   24669

listen_addresses = '*'
wal_level = logical ####修改postgres.conf，wal_level参数重启生效
max_replication_slots = 8  ###设置数据库复制槽的数量，值必须大于订阅的数量
max_wal_senders = 10       ###设置值必须大于max_replication_slots + 物理备库数

host subdb rep 192.168.88.0/32 md5

createuser rep replication login connectionlimit8password'rep';
createdatabase subdb;

\c subdb 
createtable trep1(a int primary key,b char(10));
altertable trep1 owner to rep ;

create subscription kingbase8lasprdl_kfs_logical_slot connection'host=192.168.88.190 port=54329 dbname=pubdb user=rep password=rep' publication pub;

select * from pg_stat_subscription;
subdb=# 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 
-------+-----------------------------------+-----+-------+--------------+--------------------+-----------------------+----------------+-----------------
16400| kingbase8lasprdl_kfs_logical_slot |     | |              | |                       | |
select * from pg_subscription;
subdb=# select * from pg_subscription;
  oid | subdbid |              subname | subowner | subenabled | subconninfo |         
   subslotname | subsynccommit | subpublications 
-------+---------+-----------------------------------+----------+------------+-------------------------------------------------------------------+---------
--------------------------+---------------+-----------------
16400| 16385 | kingbase8lasprdl_kfs_logical_slot | 10 | t | host=192.168.88.190 port=54329 dbname=pubdb user=rep password=rep | kingbase
8lasprdl_kfs_logical_slot | off | {pub}
\dRs
subdb=# \dRs
                       List of subscriptions
               Name | Owner | Enabled | Publication 
-----------------------------------+--------+---------+-------------
 kingbase8lasprdl_kfs_logical_slot | system | t | {pub}
select * from pg_subscription_rel ; 
subdb=# select * from pg_subscription_rel ; 
 srsubid | srrelid | srsubstate | srsublsn 
---------+---------+------------+----------
   16400 |   16395| i |

select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name='kingbase8lasprdl_kfs_logical_slot';