Re: pg_dump fails when a table is in ACCESS SHARE MODE

Hello Rene,

Apologies for the late reply. To answer your question, disabling
hot_standby_feedback doesn't help. Also, both my master and standby runs on
the same PostgreSQL Minor version 10.12. One other thing that I have tried
is to create a standalone server and try taking the backup of the same
database using pg_dump with parallel jobs set to 10. I make sure there are
no processes running on the database other than the autovacuum jobs.
Inspite of this, the dump fails with the following error:

pg_dump: [archiver (db)] query failed: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: [parallel archiver] a worker process died unexpectedly

Any suggestions would be appreciated. :)

Thanks
Viral Shah
Senior Data Analyst, Nodal Exchange LLC

On Tue, Feb 25, 2020 at 1:32 PM Rene Romero Benavides <
rene(dot)romero(dot)b(at)gmail(dot)com> wrote:

>
>
> On Tue, Feb 25, 2020 at 10:27 AM Rene Romero Benavides <
> rene(dot)romero(dot)b(at)gmail(dot)com> wrote:
>
>>
>> On Tue, Feb 25, 2020 at 9:45 AM Viral Shah <viralshah009(at)gmail(dot)com>
>> wrote:
>>
>>> Hello Rene,
>>>
>>> Since I am using pg_wal_replay_pause before my pg_dump, it doesn't throw
>>> any error. Instead, it waits for the resource to release the lock so that
>>> it can proceed with taking the dump. the hot_standby_feedback is set to
>>> *on*
>>> on my replica and primary.
>>>
>>> Thanks,
>>> Viral Shah
>>> Data Analyst, Nodal Exchange LLC
>>> viralshah009(at)gmail(dot)com | (240) 645 7548
>>>
>>>
>>> On Tue, Feb 25, 2020 at 10:36 AM Rene Romero Benavides <
>>> rene(dot)romero(dot)b(at)gmail(dot)com> wrote:
>>>
>>>>
>>>> On Tue, Feb 25, 2020 at 9:11 AM Viral Shah <viralshah009(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hello All,
>>>>>
>>>>> I am facing some problems while taking logical backup of my database
>>>>> using pg_dump. We have set up our infrastructure such that the logical
>>>>> backups are taken on our DR. Our intention here is to avoid substantial
>>>>> load on our primary server. We also pause and play wal files using
>>>>> pg_wal_replay_pause and pg_wal_replay_resume before and after pg_dump.
>>>>> However, at the time of pausing the wal replay, if there is a table under
>>>>> ACCESS SHARE MODE, the pg_dump cannot complete the backup.
>>>>>
>>>>> Can anyone suggest any better solution to take a logical backup using
>>>>> pg_dump where the table lock doesn't result in failure of the logical
>>>>> backup?
>>>>>
>>>>> PS: we are using PostgreSQL 10.12
>>>>>
>>>>> Thanks,
>>>>> Viral Shah
>>>>> Data Analyst, Nodal Exchange LLC
>>>>> viralshah009(at)gmail(dot)com | (240) 645 7548
>>>>>
>>>>
>>>> Hi Viral, what's the error pg_dump is throwing at you ? what's the
>>>> current setting of standby_feedback in your replica?
>>>> --
>>>> El genio es 1% inspiración y 99% transpiración.
>>>> Thomas Alva Edison
>>>>
>>>>
>>>> Nothing off the top of my head that could explain this behavior, if
>> possible, try disabling hot_standby_feedback on the replica (in the master
>> it has no effect) for a bit and see if that helps. Access share mode kind
>> of locks shouldn't interfere with pg_dump, are both master and slave the
>> exact same versions including the minor releases? it might be a bug, try
>> upgrading to the latests minor releases in both ends. Since when did you
>> start seeing this behavior, how long have you been on PG10 and creating
>> backups this way? any recent modification in configurations?
>> Since you're using PG10, you could try logical replication, it's more
>> reliable for this sort of thing, since the activity on the master doesn't
>> affect the slave lock-wise whatsoever.
>>
>> --
>> El genio es 1% inspiración y 99% transpiración.
>> Thomas Alva Edison
>>
>>
>>
> Also, check for the occurrence of long running transactions on the master.
> Does the replica serve as a data source for analytic applications (reports)
> ?
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
>
>
>