I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it.

I need to run pg_dump from replica although I face with error. What do I do:

  1. SELECT pg_xlog_replay_pause()
  2. Run pg_dump on replica server
  3. SELECT pg_xlog_replay_resume()

Immediately I receive after pg_xlog_replay_pause()

ERROR:  recovery is not in progress

And when the command of pg_dump starts I see:

pg_dump: [archiver (db)] query failed: ERROR:  cannot assign TransactionIds during recovery
pg_dump: [archiver (db)] query was: SELECT pg_export_snapshot()


  1. Does it mean I can't do pg_dump from replica? I have a feeling that in case of wal streaming replication it would be possible. Correct me if I'm wrong.

  2. I can't find any information for SELECT pg_xlog_replay_pause() / SELECT pg_xlog_replay_resume() works only with wal streaming setup. Can someone tell if this true?

Thanks in advance.

Customer's setup is: They copy xlog to network share and replica recovery.conf consist of

standby_mode = 'on'
restore_command = 'if exist A:\\Logs\\From_Master_DB\\%f (copy A:\\Logs\\From_master_DB\\%f  %p) else (exit /b 1)'
archive_cleanup_command = '"C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  A:\\Logs\\From_Master_DB %r && "C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  D:\\PG-SQL\\data\\pg_xlog %r'
recovery_target_timeline = 'latest'
  • What are the options passed to pg_dump ? – Daniel Vérité Jul 26 '18 at 12:33
  • Hi Daniel, I try a directory type of backup: pg_dump" -w -h localhost -U USER -Z3 -Fd -j 12 -f %BACKUP_PATH%\%DUMPFILE_DB% %DB_NAME% – net_diver Jul 26 '18 at 13:24
  • With streaming replication, you can definitely do a pg_dump on a replica. I am not sure how it looks like without. Also, the pair of functions can be used in both modes, as postgresql.org/docs/9.5/static/functions-admin.html suggests. But in the first place: why are you pausing replication? pg_dump is guaranteed to produce a consistent backup: postgresql.org/docs/9.6/static/app-pgdump.html – dezso Jul 26 '18 at 13:47
  • Hello dezso, thanks for reply. First, my understanding is based on this doc: postgresql.org/docs/current/static/… If I understand it right, any operation in this case it's a copy command is a transaction which potentially can affect on applying xlog on the replica. So by pausing the xlog apply, - there won't be any disruption of pg_dump process, I might be wrong here though. As for the pg_dump, thanks for the link, I will check it right away for better understanding. – net_diver Jul 27 '18 at 7:24
  • Second, I was also surprised by the behaviour of the fucntions, it runs fine, but I do not receive 't' or 'f' result, although pg_is_xlog_replay_paused() returns the correct status. – net_diver Jul 27 '18 at 7:24

Parallel pg_dump against a replica is not supported cleanly until v10.

You can either drop the -j, run the dump against the master rather than the replica, or you can use --no-synchronized-snapshots which will make your backup be potentially inconsistent.

Pausing the recovery might cure the consistency problem with use of --no-synchronized-snapshots, but the pause must be run on the replica, not on the master, which is what you seem to doing.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.