BUG #15323: wal_keep_segments must be >= 1 for WAL archiving + streaming to work

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

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.

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.

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

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

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

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

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

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