Hi,
On 2015-03-27 09:47:57 +0000, pet.slavov@gmail.com wrote:
> I am trying to use logical decoding to replay the data modifying queries on
> a different server, to synchronize some of the tables. The primary server
> has some load, but not so much. I am getting the changes with
> pg_logical_slot_get_changes limiting the changes to 50 at a time.
That's generally not a very good idea. It's much better to use the
streaming interface. Repeatedly starting/stopping a logical slot (as the
SQL interface has to do every time) isn't all that efficient.
> At some point pg_logical_slot_get_changes queries become slow and starts to
> eat all the ram and swap, which eventually kills the primary database with
> this error:
> FATAL: the database system is in recovery mode.
That indicates a crash. Please check the server log for any details? If
it crashes, could you perhaps get a backtrace?
What output plugin are you using?
> The nature of changes on the primary can have a lot of data in one
> transaction. Which I guess is the reason of the slow
> pg_logical_slot_get_changes.
In that case changes should just be spooled to disk.
Greetings,
Andres Freund