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

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