From: John Scalia <jayknowsunix@gmail.com>
To: Aniko Belim <belim.aniko@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: standby parallel backup
Date: Fri, 13 Mar 2015 18:24:14 -0400
Message-ID: <5503638E.1090601@gmail.com> (raw)
In-Reply-To: <CAFuQUMet2o=sQ7j_L-S_oXURj2wRrK3r+uKAySLnW8Y321EbVA@mail.gmail.com>
References: <CAFuQUMet2o=sQ7j_L-S_oXURj2wRrK3r+uKAySLnW8Y321EbVA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, belim.aniko@gmail.com
Subject: Re: standby parallel backup
In-Reply-To: <5503638E.1090601@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox