A brief tutorial on using postgresql 10 pub/sub
1. Initialize configuration
1) .pubdb
Postgres=# create database pubdb
Postgres=#\ c pubdb
You are now connected to database "pubdb" as user "postgres".
Pubdb=# create table T1 (id bigserial primary key, name varchar (20))
CREATE TABLE
Pubdb=# create publication pub_mdb1_t1 for table t1
CREATE PUBLICATION
Pubdb=#
Pubdb=# select * from pg_publication
Pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-+-
Pub_mdb1_t1 | 10 | f | t | t | t
(1 row)
Pubdb=#
Pubdb=# insert into T1 (name) values ('Peter'), (' Chris'), ('Jasmine'), (' Jeans'), ('Willam')
INSERT 0 5
Pubdb=# select * from T1
Id | name
-- +-
1 | Peter
2 | Chris
3 | Jasmine
4 | Jeans
5 | Willam
(5 rows)
Pubdb=#
Pubdb=#
Pubdb=#\ Q
[postgres@pgmdb01 ~] $
2). Subdb
$createdb subdb
Restore the original published data
$pg_restore-d subdb t1.dump
Create a submission without replicating the existing data, and the subside starts a logical replication worker at this time
Create a pg_replication_slot on the pub side
$psql subdb
Subdb=# CREATE SUBSCRIPTION sub_mdb1_t1
CONNECTION 'host=172.16.3.228 port=5432 user=postgres password=123456 dbname=pubdb'
PUBLICATION pub_mdb1_t1
WITH (copy_data = false)
NOTICE: created replication slot "sub_mdb1_t1" on publisher
CREATE SUBSCRIPTION
Subdb=#
Subdb=#
Subdb=#
Subdb=# select * from T1
Id | name
-- +-
1 | Peter
2 | Chris
3 | Jasmine
4 | Jeans
5 | Willam
(5 rows)
View submission information
Subdb=#\ dRs+
List of subscriptions
-[RECORD 1]-+
Name | sub_mdb1_t1
Owner | postgres
Enabled | t
Publication | {pub_mdb1_t1}
Synchronous commit | off
Conninfo | host=172.16.3.228 port=5432 user=postgres password=123456 dbname=pubdb
Subdb=#\ dRs
List of subscriptions
-[RECORD 1]-
Name | sub_mdb1_t1
Owner | postgres
Enabled | t
Publication | {pub_mdb1_t1}
Subdb=#
3) .pubdb view publication replication information
Pubdb=#
Pubdb=# select * from pg_stat_replication
-[RECORD 1]-+--
Pid | 16501
Usesysid | 10
Usename | postgres
Application_name | sub_mdb1_t1
Client_addr | 172.16.3.230
Client_hostname |
Client_port | 52682
Backend_start | 2017-10-10 14 5918.46971558
Backend_xmin |
State | streaming
Sent_lsn | 0/F036E90
Write_lsn | 0/F036E90
Flush_lsn | 0/F036E90
Replay_lsn | 0/F036E90
Write_lag |
Flush_lag |
Replay_lag |
Sync_priority | 0
Sync_state | async
Pubdb=# select * from pg_replication_slots
-[RECORD 1]-+-
Slot_name | sub_mdb1_t1
Plugin | pgoutput
Slot_type | logical
Datoid | 26203
Database | pubdb
Temporary | f
Active | t
Active_pid | 16501
Xmin |
Catalog_xmin | 604
Restart_lsn | 0/F036E58
Confirmed_flush_lsn | 0/F036E90
two。 Insert incremental release record
1) .pubdb insert increment
Pubdb=# pubdb=# insert into T1 (name) values ('Zeng'), (' Feng'), ('Mia')
INSERT 0 3
Pubdb=#\ x
Expanded display is off.
Pubdb=# select * from T1
Id | name
-- +-
1 | Peter
2 | Chris
3 | Jasmine
4 | Jeans
5 | Willam
6 | Zeng
7 | Feng
8 | Mia
(8 rows)
Pubdb=#
Pubdb=# select * from pg_stat_replication
-[RECORD 1]-+--
Pid | 16501
Usesysid | 10
Usename | postgres
Application_name | sub_mdb1_t1
Client_addr | 172.16.3.230
Client_hostname |
Client_port | 52682
Backend_start | 2017-10-10 14 5918.46971558
Backend_xmin |
State | streaming
Sent_lsn | 0/F0372B8
Write_lsn | 0/F0372B8
Flush_lsn | 0/F0372B8
Replay_lsn | 0/F0372B8
Write_lag |
Flush_lag |
Replay_lag |
Sync_priority | 0
Sync_state | async
Pubdb=# select * from pg_replication_slots
-[RECORD 1]-+-
Slot_name | sub_mdb1_t1
Plugin | pgoutput
Slot_type | logical
Datoid | 26203
Database | pubdb
Temporary | f
Active | t
Active_pid | 16501
Xmin |
Catalog_xmin | 605
Restart_lsn | 0/F037280
Confirmed_flush_lsn | 0/F0372B8
Pubdb=#
[postgres@pgmdb01 ~] $ps-fU postgres
UID PID PPID C STIME TTY TIME CMD
Postgres 15874 15873 0 12:44 pts/2 00:00:00-bash
Postgres 15950 10 12:50? 00:00:00 / opt/pgsql/10.0/bin/postmaster-D / pgdata10
Postgres 15951 15950 0 12:50? 00:00:00 postgres: logger process
Postgres 15953 15950 0 12:50? 00:00:00 postgres: checkpointer process
Postgres 15954 15950 0 12:50? 00:00:00 postgres: writer process
Postgres 15955 15950 0 12:50? 00:00:00 postgres: wal writer process
Postgres 15956 15950 0 12:50? 00:00:00 postgres: autovacuum launcher process
Postgres 15957 15950 0 12:50? 00:00:00 postgres: archiver process
Postgres 15958 15950 0 12:50? 00:00:00 postgres: stats collector process
Postgres 15959 15950 0 12:50? 00:00:00 postgres: bgworker: logical replication launcher
Postgres 15961 15960 0 12:50 pts/1 00:00:00-bash
Postgres 16077 15874 0 13:08 pts/2 00:00:00 tail-f postgresql-Tue.log
Postgres 16082 15950 0 13:10? 00:00:00 postgres: postgres pubdb 172.16.3.223 (56608) idle
Postgres 16083 15950 0 13:10? 00:00:00 postgres: postgres pubdb 172.16.3.223 (56610) idle
Postgres 16501 15950 0 14:59? 00:00:00 postgres: wal sender process postgres 172.16.3.230 (52682) idle
Postgres 16543 15961 0 15:09 pts/1 00:00:00 ps-fU postgres
[postgres@pgmdb01 ~] $
Sequence grows to the latest value of 8
CREATE SEQUENCE public.t1_id_seq
INCREMENT 1
START 8
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1
2)。 View incremental submission record
Subdb
Subdb=# select * from T1
Id | name
-- +-
1 | Peter
2 | Chris
3 | Jasmine
4 | Jeans
5 | Willam
6 | Zeng
7 | Feng
8 | Mia
(8 rows)
Subdb=#
[postgres@pgsubdb1 log] $ps-fU postgres
UID PID PPID C STIME TTY TIME CMD
Postgres 935 10 10:28? 00:00:01 / opt/pgsql/10.0/bin/postmaster-D / pgdata10
Postgres 1001 935 0 10:28? 00:00:00 postgres: logger process
Postgres 1005 935 0 10:28? 00:00:00 postgres: checkpointer process
Postgres 1006 935 0 10:28? 00:00:00 postgres: writer process
Postgres 1007 935 0 10:28? 00:00:01 postgres: wal writer process
Postgres 1008 935 0 10:28? 00:00:00 postgres: autovacuum launcher process
Postgres 1009 935 0 10:28? 00:00:00 postgres: archiver process
Postgres 1010 935 010: 28? 00:00:00 postgres: stats collector process
Postgres 1011 935 0 10:28? 00:00:00 postgres: bgworker: logical replication launcher
Postgres 1084 1083 0 10:28 pts/0 00:00:00-bash
Postgres 15551 15550 0 11:48 pts/1 00:00:00-bash
Postgres 16206 16205 0 14:40 pts/2 00:00:00-bash
Postgres 16276 1084 0 14:46 pts/0 00:00:00 psql subdb
Postgres 16277 935 0 14:46? 00:00:00 postgres: postgres subdb [local] idle
Postgres 16324 16206 0 14:56 pts/2 00:00:00 psql subdb
Postgres 16325 935 0 14:56? 00:00:00 postgres: postgres subdb [local] idle
Postgres 16332 935 0 14:59? 00:00:00 postgres: bgworker: logical replication worker for subscription 24626
Postgres 16374 15551 0 15:11 pts/1 00:00:00 ps-fU postgres
[postgres@pgsubdb1 log] $
Sequence is the original value of 5, does not grow
CREATE SEQUENCE public.t1_id_seq
INCREMENT 1
START 5
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1
3. Frequently asked questions (see official documentation for special situations)
When the remote database cannot connect, drop SUBSCRIPTION
# ALTER SUBSCRIPTION name DISABLE
# alter subscription sub_measurement set (slot_name=none)
# drop subscription sub_measurement
