Re: standby parallel backup - Mailing list pgsql-admin

From Aniko Belim
Subject Re: standby parallel backup
Date
Msg-id CAFuQUMcVx-mTdvD8mGvru2-mK9gNBPAaGWwS75A8PN4FNfvCeA@mail.gmail.com
Whole thread Raw
In response to Re: standby parallel backup  (John Scalia <jayknowsunix@gmail.com>)
List pgsql-admin
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@gmail.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
>


pgsql-admin by date:

Previous
From: "Ruth Melendo"
Date:
Subject: pgpool + BDR, is it possible?
Next
From: "Ruth Melendo"
Date:
Subject: how to recovery your database when stops replicating