Re: standby parallel backup

Hi,

We need to backup individual databases, sometimes just schemas. That's why
we use pg_dump.

Aniko

On Fri, Mar 13, 2015 at 5:24 PM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> Is there some reason that you can't use pg_basebackup? It's much faster
> than pg_dump, like a couple of orders of magnitude. It also allows you to
> specify how many threads to want
> to speed it up.
>
> On 3/13/2015 5:26 PM, Aniko Belim wrote:
> > Hi,
> >
> > We are currently backing up our PostgreSQL 9.4 standby databases with
> pg_dump using file method. Now we need to change the method to using
> directory backup with parallel option
> > to make the backup faster.
> >
> > But we get the following error:
> > ERROR: cannot assign TransactionIds during recovery
> > STATEMENT: SELECT pg_export_snapshot()
> >
> > I assume that the problem occurs because it want to use
> export_snapshot() to ensure that all parallel connections of the dump will
> see the same snapshot, but can't because the
> > standby is read-only.
> >
> > Is there a workaround for this problem?
> > We found the --no-synchronized-snapshots option of pg_dump for a
> possible solution, which allows the backup to succeed, but I'm concerned
> about the consistency of the backup. I
> > did some test with it, and it looks like I only need to ensure that no
> changes made while the connections of the parallel threads occur, not
> during the whole backup (which could
> > cause a large lag in replication). Would it be safe to pause and resume
> replication only for that few seconds?
> >
> > Or is there any other good solution for backing up hot standby with
> pg_dump using the parallel directory method while this bug fixed? (We found
> this error reported in 2013, and
> > it looks like isn't fixed yet.)
> >
> > Thank you!
> > Aniko
> >
>
>