1. Initialize the configuration
1). pubdb
postgres=# CREATE DATABASE pubdb;
postgres=# \c Pubdb
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
[Email protected] ~]$
2). subdb
$ createdb Subdb
Restore original Published Data
$ pg_restore-d subdb T1.dump
Create a commit without copying the original data, at which point a logical replication worker is started at the Sub end,
Pub side Create a Pg_replication_slot
$ 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:59:18.469715+08
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
2. Inserting an 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:59:18.469715+08
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=#
[Email protected] ~]$ 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 1 0 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
[Email protected] ~]$
Sequence growth to the latest value 8
CREATE SEQUENCE Public.t1_id_seq
INCREMENT 1
START 8
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
2). View incremental Commit Records
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=#
[Email protected] log]$ Ps-fu Postgres
UID PID PPID C stime TTY time CMD
Postgres 935 1 0 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 0 10: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
[Email protected] log]$
Sequence is the original value 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 scenario use )
When the remote database is unable to connect, drop SUBSCRIPTION
# ALTER SUBSCRIPTION name DISABLE
# ALTER subscription sub_measurement set (Slot_name=none);
# Drop Subscription sub_measurement;
This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1971194
PostgreSQL pub/sub using a concise tutorial