PostgreSQL pub/sub using a concise tutorial

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