CODEWITHSUNDEEP
postgresqlreplicationlogical-replication
wishcaster
wishcaster

Reputation: 43

PostgreSQL Large Table Logical Replication Infinite Sync

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.

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.

Upvotes: 1

Views: 1580

Answers (2)

unlike777
unlike777

Reputation: 9

The problem is exactly the same Check the logs I found the following error:

ERROR:  could not receive data from WAL stream: ERROR:  canceling statement due to statement timeout

Due to large tables, replication fell off by timeout By increasing the timeouts, the problem went away

PS Ideally, it would be cooler to set up separate timeouts for replication and for the main base.

Upvotes: 0

jjanes
jjanes

Reputation: 44305

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.

Upvotes: 3

Related Questions