RE: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

Hi Andreas,

Thank you for your suggestion about pausing/resuming the standby!

I've tried running on the standby
SELECT pg_xlog_replay_pause()
Then running the dump, and it threw the same error I had seen before.
Seeing that I wasn't able to do it, I just ran
SELECT pg_xlog_replay_resume()
On the slave.

I've asked in the IRC channel as well and I've been told that in version 10
this would work on standbys. I've looked at the code [3]
and indeed it does not have the check for standby anymore (as it did in the previous
gdb code listing in my initial email).

But.. upgrading to 10 would be quite a heavy thing for now, since it would involve many changes
so I don't believe that would be feasible right now.

[3] https://github.com/postgres/postgres/blob/REL_10_STABLE/src/bin/pg_dump/pg_dump.c#L1149

Stefan Petrea
System Engineer/DBA, Network Engineering

stefan(dot)petrea(at)tangoe(dot)com

tangoe.com

This e-mail message, including any attachments, is for the sole use of the intended recipient of this message, and may contain information that is confidential or legally protected. If you are not the intended recipient or have received this message in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Please notify the sender immediately by return e-mail and permanently delete this message and any attachments. Tangoe makes no warranty that this e-mail or its attachments are error or virus free.

-----Original Message-----
From: Andreas Kretschmer [mailto:andreas(at)a-kretschmer(dot)de]
Sent: Wednesday, March 28, 2018 8:26 AM
To: pgsql-general(at)lists(dot)postgresql(dot)org; Stefan Petrea <Stefan(dot)Petrea(at)tangoe(dot)com>; pgsql-general(at)postgresql(dot)org
Subject: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

WARNING: The sender of this email could not be validated and may not match the person in the "From" field. This is potentially a spam/phishing email! Contact IT if you need assistance.

On 28 March 2018 07:02:30 CEST, Stefan Petrea <Stefan(dot)Petrea(at)tangoe(dot)com> wrote:
>Hi,
>
>
>I wonder why synchronized snapshots are not supported on standby
>servers.
>
>
>
If you want to take backups using -Fd on a standby you should pause the reply-process. In this case you don' t need synchronized snapshots.

Regards, Andreas.

--
2ndQuadrant - The PostgreSQL Support Company