6-逻辑复制

逻辑复制

一般为异步，也可以用synchronous_standby_names设成同步

Publication
mkdir -p /pgccc/pgdata
chown -R postgres. /pgccc/pgdata
/usr/pgsql-14/bin/initdb -D /pgccc/pgdata

vi postgresql.conf
wal_level = logical
max_replication_slots=8
max_wal_senders=8
max_worker_processes=8

vi pg_hba.conf
host all repuser 0.0.0.0/0 md5

/usr/pgsql-14/bin/pg_ctl -D /pgccc/pgdata -l logfile start

–/usr/pgsql-14/bin/pg_ctl -D /pgccc/pgdata restart

关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service

psql

create user repuser replication login connection limit 10 encrypted password ‘repuser’;

create schema postgres;
create table postgres.test(id int4 primary key, name text);
insert into postgres.test values (1,‘a’);

CREATE PUBLICATION pub FOR TABLE postgres.test;

select * from pg_publication;

grant connect on database postgres to repuser;
grant usage on schema postgres to repuser;
grant select on test to repuser;
上面的不好用直接用这个
ALTER ROLE repuser WITH Superuser;

Subscription
create schema postgres;
create table postgres.test(id int4 primary key ,name text);

–create table postgres.test(id int4 ,name text);
–alter table postgres.test add primary key(id); --没有主键，数据同步将会报错

CREATE SUBSCRIPTION sub CONNECTION ‘host=172.16.40.104 port=5432 dbname=postgres user=repuser password=repuser’ PUBLICATION pub;

select * from pg_subscription;

Publication
insert into test values (2,‘b’);
delete from test where id=1;

select
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(),sent_lsn)) send_gap, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(),replay_lsn)) apply_gap from pg_stat_replication;

Subscription
select * from postgres.test;

select pg_size_pretty(pg_wal_lsn_diff(received_lsn,latest_end_lsn)) from pg_stat_subscription;