## vi postgressql.conf（重啟生效）
listen_addresses = '*'
wal_level=logical
max_replication_slots=8
max_wal_senders=10
## alter system set
alter system set wal_level=logical;
## 參數(shù)說明
wal_level設(shè)置為logical，才支持邏輯復(fù)制，低于這個(gè)級別邏輯復(fù)制不能工作。
max_replication_slots設(shè)置值必須大于訂閱的數(shù)量。
max_wal_senders設(shè)置值必須大于max_replication_slots參數(shù)值加上物理備庫數(shù)，因?yàn)槊總€(gè)訂閱在主庫上都會占用主庫一個(gè)wal發(fā)送進(jìn)程。

vi pg_hba.conf
host    replication     test       0/0         md5

## vi postgresql.conf（重啟生效）
listen_addresses = '*'
wal_level=logical
max_replication_slots=8
max_logical_replication_workers=8
## alter system set
alter system set wal_level=logical;
## 參數(shù)說明
max_replication_slots設(shè)置數(shù)據(jù)庫復(fù)制槽數(shù)量。
max_logical_replication_workers設(shè)置邏輯復(fù)制進(jìn)程數(shù)，應(yīng)大于訂閱節(jié)點(diǎn)的數(shù)量，并且給表同步預(yù)留一些進(jìn)程數(shù)量。
注意：max_logical_replication_workers會消耗后臺進(jìn)程數(shù)，并且從max_worker_processes參數(shù)設(shè)置的后臺進(jìn)程數(shù)中消費(fèi)，因此max_worker_processes需要設(shè)置的大一些。

postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE
limit 8：為新用戶設(shè)置最大數(shù)目連接數(shù)。默認(rèn)無限制。

## 創(chuàng)建復(fù)制數(shù)據(jù)庫
postgres=# create database pubdb;
CREATE DATABASE
## 授予復(fù)制用戶權(quán)限
postgres=# \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# grant all on schema public to replic;
GRANT
## 創(chuàng)建復(fù)制表
pubdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE
pubdb=> insert into c1 values (1,'a');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
  1 | a
(1 row)
## 創(chuàng)建發(fā)布
pubdb=> \c pubdb postgres
You are now connected to database "pubdb" as user "postgres".
pubdb=# create publication pub1 for table c1;
CREATE PUBLICATION
注意：如果發(fā)布多張表使用逗號隔開，如果發(fā)布所有表則將 for table 修改為 for all tables。
##查看創(chuàng)建的發(fā)布
pubdb=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 33177 | pub1    |       10 | f            | t         | t         | t         | t           | f
(1 row)
參數(shù)說明：
pubname：發(fā)布名稱。
pubowner：發(fā)布的屬主，可以和pg_user視圖的usesysid字段關(guān)聯(lián)查詢屬主的具體信息。
puballtables：是否發(fā)布數(shù)據(jù)庫中的所有表，t 表示發(fā)布數(shù)據(jù)庫中所有已存在的表和以后新建的表。
pubinsert：t 表示僅發(fā)布表上的insert操作。
pubupdate：t 表示僅發(fā)布表上的update操作。
pubdelete：t 表示僅發(fā)布表上的delete操作。
pubtruncate：t 表示僅發(fā)布表上的truncate操作。

pubdb=# grant connect on database pubdb to replic;
GRANT
pubdb=# grant usage on schema public to replic;
GRANT
pubdb=# grant select on c1 to replic;
GRANT

postgres=# create database subdb;
CREATE DATABASE
postgres=# create user replic replication login connection limit 8 password 'replic';
CREATE ROLE
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# grant all on schema public to replic;
GRANT
subdb=> create table c1 (id int4 primary key,name text);
CREATE TABLE

subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# create subscription sub1 connection 'host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
## 查看創(chuàng)建的訂閱
subdb=# \x
Expanded display is on.
subdb=# select * from pg_subscription;
-[ RECORD 1 ]----+-----------------------------------------------------------------------
oid              | 41374
subdbid          | 41361
subskiplsn       | 0/0
subname          | sub1
subowner         | 10
subenabled       | t
subbinary        | f
substream        | f
subtwophasestate | d
subdisableonerr  | f
subconninfo      | host=192.168.80.239 port=5432 dbname=pubdb user=replic password=replic
subslotname      | sub1
subsynccommit    | off
subpublications  | {pub1}

subdb=# grant connect on database subdb to replic;
GRANT
subdb=# grant usage on schema public to replic;
GRANT
subdb=# grant select on c1 to replic;
GRANT

postgres=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn 
-----------+----------+-----------+----------+--------+-------------
 sub1      | pgoutput | logical   | pubdb    | t      | 0/3F45C840
(1 row)

## 發(fā)布端向表中插入數(shù)據(jù)
pubdb=> insert into c1 values (2,'tt');
INSERT 0 1
pubdb=> select * from c1;
 id | name 
----+------
  1 | a
  2 | tt
(2 rows)
pubdb=> delete from c1 where id=1;
DELETE 1
pubdb=> select * from c1;
 id | name 
----+------
  2 | tt
(1 row)
## 訂閱端查看結(jié)果
subdb=# select * from c1;
 id | name 
----+------
  2 | tt
(1 row)
## 添加新表測試，發(fā)布端創(chuàng)建表結(jié)構(gòu)
pubdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE
## 訂閱端創(chuàng)建表結(jié)構(gòu)
subdb=> create table c2 (id int primary key,addr varchar(100));
CREATE TABLE
## 發(fā)布端授權(quán)
pubdb=> grant select on c2 to replic;
GRANT
## 將新表c2，添加到發(fā)布列表中
pubdb=> \c pubdb postgres 
You are now connected to database "pubdb" as user "postgres".
pubdb=# alter publication pub1 add table c2;
ALTER PUBLICATION
## 發(fā)布端查看發(fā)布列表
pubdb=# select * from pg_publication_tables;
 pubname | schemaname | tablename | attnames  | rowfilter 
---------+------------+-----------+-----------+-----------
 pub1    | public     | c1        | {id,name} | 
 pub1    | public     | c2        | {id,addr} | 
(2 rows)
## 如果沒有看到新表，可在訂閱端刷新訂閱
subdb=> \c subdb postgres
You are now connected to database "subdb" as user "postgres".
subdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
## 刪除復(fù)制設(shè)置
drop subscription sub1;