PostgreSQL 大表逻辑复制无限同步 [英] PostgreSQL Large Table Logical Replication Infinite Sync

问题描述

我有一个大型且快速增长的 PostgreSQL 表(166GB 索引和 72GB 数据库).我想设置这个表的逻辑复制.双方均为 11.4 版.

I have a large and fast-growing PostgreSQL table (166gb Index and 72 GB database). And I want to set up a logical replication of this table. Version 11.4 on both sides.

我尝试这样做了 2 周，但我唯一拥有的是无限同步和增加副本上的表大小(已经 293 Gb 索引和 88 Gb 表，比原始表多，并且没有错误日志).我还尝试进行转储、恢复并开始同步 - 但现有主键出错.复制统计的 Backend_xmin 值每周更改一次，但同步状态仍为启动".这些服务器之间的网络连接根本没有使用(它们在同一个数据中心)，实际速度大约为 300-400Kb(看起来它主要是复制过程的流传输部分).

I'm trying to do it for 2 weeks, but the only thing I have is infinite syncing and growing table size on the replica (already 293 Gb index and 88Gb table, more than original, and there are no errors in the log). I also have tried to take a dump, restore it and start syncing - but got errors with existing primary keys. Backend_xmin value of replication stats is changing once in a week, but the sync state is still "startup". The network connection between those servers is not used at all (they are in the same datacenter), actual speed like 300-400Kb (looks like it's mostly streaming part of replication process).

所以问题是如何正确设置大且快速增长的表的逻辑复制，不知有没有可能?谢谢.

So the question is How to set up a Logical replication of large and fast-growing table properly, is it possible somehow? Thank you.

推荐答案

我尝试这样做了 2 周，但我唯一拥有的是无限同步和增加副本上的表大小(已经 293 Gb 索引和 88 Gb 表，比原始表多，并且没有错误日志).

I'm trying to do it for 2 weeks, but the only thing I have is infinite syncing and growing table size on the replica (already 293 Gb index and 88Gb table, more than original, and there are no errors in the log).

在同步完成之前删除副本上的非标识索引.

Drop the non-identity indexes on the replica until after the sync is done.

这篇关于PostgreSQL 大表逻辑复制无限同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！