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

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