1

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
wishcaster
  • 43
  • 7
  • You say your database is fast-growing, so when you say it is 72GB, at what point in time was it 72GB? – jjanes Aug 28 '19 at 14:50

2 Answers2

2

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thank you, after removing indexes the logical replication worker has started to syncing table with high speed, and after a few hours sync was done. – wishcaster Aug 29 '19 at 07:56
1

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.

unlike777
  • 11
  • 2