2

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()

Questions:

  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'
Improve this question
5
  • What are the options passed to pg_dump ?
    – Daniel Vérité
    Commented Jul 26, 2018 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%
    – ntdrv
    Commented Jul 26, 2018 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
    – András Váczi
    Commented Jul 26, 2018 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.
    – ntdrv
    Commented Jul 27, 2018 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.
    – ntdrv
    Commented Jul 27, 2018 at 7:24

1 Answer 1

Reset to default
0

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 and acknowledge you have read our privacy policy.

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