postgresql 10 pub/sub使用简明教程
1.初始化配置 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 恢复原发布数据 $ pg_restore -d subdb t1.dump 创建提交,不复制原存在数据,此时sub端启动一个logical replication worker, pub端创建一个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) 查看提交信息 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查看发布复制信息 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.插入增量发布记录 1).pubdb插入增量 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=# [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 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 [postgres@pgmdb01 ~]$ sequence增长为最新值8 CREATE SEQUENCE public.t1_id_seq INCREMENT 1 START 8 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1; 2).查看增量提交记录 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 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 [postgres@pgsubdb1 log]$ sequence为原始值5,不增长 CREATE SEQUENCE public.t1_id_seq INCREMENT 1 START 5 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1; 3.常见问题(特殊情景使用请参见官方文档) 当远程数据库无法连接时,drop SUBSCRIPTION # ALTER SUBSCRIPTION name DISABLE # alter subscription sub_measurement set (slot_name=none); # drop subscription sub_measurement; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |