PostgreSQL pub/sub using a concise tutorial

Source: Internet
Author: User
Tags postgresql psql

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.