pglogical and Postgres 10 Partitions
https://blog.2ndquadrant.com/pg-phriday-pglogical-postgres-10-partitions/
在旧金山举行的2017年邮电公开赛(PostGresOpen2017)上，有人来到2 ndQuadant摊位，和我聊了起来。在我们无耻地讨论数据库机制时，他问我pgLogic是否支持新的PostGres 10个分区。考虑到我在所有方面的专业知识，我以适当的方式回答：
“”我不知道。我得调查一下。“。
嗯，经过一些实验，我有了一个更具体的答案，它是令人放心的积极的。
问题:
给定提供者节点上的表，是否可以仅捕获插入通信量，以便将其累加到订阅系统上进行存档？这是一种相当常见的策略，它允许一个活动的OLTP系统定期清除旧数据，而一个报告OLAP系统在事后保持其可用性。
要使这个实验进行下去，首先必须有一个可能适合这个模型的常规表格。
CREATE TABLE sensor_log (
id SERIAL PRIMARY KEY NOT NULL,
location VARCHAR NOT NULL,
reading BIGINT NOT NULL,
reading_date TIMESTAMP NOT NULL
);
INSERT INTO sensor_log (location, reading, reading_date)
SELECT s.id % 1000, round(random() * 100),
CURRENT_DATE + INTERVAL '1d' - ((s.id * 10)::TEXT || 's')::INTERVAL
FROM generate_series(1, 1000000) s(id);
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
node_name := 'prod_sensors',
dsn := 'host=localhost port=5434 dbname=phriday'
);
SELECT pglogical.create_replication_set(
set_name := 'logging',
replicate_insert := TRUE, replicate_update := FALSE,
replicate_delete := FALSE, replicate_truncate := FALSE
);
SELECT pglogical.replication_set_add_table(
set_name := 'logging', relation := 'sensor_log',
synchronize_data := TRUE
);
给定提供者节点上的表，是否可以仅捕获插入通信量，以便将其累加到订阅系统上进行存档？这是一种相当常见的策略，它允许一个活动的OLTP系统定期清除旧数据，而一个报告OLAP系统在事后保持其可用性。
要使这个实验进行下去，首先必须有一个可能适合这个模型的常规表格。
概念证明
正确配置了提供程序后，剩下的就是设置订阅服务器节点。这与设置提供程序节点非常类似：创建表、安装pgLogic、创建订阅。我们现在可以这样做：
CREATE TABLE sensor_log (
id INT PRIMARY KEY NOT NULL,
location VARCHAR NOT NULL,
reading BIGINT NOT NULL,
reading_date TIMESTAMP NOT NULL
);
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
node_name := 'sensor_warehouse',
dsn := 'host=localhost port=5435 dbname=phriday'
);
SELECT pglogical.create_subscription(
subscription_name := 'wh_sensor_data',
replication_sets := array['logging'],
provider_dsn := 'host=localhost port=5434 dbname=phriday'
);
SELECT pg_sleep(10);
SELECT COUNT(*) FROM sensor_log;
COUNT
---------
1000000
再一次，我们在谨慎方面犯了错误，手工做了几件不一定完全必要的事情。我们的意思是在subsriber节点上手动创建传感器_LOG表。
CREATESOVING函数有一个名为SynchronizStructure的参数，可以跳过表创建步骤。另一方面，它使用pg_dump获取表结构DDL，因此如果收件人数据库不是空的，则导入可能失败。我们完全不用参数就可以跳过整个舞蹈。
一旦我们核实了一百万个样本行已经转移，我们的工作就完成了，对吗？
重新开始。
差不多吧。还有时间去幻想。虽然我们已经证明只捕获插入的数据是可能的，但PostGres 10表分区在这种关系中仍然是一个未知数。事实证明，它们在幕后的实现是一个非常相关的细节。
为了了解如何做，我们需要删除订阅并将收件人表放在订阅服务器上：
SELECT pglogical.drop_subscription(
subscription_name := 'wh_sensor_data'
);
DROP TABLE sensor_log;
别担心，我们的传感器日志表会回来的，比以前更好。
接着看
我们只在提供程序节点的传感器_日志副本中插入了一百万行。事实证明，我们生成的日期甚至不能从2017年起算。不过，这很好，因为使用PostGres 10分区，即使一个分区也足以演示这个过程。
让我们从一个由read_date列分区的表开始：
CREATE TABLE sensor_log (
id SERIAL,
location VARCHAR NOT NULL,
reading BIGINT NOT NULL,
reading_date TIMESTAMP NOT NULL
)
PARTITION BY RANGE (reading_date);
CREATE TABLE sensor_log_part_2017
PARTITION OF sensor_log
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE UNIQUE INDEX udx_sensor_log_2017_sensor_log_id ON sensor_log_part_2017 (sensor_log_id);
CREATE INDEX idx_sensor_log_2017_location ON sensor_log_part_2017 (location);
CREATE INDEX idx_sensor_log_2017_date ON sensor_log_part_2017 (reading_date);
我们对无法使用类似语法复制根表上的任何占位符索引感到遗憾，但这可能会出现在PostGres 11或12中。无论如何，我们现在有一个分区表，由一个分区支持。
开始
这就是乐趣的开始！我们所需要做的就是重新创建订阅，传感器_LOG表数据应该被重定向到2017分区，从而证明使用PostGres 10分区可以实现pgLogic。
让我们试试看：
SELECT pglogical.create_subscription(
subscription_name := 'wh_sensor_data',
replication_sets := array['logging'],
provider_dsn := 'host=localhost port=5434 dbname=phriday'
);
SELECT pg_sleep(10);
SELECT COUNT(*) FROM sensor_log;
COUNT
-------
0
SELECT pglogical.drop_subscription(
subscription_name := 'wh_sensor_data'
);
等等，这是怎么回事？为什么这张表一点都没有被复制？让我们看看日志上写着什么…
2017-09-18 14:36:03.065 CDT [4196] LOG: starting receiver for subscription wh_sensor_data
2017-09-18 14:36:03.111 CDT [4196] ERROR: pglogical target reation "public.sensor_log" is not a table
哦。
深渊回头看。
很巧，PostGres分区表实际上不是表。它们更像是一个类似于表的结构，允许某些数据库操作针对底层分区。我们甚至可以通过查看pg_class系统目录表亲自看到这一点：
SELECT relname, relkind
FROM pg_class
WHERE relname LIKE 'sensor_log%';
relname | relkind
----------------------+---------
sensor_log | p
sensor_log_id_seq | S
sensor_log_part_2017 | r
relkind列告诉我们要看的是哪种类型的对象。PostGres中的普通表通常标记为“r”表示关系。但是，订阅服务器上的传感器_日志表显示分区表的“p”。这实际上很重要，因为只有关系才能存储数据。当pgLogic看到分区表不是关系时，它拒绝继续。
PgLogic拒绝插入sensor_LOG的决定不是唯一的。如果我们尝试使用PostGres 10的新发布/订阅逻辑复制系统，我们将得到同样的结果。甚至PostGres 10内置的逻辑复制也不能与分区表兼容；它们太新了。
一条出路。
尽管实现细节造成了一些非直觉的障碍，但有一种方法可以解决这个问题：我们作弊。与PostGres 10内置的逻辑复制不同，pgLogic公开了高级API挂钩。其中之一是PostGresServer编程接口。
逻辑解码的默认行为是尝试并匹配PostGres内部对象，以防止结构不兼容。因此，重要的是sensor_log不是一个关系；它最终是临时的，不能存储相同的数据。
但是，如果pgLogic可以将逻辑解码转换为文字插入语句，那该怎么办？嗯，pg逻辑文档告诉我们，我们可以通过在postgresql.conf中设置这些参数来做到这一点：
pglogical.conflict_resolution = false
pglogical.use_spi = true
第一个禁用冲突解决。我们不需要在订阅者上这样做，因为它只是接收到一连串的插入。然后我们启用SPI进程，它将逻辑解码直接转换为实际的INSERT语句。
回到现实。
如果我们再次尝试订阅，应该会看到预期的结果：
SELECT pglogical.create_subscription(
subscription_name := 'wh_sensor_data',
replication_sets := array['logging'],
provider_dsn := 'host=localhost port=5434 dbname=phriday'
);
SELECT pg_sleep(10);
SELECT COUNT(*) FROM sensor_log;
COUNT
---------
1000000
SELECT COUNT(*) FROM sensor_log_part_2017;
COUNT
---------
1000000
因此，不仅分区系统有效，我们也不需要像以前尝试实现这个模型那样使用触发器。PostGres 10就像广告上说的那样工作，在撰写本文的时候它还是一个测试版。
虽然不幸的是，我们不得不跳过几个奇怪的圈圈到达预定的目的地，但我们仍然完好无损地到达了目的地。更重要的是，我们可以看到，虽然PostGres 10提供了内部逻辑复制，但它仍然是一个不断发展的功能，还没有完全完成。
PostGres 11、12和未来的版本将随着补丁的合并慢慢地填补这些漏洞。同时，PgLogic将继续利用扩展系统来添加PostGres核心还没有准备好吸收的高级特性。实际上，将逻辑复制重定向到分区是一种有点高级的用例。
我一直很喜欢PostGres扩展系统；用pg_logical之类的东西来增强PostGres的功能，可以确保即使是困难的边缘情况也有一个可行的解决方案。
问: 肖恩。 因此，您展示了如何通过在postgresql.conf中修改以下设置，使pg逻辑扩展能够在PG10中工作： pglogical.conflict_resolution = false pglogical.use_spi = true
在PG 10中是否有一种“内部/内置逻辑复制”方法来处理分区表？
答： 嗨伊戈尔。
我不知道有什么方法可以用于内置的逻辑复制.。正如我在文章中提到的，作为功能的第一次迭代，它是相对简单的。不过，PostGres 11很有可能具备这一功能。
问: 你好肖恩。 对于订阅节点中的分区表，是否存在在复制情况下利用更新元组的解决方案？ 如果有许多已分区的表，则可能不足以找到所需的元组，因为表通常不是由唯一主键分区的。
谢谢你帮忙。
1408
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
