Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.