max_standby_streaming_delay or hot_standby_feedback

Поиск
Список
Период
Сортировка
Искать

max_standby_streaming_delay or hot_standby_feedback

От:
Troels Arvin <troels@arvin.dk>
Дата:
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






Re: max_standby_streaming_delay or hot_standby_feedback

От:
Jan Karremans <karremans.ja@gmail.com>
Дата:
Hi Troels,

The question is how long your pg_dump run takes, as that is the duration you need to have the delay set to. After the delay runs out, the system will start applying WAL again, and to do that, it will end all queries that run against the system/tables to allow for the apply to take place.
Alternatively, you can indeed activate hot_standby_feedback, which will sort of do the same, and retain WAL on the primary, both cases require space monitoring to ensure you continue to have enough free space for the accumulating WAL.

Cheers,
Jan

> On 29 Oct 2026, at 10:45, Troels Arvin  wrote:
> 
> 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
> 
> 
> 
> 
> 
> 



Re: max_standby_streaming_delay or hot_standby_feedback

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Thu, 2026-10-29 at 10:45 +0100, Troels Arvin wrote:
> 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"? 

If these are reporting queries, delaying replay is fine.
So set "max_standby_streaming_delay" to -1, that will avoid query cancellation.
Don't enable "hot_standby_feedback", otherwise reporting queries on the standby
can bloat tables on your primary.

Yours,
Laurenz Albe


Re: max_standby_streaming_delay or hot_standby_feedback

От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Troels,

You are hitting something called a recovery conflict. This means a read query on the replica conflicts with incoming WAL changes from the primary on certain objects.

There are 3 main types of conflicts:

Snapshot conflict: The primary's VACUUM removes tuple versions that a transaction on the replica still needs to see. If the replica replays that VACUUM-related WAL record, the read transaction would no longer see the correct data. Postgres must either pause WAL replay or cancel the read query. Enabling hot_standby_feedback mitigates most of these cases because the primary delays vacuuming tuple versions visible to the standby.

Lock conflict: The primary requires an AccessExclusiveLock on an object (usually a table), typically during DDL operations or the table-truncation phase of a VACUUM. If a read query is actively accessing that table, replay cannot proceed.

Buffer pin conflict: An operation on the primary needs an exclusive cleanup lock (sometimes called a super-exclusive lock) on a specific buffer page—most often during b-tree or heap index vacuuming. while a query on the replica holds a pin on that buffer (common during heavy nested loop joins).

In all of these cases, Postgres tries to balance replication lag against read query execution. This is where max_standby_streaming_delay comes into play. If set too low, your queries will frequently be canceled. If set too high, replication lag will increase. If set to -1 (wait indefinitely), you run the risk of the primary accumulating excess WAL until it exceeds wal_keep_size or invalidates the replication slot via max_slot_wal_keep_size, which would break replication entirely. 

This is a trade off to consider.

Thanks,
Shihao
FAQ