Sample Header Ad - 728x90

PG_DUMP on Replica DB server Error

2 votes
1 answer
2047 views
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'
Asked by ntdrv (21 rep)
Jul 26, 2018, 10:11 AM
Last activity: Jan 19, 2025, 08:04 PM