PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup).

All working good. However for development/debugging purposes I may need to create simple dump and copy it to localhost to inspect.

On master server I can simply use pg_dump tool. But I don't want to use it under heavy production load. So I was thinking - is there a way to create dump from our backup server?

Only one way I can think about: apply barman recover command to different server (not production), then there create simple dump via pg_dump. That will work, but isn't there a way to ditch intermediate server?

  • You can use pg_dump on the standby provided it is configured as a "hot standby" – a_horse_with_no_name Dec 18 '19 at 10:12

No, you can only run pg_dump against a running PostgreSQL server.

You can avoid the necessity to recover the database whenever you need a pg_dump by setting up a streaming replication hot standby server with max_standby_streaming_delay = -1 and run your pg_dump against that.

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.