Message-ID: <5503638E.1090601@gmail.com>
Date: Fri, 13 Mar 2015 18:24:14 -0400
From: John Scalia <jayknowsunix@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: Aniko Belim <belim.aniko@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: standby parallel backup
References: 
 <CAFuQUMet2o=sQ7j_L-S_oXURj2wRrK3r+uKAySLnW8Y321EbVA@mail.gmail.com>
In-Reply-To: 
 <CAFuQUMet2o=sQ7j_L-S_oXURj2wRrK3r+uKAySLnW8Y321EbVA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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