Re: BUG #12910: Memory leak with logical decoding - Mailing list pgsql-bugs

На 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.
Actually I tried with pg_recvlogical and result was the same.
>> 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?
I will try to get a backtrace but from the logs I can see this:
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-1] 2015-03-30
10:59:57 GMT [20905]: [30924-1] user=postgres,db=sumup [local] WARNING:
57P02: terminating connection because of crash of another server process
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-2] 2015-03-30
10:59:57 GMT [20905]: [30925-1] user=postgres,db=sumup [local] DETAIL:
The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-3] 2015-03-30
10:59:57 GMT [20905]: [30926-1] user=postgres,db=sumup [local] HINT:  In
a moment you should be able to reconnect to the database and repeat your
command.
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-4] 2015-03-30
10:59:57 GMT [20905]: [30927-1] user=postgres,db=sumup [local] CONTEXT:
slot "peshka", output plugin "test_decoding", in the change callback,
associated LSN CC/833C4940
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-5] 2015-03-30
10:59:57 GMT [20905]: [30928-1] user=postgres,db=sumup [local]
LOCATION:  quickdie, postgres.c:2581
Mar 30 10:59:57 peshka-dev postgres[20905]: [15930-6] 2015-03-30
10:59:57 GMT [20905]: [30929-1] user=postgres,db=sumup [local]
STATEMENT:  select * from pg_logical_slot_get_changes('peshka', null, 1);

This happen of course just 1-2 minutes after all the ram and swap is
eaten from postgresql
> What output plugin are you using?
I tryed with test_decoding and decoder_raw from
https://github.com/michaelpq/pg_plugins
The result is the same.
>> 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