Postgresql – PG_DUMP on Replica DB server Error

pg-dumppostgresqlpostgresql-9.4

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'

Best Answer

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.

Related Question