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

Did you try increasing max_locks_per_transaction as the hint message says?
what's your current settings for both max_locks_per_transaction and
shared_buffers?

On Thu, Apr 30, 2020 at 5:15 PM Viral Shah <viralshah009(at)gmail(dot)com> wrote:

> 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
>>
>>
>>

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/