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

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