本篇介紹PostgreSQL logical replication設定方式，此功能是於 PostgreSQL 10之後才提供
Step 1. 調整Publication wal_level的設定
[pgadm@pgrep1 dbdata]$ vim postgresql.conf wal_level = logical [pgadm@pgrep1 dbdata]$ pg_ctl restart -D $PGDATA
Step 2. 建立Subscription PG主機，並啟用資料庫
[pgadm@pgrep3 ~]$ initdb /pgdata/dbdata/
The files belonging to this database system will be owned by user "pgadm".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pgdata/dbdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Taipei
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pgdata/dbdata/ -l logfile start
[pgadm@pgrep3 ~]$ pg_ctl start -D $PGDATA
waiting for server to start....2022-03-25 17:22:29.408 CST [30644] LOG: starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
2022-03-25 17:22:29.410 CST [30644] LOG: listening on IPv6 address "::1", port 5699
2022-03-25 17:22:29.410 CST [30644] LOG: listening on IPv4 address "127.0.0.1", port 5699
2022-03-25 17:22:29.413 CST [30644] LOG: listening on Unix socket "/tmp/.s.PGSQL.5699"
2022-03-25 17:22:29.416 CST [30645] LOG: database system was shut down at 2022-03-25 17:22:20 CST
2022-03-25 17:22:29.419 CST [30644] LOG: database system is ready to accept connections
done
server started
Step 3. 在Publication端，建立測試用的schema
[pgadm@pgrep1 ~]$ psql -d postgres postgres=# create user repuser password 'password'; CREATE ROLE postgres=# create database repdb; CREATE DATABASE [pgadm@pgrep1 ~]$ psql -U repuser -d repdb repdb=> create table t1 (c1 int primary key, c2 varchar(200)); CREATE TABLE repdb=> insert into t1 select generate_series(1,1000), random()::text; INSERT 0 1000 repdb=> select count(*) from t1; count ------- 1000 (1 row)
Step 4. 建立Publication；一般user無法建立，需要superuser
[pgadm@pgrep1 ~]$ psql -U repuser -d repdb repdb=> create publication pub_primary for table t1; ERROR: permission denied for database repdb [pgadm@pgrep1 ~]$ psql -d repdb repdb=# create publication pub_primary for table t1; CREATE PUBLICATION repdb=# select * from pg_publication; oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot -------+-------------+----------+--------------+-----------+-----------+-----------+-------------+------------ 16469 | pub_primary | 10 | f | t | t | t | t | f (1 row)
Step 5. 建立Subscription，建立過程中一樣需要superuser，遠端的指定對象也需要superuser，如果建立sbuscription，不需要帶入data的話，在建立結尾可以帶入 with (copy_data=false) 參數
[pgadm@pgrep3 ~]$ psql -d repdb postgres=# create database repdb; CREATE DATABASE postgres=# create user repuser password 'password'; CREATE ROLE postgres=# \c repdb repuser You are now connected to database "repdb" as user "repuser". repdb=> create subscription sub_standby connection 'host=172.30.0.25 port=5699 user=repuser password=password dbname=repdb' publication pub_primary; ERROR: must be superuser to create subscriptions postgres=# \c repdb pgadm You are now connected to database "repdb" as user "pgadm". repdb=# create subscription sub_standby connection 'host=172.30.0.25 port=5699 user=repuser password=password dbname=repdb' publication pub_primary; ERROR: could not connect to the publisher: connection to server at "172.30.0.25", port 5699 failed: FATAL: must be superuser or replication role to start walsender repdb=# create subscription sub_standby connection 'host=172.30.0.25 port=5699 user=pgadm password=password dbname=repdb' publication pub_primary; ERROR: relation "public.t1" does not exist repdb=# create table t1 (c1 int primary key, c2 varchar(200)); CREATE TABLE repdb=# create subscription sub_standby connection 'host=172.30.0.25 port=5699 user=pgadm password=password dbname=repdb' publication pub_primary; NOTICE: created replication slot "sub_standby" on publisher CREATE SUBSCRIPTION repdb=# select count(*) from t1; count ------- 1000 (1 row)
Step 6. 確認replication狀態
repdb=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start
| backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_la
g | replay_lag | sync_priority | sync_state | reply_time
--------+----------+---------+------------------+-------------+-----------------+-------------+-----------------------
--------+--------------+-----------+------------+------------+------------+------------+-----------------+------------
-----+-----------------+---------------+------------+-------------------------------
155327 | 16385 | repmgr | pgrep1 | 172.30.0.21 | | 33982 | 2022-03-28 17:53:22.02
7058+08 | | streaming | 0/2A13A4A8 | 0/2A13A4A8 | 0/2A13A4A8 | 0/2A13A4A8 | 00:00:00.000335 | 00:00:00.00
1062 | 00:00:00.001136 | 0 | async | 2022-03-28 21:45:20.807647+08
166586 | 10 | pgadm | sub_standby | 172.30.0.23 | | 42880 | 2022-03-28 21:45:05.07
6164+08 | | streaming | 0/2A13A4A8 | 0/2A13A4A8 | 0/2A13A4A8 | 0/2A13A4A8 | |
| | 0 | async | 2022-03-28 21:45:15.133727+08
(2 rows)
repdb=# select * from pg_stat_replication_slots;
slot_name | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total
_bytes | stats_reset
-------------+------------+-------------+-------------+-------------+--------------+--------------+------------+------
-------+-------------
sub_standby | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 |
(1 row)
Step 7. 在Publication測試新增資料
--Publication
repdb=# insert into t1 values (1001, 'AAAAAAAAAAA');
INSERT 0 1
--Subscription
repdb=# select count(*) from t1;
count
-------
1001
(1 row)
合併repmgr failover + logical replication測試
Step 1. 停掉node1讓repmgr failover到node2，然後在node2上檢查cluster狀態
[pgadm@pgrep1 ~]$ pg_ctl stop waiting for server to shut down.... done server stopped [pgadm@pgrep2 ~]$ repmgr -f /pgdata/repmgr.conf cluster show ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string ----+--------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------- 1 | pgrep1 | primary | - failed | ? | default | 100 | | host=pgrep1 user=repmgr dbname=repmgr connect_timeout=2 2 | pgrep2 | primary | * running | | default | 100 | 18 | host=pgrep2 user=repmgr dbname=repmgr connect_timeout=2 WARNING: following issues were detected - unable to connect to node "pgrep1" (ID: 1) HINT: execute with --verbose option to see connection error messages
Step 2. node1 rejoin cluster
[pgadm@pgrep1 ~]$ repmgr node rejoin -f /pgdata/repmgr.conf -d 'host=pgrep2 user=repmgr dbname=repmgr' --force-rewin --verbose NOTICE: using provided configuration file "/pgdata/repmgr.conf" NOTICE: rejoin target is node "pgrep2" (ID: 2) INFO: prerequisites for using pg_rewind are met INFO: 0 files copied to "/tmp/repmgr-config-archive-pgrep1" NOTICE: executing pg_rewind DETAIL: pg_rewind command is "/pgbin/pghome_1/bin/pg_rewind -D '/pgdata/dbdata' --source-server='host=pgrep2 user=repmgr dbname=repmgr connect_timeout=2'" NOTICE: 0 files copied to /pgdata/dbdata INFO: directory "/tmp/repmgr-config-archive-pgrep1" deleted NOTICE: setting node 1's upstream to node 2 WARNING: unable to ping "host=pgrep1 user=repmgr dbname=repmgr connect_timeout=2" DETAIL: PQping() returned "PQPING_NO_RESPONSE" NOTICE: starting server using "/pgbin/pghome_1/bin/pg_ctl -w -D '/pgdata/dbdata' start" INFO: node "pgrep1" (ID: 1) is pingable INFO: node "pgrep1" (ID: 1) has attached to its upstream node NOTICE: NODE REJOIN successful DETAIL: node 1 is now attached to node 2
Step 3. 檢查Publication端，會發現只剩下repmgr在同步，而logical replication則未帶起
repdb=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin |
state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |
reply_time
--------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+---
--------+------------+------------+------------+------------+-----------------+-----------------+-----------------+---------------+------------+-
------------------------------
334561 | 16385 | repmgr | pgrep2 | 172.30.0.22 | | 54666 | 2022-03-28 21:49:00.971726+08 | | st
reaming | 0/2A14B470 | 0/2A14B470 | 0/2A14B470 | 0/2A14B470 | 00:00:00.000314 | 00:00:00.001367 | 00:00:00.001385 | 0 | async |
2022-03-28 21:49:35.310866+08
(1 row)
Step 4. 檢查Subscription端，發現是停下來的狀態
repdb=# select * from pg_stat_subscription ; subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | late st_end_time -------+-------------+-----+-------+--------------+--------------------+-----------------------+----------------+----- ------------ 16392 | sub_standby | | | | | | | (1 row)
Step 5. 在Publication端，建立replication slot，名稱一定要跟subscription相同
repdb=# select * from pg_create_logical_replication_slot('sub_standby','pgoutput');
slot_name | lsn
-------------+------------
sub_standby | 0/2A14FC90
(1 row)
Step 6. 檢查Subscription端，重新檢查狀態，則會發現連結成功
repdb=# select * from pg_stat_subscription ; subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time -------+-------------+-------+-------+--------------+-------------------------------+-------------------------------+- ---------------+------------------------------- 16392 | sub_standby | 45796 | | 0/2A15EC40 | 2022-03-28 21:54:42.557422+08 | 2022-03-28 21:54:36.880612+08 | 0/2A15EC40 | 2022-03-28 21:54:42.557422+08 (1 row)
Step 7. 重新在Publication端新增資料測試
-- Publication repdb=# select count(*) from t1; count ------- 1003 (1 row) repdb=# insert into t1 values (1004, 'AQZAAAA'); INSERT 0 1 -- Subscription repdb=# select count(*) from t1; count ------- 1004 (1 row)
Node. logical replication 指令
# 只能查到目前資料庫的publication，無法全域查到
repdb=# select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+-------------+----------+--------------+-----------+-----------+-----------+-------------+------------
16473 | pub_primary | 10 | f | t | t | t | t | f
(1 row)
# 查看目前同步開啟的狀況
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin |
state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |
reply_time
--------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+---
--------+------------+------------+------------+------------+-----------------+-----------------+-----------------+---------------+------------+-
------------------------------
334561 | 16385 | repmgr | pgrep2 | 172.30.0.22 | | 54666 | 2022-03-28 21:49:00.971726+08 | | st
reaming | 0/2A4E3200 | 0/2A4E3200 | 0/2A4E3200 | 0/2A4E3200 | 00:00:00.000245 | 00:00:00.001466 | 00:00:00.001559 | 0 | async |
2022-03-29 10:22:20.803616+08
334757 | 10 | pgadm | sub_standby | 172.30.0.23 | | 43016 | 2022-03-28 21:53:56.610511+08 | | st
reaming | 0/2A4E3200 | 0/2A4E3200 | 0/2A4E3200 | 0/2A4E3200 | | | | 0 | async |
2022-03-29 10:22:21.84889+08
(2 rows)
# 查看目前已建立的subscription數量
postgres=# select * from pg_subscription;
oid | subdbid | subname | subowner | subenabled | subbinary | substream | subconnin
fo | subslotname | subsynccommit | subpublications
-------+---------+-------------+----------+------------+-----------+-----------+--------------------------------------
--------------------------------+-------------+---------------+-----------------
16392 | 16385 | sub_standby | 10 | t | f | f | host=172.30.0.25 port=5699 user=pgadm
password=1qaz@WSX dbname=repdb | sub_standby | off | {pub_primary}
(1 row)
# 查看目前subscription同步的狀況 (如果只有subid subname有資料，其它為空時，則表示未與publication連接，需要檢查publication的slot狀況)
repdb=# select * from pg_stat_subscription ;
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time |
latest_end_lsn | latest_end_time
-------+-------------+-------+-------+--------------+-------------------------------+-------------------------------+-
---------------+-------------------------------
16392 | sub_standby | 45796 | | 0/2A4E7D30 | 2022-03-29 10:24:18.845943+08 | 2022-03-29 10:24:12.856639+08 |
0/2A4E7D30 | 2022-03-29 10:24:18.845943+08
(1 row)
# 查看目前publication開啟的slots通道
postgres=# select * from pg_stat_replication_slots ;
slot_name | spill_txns | spill_count | spill_bytes | stream_txns | stream_count | stream_bytes | total_txns | total_bytes | stats_reset
-------------+------------+-------------+-------------+-------------+--------------+--------------+------------+-------------+-------------
sub_standby | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 140 |
(1 row)
# 如果slots通道因pg某些原故未開啟，則需要手動開啟
# slot_name 需依 subscription.subname 來設定
select * from pg_create_logical_replication_slot('slot_name','pgoutput');
參考來源：
Postgresql Replication slot error postgresql
Postgres 10 的 Logical Replication 操作筆記
0 留言