pglogical and Postgres 10 Partitions

前端之家收集整理的这篇文章主要介绍了pglogical and Postgres 10 Partitions前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

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

谢谢你帮忙。

原文链接:https://www.f2er.com/postgresql/193399.html

猜你在找的Postgre SQL相关文章