standby parallel backup

From: Aniko Belim <belim(dot)aniko(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: standby parallel backup
Date: 2015-03-13 21:26:00
Message-ID: CAFuQUMet2o=sQ7j_L-S_oXURj2wRrK3r+uKAySLnW8Y321EbVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message John Scalia 2015-03-13 22:24:14 Re: standby parallel backup
Previous Message Achilleas Mantzios 2015-03-13 17:05:04 Re: Samsung SSD 850 PRO 1T : any good for PostgreSQL?