测试逻辑复制
本节我们将正式在Aurora和本机Postgres上建立逻辑复制
为了区分发布者和订阅者的命令行，将使用以下名称进行区分：
rds-master=>– 代表publisher， 即Aurora Postgresself-managed-replica=#– 代表subscriber，即本地的Postgres
在publisher上创建数据表
在进行逻辑复制之前，先在publisher上创建一张数据表table_before_publication：
使用Cloud 9连接到Aurora Postgres，并执行以下命令：
rds-master=> create table table_before_publication (id1 int, id2 int);
CREATE TABLE
rds-master=> insert into table_before_publication values (1,2);
INSERT 0 1
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
目前在publisher上还没有replication slots。
为postgres数据库里的所有表创建一个发布（publication）：
rds-master=> CREATE PUBLICATION alltables FOR ALL TABLES;
CREATE PUBLICATION
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
新启动另一个终端，连接到本地的Postgres，创建一个订阅(subscription)：
self-managed-replica=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres host=pg1.cxxxxxbz.us-east-1.rds.amazonaws.com user=postgres password=pxxxxxd' PUBLICATION alltables;
ERROR: relation "public.table_before_publication" does not exist
此时会报错relation "public.table_before_publication" does not exist，因为我们的订阅者里面并没有提前创建好该表。
创建public.table_before_publication表，注意表结构中可以添加新的列，并不一定和发布者的表结构完全匹配（订阅者的表结构可以有多余的列，在同步数据时，这些列被设置填充为默认值）：
self-managed-replica=# create table table_before_publication(placeholder_col1 char(2), id1 int, placeholder_col2 char(2), id2 bigint);
CREATE TABLE
self-managed-replica=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres host=pg1.cxxxxxz.us-east-1.rds.amazonaws.com user=postgres password=pxxxxxd' PUBLICATION alltables;
NOTICE: created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
成功创建订阅后，publisher会将表中已有的数据传输到subscriber，并建立replication_slots：
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn |
wal_status | safe_wal_size
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+
------------+---------------
mysub | pgoutput | logical | 14360 | postgres | f | t | 403 | | 805242 | 0/AE508C0 | 0/AE521E0 |
reserved |
检验数据是否可以即时复制：
rds-master=> select * from table_before_publication;
id1 | id2
-----+-----
1 | 2
(1 row)
self-managed-replica=# select * from table_before_publication; # 自搭数据库上成功同步表中的初始数据
placeholder_col1 | id1 | placeholder_col2 | id2
------------------+-----+------------------+-----
| 1 | | 2
(1 row)
rds-master=> insert into table_before_publication values (2,3); # 在Aurora中新插入一条数据
INSERT 0 1
self-managed-replica=# select * from table_before_publication; # 在自建数据库上随即同步到该数据。 `placeholder_col1` 和 `placeholder_col2` 这两列被自动填充进去了空值。
placeholder_col1 | id1 | placeholder_col2 | id2
------------------+-----+------------------+-----
| 1 | | 2
| 2 | | 3
(2 rows)
查看publisher的状态：
rds-master=> select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | b
ackend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_prio
rity | sync_state | reply_time
-------+----------+----------+------------------------+---------------+-----------------+-------------+-------------------------------+--
------------+-----------+-----------+-----------+-----------+------------+----------------+-----------------+-----------------+----------
-----+------------+-------------------------------
17551 | 16399 | postgres | mysub | 172.31.37.135 | | 34614 | 2022-04-20 14:44:17.837555+00 |
| streaming | 0/BC0B848 | 0/BC0B848 | 0/BC0B848 | 0/BC0B848 | | | |
0 | async | 2022-04-21 08:37:17.325651+00
查看subscriber的状态：
self-managed-replica=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+--------------------------+------------+------------
16391 | table_before_publication | 57/80197D0 | r
(1 rows)
srsubstate 状态码有以下几种：
- i – Initialize,
- d – Data正在被复制(copying)
- s – Synchronized
- r – Ready