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