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 >
