pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
max_standby_streaming_delay or hot_standby_feedback
4+ messages / 4 participants
[nested] [flat]

* max_standby_streaming_delay or hot_standby_feedback
@ 2026-10-29 09:45 Troels Arvin <troels@arvin.dk>
  2026-10-29 10:26 ` Re: max_standby_streaming_delay or hot_standby_feedback Jan Karremans <karremans.ja@gmail.com>
  2026-10-29 12:25 ` Re: max_standby_streaming_delay or hot_standby_feedback Laurenz Albe <laurenz.albe@cybertec.at>
  0 siblings, 2 replies; 4+ messages in thread

From: Troels Arvin @ 2026-10-29 09:45 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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










^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: max_standby_streaming_delay or hot_standby_feedback
  2026-10-29 09:45 max_standby_streaming_delay or hot_standby_feedback Troels Arvin <troels@arvin.dk>
@ 2026-10-29 10:26 ` Jan Karremans <karremans.ja@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Jan Karremans @ 2026-10-29 10:26 UTC (permalink / raw)
  To: Troels Arvin <troels@arvin.dk>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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 <troels@arvin.dk> 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
> 
> 
> 
> 
> 
> 







^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: max_standby_streaming_delay or hot_standby_feedback
  2026-10-29 09:45 max_standby_streaming_delay or hot_standby_feedback Troels Arvin <troels@arvin.dk>
@ 2026-10-29 12:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
  2026-10-29 14:06   ` Re: max_standby_streaming_delay or hot_standby_feedback shihao zhong <zhong950419@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Laurenz Albe @ 2026-10-29 12:25 UTC (permalink / raw)
  To: Troels Arvin <troels@arvin.dk>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: max_standby_streaming_delay or hot_standby_feedback
  2026-10-29 09:45 max_standby_streaming_delay or hot_standby_feedback Troels Arvin <troels@arvin.dk>
  2026-10-29 12:25 ` Re: max_standby_streaming_delay or hot_standby_feedback Laurenz Albe <laurenz.albe@cybertec.at>
@ 2026-10-29 14:06   ` shihao zhong <zhong950419@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: shihao zhong @ 2026-10-29 14:06 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Troels Arvin <troels@arvin.dk>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2026-10-29 14:06 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 09:45 max_standby_streaming_delay or hot_standby_feedback Troels Arvin <troels@arvin.dk>
2026-10-29 10:26 ` Jan Karremans <karremans.ja@gmail.com>
2026-10-29 12:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-29 14:06   ` shihao zhong <zhong950419@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox