Hello,
I have a setup with a rather busy and semi-large primary Postgres server
which uses physical streaming replication to a secondary server. The
secondary server is to be used for read-only queries (reporting), and
I've starting running pg_dumps towards the secondary.
I now run into error messages like the following when pg_dump works
against the secondary:
pg_dump: error: Dumping the contents of table "sometable" failed:
PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR: canceling statement
due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be
removed.
pg_dump: detail: Command was: COPY someschema.sometable (col1, col2,
col3) TO stdout;
I have tried setting
max_standby_streaming_delay = 5min
(Default is 30s.) That doesn't help.
I'm wondering: Should I further raise max_standby_streaming_delay, for
example to 30min?
Or should I set hot_standby_feedback to "on"?
https://postgresqlco.nf/doc/en/param/hot_standby_feedback/ recommends it
stay off for my use case: "Turn it off for a replica which does
long-running reports and is allowed to lag." But having heard
https://postgres.fm/episodes/hot_standby_feedback I get the impression
hot_standby_feedback should almost always be on when using streaming
replication.
Would it make sense with a combination, i.e.
setting max_standby_streaming_delay to 10 minutes and
activating hot_standby_feedback?
--
Kind regards,
Troels Arvin
