agora inbox for [email protected]
help / color / mirror / Atom feedRe: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
10+ messages / 6 participants
[nested] [flat]
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
@ 2024-11-24 17:15 Ron Johnson <[email protected]>
2024-11-24 19:54 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Christophe Pettus <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2024-11-24 17:15 UTC (permalink / raw)
To: pgsql-general
On Sun, Nov 24, 2024 at 12:06 PM Christophe Pettus <[email protected]> wrote:
> > On Nov 24, 2024, at 09:03, Subhash Udata <[email protected]> wrote:
> > When we shut down the standby, upgrade it, and then start it back up,
> will the replication automatically resume from the primary to the standby?
>
> Assuming that the standby has access to any WAL generated during the
> shutdown (either still in the primary's WAL directory, or via an archive
> using archive_command), yes. If you are not using a WAL archive using
> archive_command, you will want to make sure your wal_keep_size parameter is
> set high enough that required WAL segments aren't recycled during the
> standby's downtime.
Doesn't the existence of a replication slot force PG to retain WAL files
when replication is broken?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 17:15 Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
@ 2024-11-24 19:54 ` Christophe Pettus <[email protected]>
2024-11-24 21:00 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Christophe Pettus @ 2024-11-24 19:54 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
> On Nov 24, 2024, at 09:15, Ron Johnson <[email protected]> wrote:
>
> Doesn't the existence of a replication slot force PG to retain WAL files when replication is broken?
It does. I don't recall if the OP said that they were using a persistent replication slot or not; it's not as common with binary replication as with logical replication.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 17:15 Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-24 19:54 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Christophe Pettus <[email protected]>
@ 2024-11-24 21:00 ` Ron Johnson <[email protected]>
2024-11-24 21:58 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2024-11-24 21:00 UTC (permalink / raw)
To: pgsql-general
On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <[email protected]> wrote:
> > On Nov 24, 2024, at 09:15, Ron Johnson <[email protected]> wrote:
> >
> > Doesn't the existence of a replication slot force PG to retain WAL files
> when replication is broken?
>
> It does. I don't recall if the OP said that they were using a persistent
> replication slot or not; it's not as common with binary replication as with
> logical replication.
>
Really? I wonder why people fight with configuring max_wal_size and
wal_keep_size, when replication slots do all the work for you.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 17:15 Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-24 19:54 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Christophe Pettus <[email protected]>
2024-11-24 21:00 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
@ 2024-11-24 21:58 ` Adrian Klaver <[email protected]>
2024-11-24 22:15 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2024-11-24 21:58 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On 11/24/24 13:00, Ron Johnson wrote:
> On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <[email protected]
> <mailto:[email protected]>> wrote:
>
> > On Nov 24, 2024, at 09:15, Ron Johnson <[email protected]
> <mailto:[email protected]>> wrote:
> >
> > Doesn't the existence of a replication slot force PG to retain
> WAL files when replication is broken?
>
> It does. I don't recall if the OP said that they were using a
> persistent replication slot or not; it's not as common with binary
> replication as with logical replication.
>
>
> Really? I wonder why people fight with configuring max_wal_size and
> wal_keep_size, when replication slots do all the work for you.
https://www.postgresql.org/docs/current/logicaldecoding-explanation.html
"
Caution
Replication slots persist across crashes and know nothing about the
state of their consumer(s). They will prevent removal of required
resources even when there is no connection using them. This consumes
storage because neither required WAL nor required rows from the system
catalogs can be removed by VACUUM as long as they are required by a
replication slot. In extreme cases this could cause the database to shut
down to prevent transaction ID wraparound (see Section 24.1.5). So if a
slot is no longer required it should be dropped.
"
"
Caution
There is a chance that the old primary is up again during the promotion
and if subscriptions are not disabled, the logical subscribers may
continue to receive data from the old primary server even after
promotion until the connection string is altered. This might result in
data inconsistency issues, preventing the logical subscribers from being
able to continue replication from the new primary server.
"
https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION
"
Caution
Beware that replication slots can cause the server to retain so many WAL
segments that they fill up the space allocated for pg_wal.
max_slot_wal_keep_size can be used to limit the size of WAL files
retained by replication slots.
"
They have their issues also, namely they may not do all the work for you.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication
2024-11-24 17:15 Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-24 19:54 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Christophe Pettus <[email protected]>
2024-11-24 21:00 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-24 21:58 ` Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Adrian Klaver <[email protected]>
@ 2024-11-24 22:15 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Ron Johnson @ 2024-11-24 22:15 UTC (permalink / raw)
To: pgsql-general
On Sun, Nov 24, 2024 at 4:58 PM Adrian Klaver <[email protected]>
wrote:
> On 11/24/24 13:00, Ron Johnson wrote:
> > On Sun, Nov 24, 2024 at 2:55 PM Christophe Pettus <[email protected]
> > <mailto:[email protected]>> wrote:
> >
> > > On Nov 24, 2024, at 09:15, Ron Johnson <[email protected]
> > <mailto:[email protected]>> wrote:
> > >
> > > Doesn't the existence of a replication slot force PG to retain
> > WAL files when replication is broken?
> >
> > It does. I don't recall if the OP said that they were using a
> > persistent replication slot or not; it's not as common with binary
> > replication as with logical replication.
> >
> >
> > Really? I wonder why people fight with configuring max_wal_size and
> > wal_keep_size, when replication slots do all the work for you.
>
> https://www.postgresql.org/docs/current/logicaldecoding-explanation.html
>
> "
> Caution
>
> Replication slots persist across crashes and know nothing about the
> state of their consumer(s). They will prevent removal of required
> resources even when there is no connection using them. This consumes
> storage because neither required WAL nor required rows from the system
> catalogs can be removed by VACUUM as long as they are required by a
> replication slot. In extreme cases this could cause the database to shut
> down to prevent transaction ID wraparound (see Section 24.1.5). So if a
> slot is no longer required it should be dropped.
> "
>
Nagios has built-in disk space monitoring, and if it doesn't also have
built-in replication monitoring, you can write a plug-in. Or write your
own bash script that periodically runs "SELECT * from
pg_replication_slots;" and "SELECT * FROM pg_stat_replication;" on the
primary and "SELECT * FROM pg_stat_wal_receiver;" on the secondary.
Whichever you do, some monitoring should always be in place.
"
> Caution
>
> There is a chance that the old primary is up again during the promotion
> and if subscriptions are not disabled, the logical subscribers may
> continue to receive data from the old primary server even after
> promotion until the connection string is altered. This might result in
> data inconsistency issues, preventing the logical subscribers from being
> able to continue replication from the new primary server.
> "
>
Logical replication is off-topic for this problem, no?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?
@ 2026-06-18 11:57 hubert depesz lubaczewski <[email protected]>
2026-06-18 13:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Thom Brown <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2026-06-18 11:57 UTC (permalink / raw)
To: pgsql-general
Hi,
we have set of clusters that run on pg14 (yes, I know, we are in process
of upgrading but it's complicated).
Recently we noticed that some of the DR nodes are lagging because they
get stuck on some wal files, and starup process, in pg_stat_activity
shows "IPC:RecoveryConflictSnapshot" as wait event.
The thing is that there are no othger connections doing anything to the
db. There are some idle monitor ones, running things like count(*) from
pg_stat_activity, every now and then, but we're talking about pg being
stuck on single wal up to an hour or so.
Stuck as in:
ps shows:
postgres: 14/main: startup recovering 00000003000065EA000000C6 waiting
What could it be, how to fix it?
Best regards,
depesz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?
2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
@ 2026-06-18 13:07 ` Thom Brown <[email protected]>
2026-06-18 13:33 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Thom Brown @ 2026-06-18 13:07 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-general
On Thu, 18 Jun 2026 at 12:57, hubert depesz lubaczewski
<[email protected]> wrote:
>
> Hi,
> we have set of clusters that run on pg14 (yes, I know, we are in process
> of upgrading but it's complicated).
>
> Recently we noticed that some of the DR nodes are lagging because they
> get stuck on some wal files, and starup process, in pg_stat_activity
> shows "IPC:RecoveryConflictSnapshot" as wait event.
>
> The thing is that there are no othger connections doing anything to the
> db. There are some idle monitor ones, running things like count(*) from
> pg_stat_activity, every now and then, but we're talking about pg being
> stuck on single wal up to an hour or so.
>
> Stuck as in:
> ps shows:
> postgres: 14/main: startup recovering 00000003000065EA000000C6 waiting
>
> What could it be, how to fix it?
Have you got anything in the logs that say "conflict with recovery"?
Also, I know you are just running count(*), but is there a transaction
being left open?
SELECT pid, state, xact_start, query
FROM pg_stat_activity
ORDER BY xact_start NULLS LAST;
Any 'idle in transaction' for too long?
What is max_standby_streaming_delay and hot_standby_feedback set to?
Thom
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?
2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-18 13:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Thom Brown <[email protected]>
@ 2026-06-18 13:33 ` hubert depesz lubaczewski <[email protected]>
2026-06-19 07:06 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Laurenz Albe <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: hubert depesz lubaczewski @ 2026-06-18 13:33 UTC (permalink / raw)
To: Thom Brown <[email protected]>; +Cc: pgsql-general
On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote:
> Have you got anything in the logs that say "conflict with recovery"?
> Also, I know you are just running count(*), but is there a transaction
> being left open?
> SELECT pid, state, xact_start, query
> FROM pg_stat_activity
> ORDER BY xact_start NULLS LAST;
> Any 'idle in transaction' for too long?
> What is max_standby_streaming_delay and hot_standby_feedback set to?
No transactions were happening then. These are our "Disaster Recovery"
instances, and they are generally unused.
max_standby_streaming_delay is 10 minutes
hot_standby_feedback is on.
Please note that these are *NOT* doing streaming replication!
Best regards,
depesz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?
2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-18 13:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Thom Brown <[email protected]>
2026-06-18 13:33 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
@ 2026-06-19 07:06 ` Laurenz Albe <[email protected]>
2026-06-19 10:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2026-06-19 07:06 UTC (permalink / raw)
To: [email protected]; Thom Brown <[email protected]>; +Cc: pgsql-general
On Thu, 2026-06-18 at 15:33 +0200, hubert depesz lubaczewski wrote:
> On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote:
> > Have you got anything in the logs that say "conflict with recovery"?
> > Also, I know you are just running count(*), but is there a transaction
> > being left open?
> > SELECT pid, state, xact_start, query
> > FROM pg_stat_activity
> > ORDER BY xact_start NULLS LAST;
> > Any 'idle in transaction' for too long?
> > What is max_standby_streaming_delay and hot_standby_feedback set to?
>
> No transactions were happening then. These are our "Disaster Recovery"
> instances, and they are generally unused.
>
> max_standby_streaming_delay is 10 minutes
> hot_standby_feedback is on.
>
> Please note that these are *NOT* doing streaming replication!
Could it be bug #19490 [1]?
That would be fixed in the next minor releases.
Yours,
Laurenz Albe
[1]: https://postgr.es/m/flat/19490-9c59c6a583513b99%40postgresql.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ?
2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-18 13:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Thom Brown <[email protected]>
2026-06-18 13:33 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-19 07:06 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Laurenz Albe <[email protected]>
@ 2026-06-19 10:07 ` hubert depesz lubaczewski <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: hubert depesz lubaczewski @ 2026-06-19 10:07 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Thom Brown <[email protected]>; pgsql-general
On Fri, Jun 19, 2026 at 09:06:12AM +0200, Laurenz Albe wrote:
> On Thu, 2026-06-18 at 15:33 +0200, hubert depesz lubaczewski wrote:
> > On Thu, Jun 18, 2026 at 02:07:36PM +0100, Thom Brown wrote:
> > > Have you got anything in the logs that say "conflict with recovery"?
> > > Also, I know you are just running count(*), but is there a transaction
> > > being left open?
> > > SELECT pid, state, xact_start, query
> > > FROM pg_stat_activity
> > > ORDER BY xact_start NULLS LAST;
> > > Any 'idle in transaction' for too long?
> > > What is max_standby_streaming_delay and hot_standby_feedback set to?
> >
> > No transactions were happening then. These are our "Disaster Recovery"
> > instances, and they are generally unused.
> >
> > max_standby_streaming_delay is 10 minutes
> > hot_standby_feedback is on.
> >
> > Please note that these are *NOT* doing streaming replication!
>
> Could it be bug #19490 [1]?
> That would be fixed in the next minor releases.
Maybe it's related, but there are two issues:
1. it's not streaming replication
2. waitevent/type doesn't match. in my case it's
IPC/RecoveryConflictSnapshot
Best regards,
depesz
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-06-19 10:07 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-24 17:15 Re: Questions on Upgrading PostgreSQL from 15.0 to 15.9 and Setting Up Streaming Replication Ron Johnson <[email protected]>
2024-11-24 19:54 ` Christophe Pettus <[email protected]>
2024-11-24 21:00 ` Ron Johnson <[email protected]>
2024-11-24 21:58 ` Adrian Klaver <[email protected]>
2024-11-24 22:15 ` Ron Johnson <[email protected]>
2026-06-18 11:57 Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-18 13:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Thom Brown <[email protected]>
2026-06-18 13:33 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
2026-06-19 07:06 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? Laurenz Albe <[email protected]>
2026-06-19 10:07 ` Re: Why is WAL-file based replica stuck for long time (hour?) in IPC:RecoveryConflictSnapshot ? hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox