一、前言
在一台虚拟机上我创建了个实例、然后配置好后pg_basebackup在创建第二个实例
只修改了port,和archive_command路径
二、postgresql.conf
2.1、源端库
port
address
wal_level = logical
archive_command = 'cp %p/pgccc/pg_data_lj/archive/%f'
archive_mode = on
archive_timeout = 1800
max_wal_senders = 10
max_replication_slots = 10
log_directory = '/pgccc/log'
logging_collector = on
2.2、目的端库
port=5439
archive_command = 'cp %p/pgccc/pg_data_lj2/archive/%f'
max_replication_slots :订阅数+保留给表同步的连接数
max_logical_replication_workers :订阅数+保留给表同步的连接数
max_worker_processes :最少:max_logical_replication_workers+1
2.3、创建同名表、用户、库
1、2节点创建:
create user zcjtest with password 'zcjtest';
alter user zcjtest replication;
create database zcjtest owner zcjtest;
create schema zcjschema;
create table zcjschema.test (id int);
2.4、pg_hba.conf
host replication zcjtest 192.168.79.115/24 trust
host zcjtest zcjtest 192.168.79.115/24 trust
三、搭建
3.1、源端
\h create publication
create publication zcjyd for table zcjschema.test;
3.2、目的端
\h create subscription
create subscription zcjmdd connection 'host=192.168.79.115 port=5438 user=zcjtest database=postgres' publication zcjyd;
3.3、检查
select * from pg_publication;
select * from pg_subscription;
源端插入,查看行数
目的端查看行数