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,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',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',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很有可能具备这一功能。

问: 你好肖恩。 对于订阅节点中的分区表，是否存在在复制情况下利用更新元组的解决方案？ 如果有许多已分区的表，则可能不足以找到所需的元组，因为表通常不是由唯一主键分区的。

谢谢你帮忙。