Hi Andres,
I am writing again because I did some more testing to see what was the
used resources.
Now I am more sure that this is more a memory leak then anything else.
I extended the swap and the disk space on the server that I was doing
the testing.
Before I start I can see that the pg_xlog directory is 7.2 GB size.
This give me some idea that the size of the changes cannot be much
bigger than that.
After I start ti get the transactions changes one by one with select *
from pg_logical_slot_get_changes('<slot name>', null, 1),
on one of this statements the process eats all the ram and some space on
the disk and fails again.
This is the eaten resources:
- 18 GB RAM + swap - 0 free
- 12 GB in base/pgsql_tmp in the data directory
Again this is comparing with the 7.2 GB in the pg_xlog directory ...
Maybe I am not understanding something, but is this normal? To my
knowledge the data in pg_xlog is not compressed,
so the size cannot just increase like this ?
Best Regards,
Peter Slavov/
/
Ðа 31.03.2015 в 17:40, Andres Freund напиÑа:
> 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
