Thread: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 15323 Logged by: Phil Endecott Email address: spam_from_pgsql_lists@chezphil.org PostgreSQL version: 9.6.10 Operating system: Debian Stretch Description: I have set up a replication system that uses a combination of WAL segment archiving and streaming. If the slave goes down for a period, when it restarts it will initially fetch WAL segment files from the archive using restore_command. After fetching the last of these it will start streaming directly from the master. It seems that the WAL segment files may have a small empty space at the end due presumably to the size of the records. In my case, the last archived WAL segment ended at offset 0xFFF088. The slave therefore tried to start streaming at that position. But the master had already archived that segment and recycled it, because I had not set wal_keep_segments and it defaults to 0. Streaming therefore failed to start. It seems to me that wal_keep_segments must always be >= 1 for the combination of WAL archiving and streaming to work. If this understanding is correct, the fix could be some combination of: (a) Make the default for wal_keep_segments 1. (b) Make the default for wal_keep_segments 1 if archiving is enabled. (c) Make the WAL restore process skip over this end-of-segment gap and start streaming at the start of the master's current segment. (d) Document that wal_keep_segments must be >= 1 in this case. (The current docs in section 2.26.5 say that "if you set up a WAL archive that's accessible from the standby, these solutions [meaning wal_keep_segments and replication slots] are not required".) See also: https://www.postgresql.org/message-id/flat/1534100972118%40dmwebmail.dmwebmail.chezphil.org Thanks, Phil.
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Andres Freund
Date:
Hi, On 2018-08-13 14:37:00 +0000, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 15323 > Logged by: Phil Endecott > Email address: spam_from_pgsql_lists@chezphil.org > PostgreSQL version: 9.6.10 > Operating system: Debian Stretch > Description: > > I have set up a replication system that uses a combination of WAL segment > archiving and streaming. > If the slave goes down for a period, when it restarts it will initially > fetch WAL segment files from the archive using restore_command. After > fetching the last of these it will start streaming directly from the > master. > It seems that the WAL segment files may have a small empty space at the end > due presumably to the size of the records. In my case, the last archived > WAL segment ended at offset 0xFFF088. The slave therefore tried to start > streaming at that position. But the master had already archived that > segment and recycled it, because I had not set wal_keep_segments and it > defaults to 0. Streaming therefore failed to start. But it'll retry after failing back to archiving, no? > It seems to me that wal_keep_segments must always be >= 1 for the > combination of WAL archiving and streaming to work. You can also use replication slots, which is usually the better alternative: https://www.postgresql.org/docs/current/static/warm-standby.html#STREAMING-REPLICATION-SLOTS Greetings, Andres Freund
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
"Phil Endecott"
Date:
Hi Andres, Andres Freund wrote: > On 2018-08-13 14:37:00 +0000, PG Bug reporting form wrote: >> The following bug has been logged on the website: >> >> Bug reference: 15323 >> Logged by: Phil Endecott >> Email address: spam_from_pgsql_lists@chezphil.org >> PostgreSQL version: 9.6.10 >> Operating system: Debian Stretch >> Description: >> >> I have set up a replication system that uses a combination of WAL segment >> archiving and streaming. >> If the slave goes down for a period, when it restarts it will initially >> fetch WAL segment files from the archive using restore_command. After >> fetching the last of these it will start streaming directly from the >> master. >> It seems that the WAL segment files may have a small empty space at the end >> due presumably to the size of the records. In my case, the last archived >> WAL segment ended at offset 0xFFF088. The slave therefore tried to start >> streaming at that position. But the master had already archived that >> segment and recycled it, because I had not set wal_keep_segments and it >> defaults to 0. Streaming therefore failed to start. > > But it'll retry after failing back to archiving, no? It will retry and keep failing indefinitely if the master is idle. If the master is not idle, it will eventually archive the next segment which the slave will restore. In my case, poking it to make that happen did cause streaming to start successfully - but in general, I think it's still possible that the master will remove the segment that is needed to start streaming. Regards, Phil.
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving + streaming to work
From
Andres Freund
Date:
On August 13, 2018 4:59:32 PM GMT+02:00, Phil Endecott <spam_from_pgsql_lists@chezphil.org> wrote: but in general, I think it's still >possible >that the master will remove the segment that is needed to start >streaming. That's why I told you about replication slots.... -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Stephen Frost
Date:
Greetings, * Andres Freund (andres@anarazel.de) wrote: > On August 13, 2018 4:59:32 PM GMT+02:00, Phil Endecott <spam_from_pgsql_lists@chezphil.org> wrote: > but in general, I think it's still > >possible > >that the master will remove the segment that is needed to start > >streaming. > > That's why I told you about replication slots.... This should really work even without replication slots though. Thanks! Stephen
Attachment
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Andres Freund
Date:
On 2018-08-13 11:22:11 -0400, Stephen Frost wrote: > Greetings, > > * Andres Freund (andres@anarazel.de) wrote: > > On August 13, 2018 4:59:32 PM GMT+02:00, Phil Endecott <spam_from_pgsql_lists@chezphil.org> wrote: > > but in general, I think it's still > > >possible > > >that the master will remove the segment that is needed to start > > >streaming. > > > > That's why I told you about replication slots.... > > This should really work even without replication slots though. Why? I fail to see what'd be gained by adding "always retain one segment" rule. It'd not make the setup any more reliable. If anything it'd make it harder to spot issues in test setups. - Andres
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Stephen Frost
Date:
Andres, * Andres Freund (andres@anarazel.de) wrote: > On 2018-08-13 11:22:11 -0400, Stephen Frost wrote: > > Greetings, > > > > * Andres Freund (andres@anarazel.de) wrote: > > > On August 13, 2018 4:59:32 PM GMT+02:00, Phil Endecott <spam_from_pgsql_lists@chezphil.org> wrote: > > > but in general, I think it's still > > > >possible > > > >that the master will remove the segment that is needed to start > > > >streaming. > > > > > > That's why I told you about replication slots.... > > > > This should really work even without replication slots though. > > Why? I fail to see what'd be gained by adding "always retain one > segment" rule. It'd not make the setup any more reliable. If anything > it'd make it harder to spot issues in test setups. What exactly is wrong with the setup where this should be failing? Thanks! Stephen
Attachment
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Andres Freund
Date:
Hi, On 2018-08-13 11:42:47 -0400, Stephen Frost wrote: > > > This should really work even without replication slots though. > > > > Why? I fail to see what'd be gained by adding "always retain one > > segment" rule. It'd not make the setup any more reliable. If anything > > it'd make it harder to spot issues in test setups. > > What exactly is wrong with the setup where this should be failing? If you want to rely on archiving, you either need to be ok with arbitrary delays in low activity periods, or use archive timeout. If you want to rely on streaming, you need an appropriate WAL retention policy, i.e. wal_keep_segments or replication slots. The setup at hand does doesn't want arbitrary delay in archiving situations but doesn't use archive_timeout and it retain the necessary WAL for streaming. Greetings, Andres Freund
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Andres Freund
Date:
On 2018-08-13 08:55:45 -0700, Andres Freund wrote: > The setup at hand does doesn't want arbitrary delay in archiving > situations but doesn't use archive_timeout and it retain the necessary > WAL for streaming. * doesn't retain
Re: BUG #15323: wal_keep_segments must be >= 1 for WAL archiving +streaming to work
From
Stephen Frost
Date:
Greetings, * Andres Freund (andres@anarazel.de) wrote: > On 2018-08-13 11:42:47 -0400, Stephen Frost wrote: > > > > This should really work even without replication slots though. > > > > > > Why? I fail to see what'd be gained by adding "always retain one > > > segment" rule. It'd not make the setup any more reliable. If anything > > > it'd make it harder to spot issues in test setups. > > > > What exactly is wrong with the setup where this should be failing? > > If you want to rely on archiving, you either need to be ok with > arbitrary delays in low activity periods, or use archive timeout. > > If you want to rely on streaming, you need an appropriate WAL retention > policy, i.e. wal_keep_segments or replication slots. > > The setup at hand does doesn't want arbitrary delay in archiving > situations but doesn't use archive_timeout and it retain the necessary > WAL for streaming. The setup doesn't rely on *only* archiving or *only* streaming though- it's set up specifically to work with both and what's happening is that PG is failing to ensure that, when both are used, a replica is able to fully catch up and follow a primary, and there's zero excuse for that as far as I'm concerned. We document explicitly that it should work and it does in almost all cases except this one, and not because we don't know what it'd take to make it work but simply because we fail to account for the primary possibly having removed a WAL file that a replica following the archive will want to start from because that's the last WAL file which was archived. I don't think there's any particular concern about arbitrary delay in archiving situations nor does it have any need for archive_timeout to be set in this case. Setting an archive_timeout likely would also have "solved" this issue, but, again, not because an archive_timeout really needs to be set but because it'd end up papering over this issue. Instead, having archive_timeout would have resulted in a lot of additional WAL ending up being archived than would be actually necessary. I simply don't buy off on these excuses that we're doing the right thing here because there's ways to get around it by using wal_keep_segments or setting archive_timeout or replication slots or just hoping that more data will be written to force another WAL out which will let the replica get a bit farther ahead and then be able to connect to the primary and get access to a WAL file that's still available and just hasn't happened to be removed yet. This could happen when building out a new replica too, btw, as far as I can tell- just restore from a backup and then let the system replay from the WAL archive until it gets to the last WAL segment there and have it try to connect to the primary- if the primary has removed that last WAL segment already and has moved on to the next WAL segment and isn't generating much WAL itself. Thanks! Stephen