patch proposal

On 8/15/16 2:33 AM, Venkata B Nagothi wrote:

During the recovery process, It would be nice if PostgreSQL generates an
error by aborting the recovery process (instead of starting-up the
cluster) if the intended recovery target point is not reached and give
an option to DBA to resume the recovery process from where it exactly
stopped.

Thom wrote a patch [1]/messages/by-id/CAA-aLv4K2-9a+cvK75dkZkYD1etxpaH+9HC0vm9Ebw2up9Co2A@mail.gmail.com recently that gives warnings in this case. You
might want to have a look at that first.

The issue here is, if by any chance, the required WALs are not available
or if there is any WAL missing or corrupted at the restore_command
location, then PostgreSQL recovers until the end of the last available
WAL and starts-up the cluster.

You can use pause_at_recovery_target/recovery_target_action (depending
on your version) to prevent promotion. That would work for your stated
scenario but not for the scenario where replay starts (or the database
reaches consistency) after the recovery target.

[1]: /messages/by-id/CAA-aLv4K2-9a+cvK75dkZkYD1etxpaH+9HC0vm9Ebw2up9Co2A@mail.gmail.com
/messages/by-id/CAA-aLv4K2-9a+cvK75dkZkYD1etxpaH+9HC0vm9Ebw2up9Co2A@mail.gmail.com

--
-David
david@pgmasters.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Aug 16, 2016 at 2:50 AM, David Steele <david@pgmasters.net> wrote:

On 8/15/16 2:33 AM, Venkata B Nagothi wrote:

During the recovery process, It would be nice if PostgreSQL generates an
error by aborting the recovery process (instead of starting-up the
cluster) if the intended recovery target point is not reached and give
an option to DBA to resume the recovery process from where it exactly
stopped.

Thom wrote a patch [1] recently that gives warnings in this case. You
might want to have a look at that first.

That is good to know. Yes, this patch is about generating a more meaningful
output messages for recovery process, which makes sense.

The issue here is, if by any chance, the required WALs are not available
or if there is any WAL missing or corrupted at the restore_command
location, then PostgreSQL recovers until the end of the last available
WAL and starts-up the cluster.

You can use pause_at_recovery_target/recovery_target_action (depending
on your version) to prevent promotion. That would work for your stated
scenario but not for the scenario where replay starts (or the database
reaches consistency) after the recovery target.

The above said parameters can be configured to pause, shutdown or prevent
promotion only after reaching the recovery target point.
To clarify, I am referring to a scenario where recovery target point is not
reached at all ( i mean, half-complete or in-complete recovery) and there
are lots of WALs still pending to be replayed - in this situation,
PostgreSQL just completes the archive recovery until the end of the last
available WAL (WAL file "00000001000000000000001E" in my case) and
starts-up the cluster by generating an error message (saying
"00000001000000000000001F" not found).

Note: I am testing in PostgreSQL-9.5

LOG: restored log file "00000001000000000000001E" from archive
cp: cannot stat ‘/data/pgrestore9531/00000001000000000000001F’: No such
file or directory
LOG: redo done at 0/1EFFDBB8
LOG: last completed transaction was at log time 2016-08-15
11:04:26.795902+10

I have used the following recovery* parameters in the recovery.conf file
here and have intentionally not supplied all the WAL archives needed for
the recovery process to reach the target xid.

recovery_target_xid = xxxx,
recovery_target_inclusive = true
recovery_target_action = pause

It would be nice if PostgreSQL pauses the recovery in-case its not complete
(because of missing or corrupt WAL), shutdown the cluster and allows the
DBA to restart the replay of the remaining WAL Archive files to continue
recovery (from where it stopped previously) until the recovery target point
is reached.

Regards,
Venkata B N

Fujitsu Australia

On 8/16/16 1:08 AM, Venkata B Nagothi wrote:

The issue here is, if by any chance, the required WALs are not available
or if there is any WAL missing or corrupted at the restore_command
location, then PostgreSQL recovers until the end of the last available
WAL and starts-up the cluster.

You can use pause_at_recovery_target/recovery_target_action (depending
on your version) to prevent promotion. That would work for your stated
scenario but not for the scenario where replay starts (or the database
reaches consistency) after the recovery target.

The above said parameters can be configured to pause, shutdown or
prevent promotion only after reaching the recovery target point.
To clarify, I am referring to a scenario where recovery target point is
not reached at all ( i mean, half-complete or in-complete recovery) and
there are lots of WALs still pending to be replayed - in this situation,
PostgreSQL just completes the archive recovery until the end of the last
available WAL (WAL file "00000001000000000000001E" in my case) and
starts-up the cluster by generating an error message (saying
"00000001000000000000001F" not found).

Note: I am testing in PostgreSQL-9.5

LOG: restored log file "00000001000000000000001E" from archive
cp: cannot stat ‘/data/pgrestore9531/00000001000000000000001F’: No
such file or directory
LOG: redo done at 0/1EFFDBB8
LOG: last completed transaction was at log time 2016-08-15
11:04:26.795902+10

I have used the following recovery* parameters in the recovery.conf file
here and have intentionally not supplied all the WAL archives needed for
the recovery process to reach the target xid.

recovery_target_xid = xxxx,
recovery_target_inclusive = true
recovery_target_action = pause

It would be nice if PostgreSQL pauses the recovery in-case its not
complete (because of missing or corrupt WAL), shutdown the cluster and
allows the DBA to restart the replay of the remaining WAL Archive files
to continue recovery (from where it stopped previously) until the
recovery target point is reached.

OK, I see what you mean. I think it would be a good idea to work
through the various scenarios and define what Postgres would do in each
scenario before actually creating a patch.

--
-David
david@pgmasters.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Greetings,

* Venkata B Nagothi (nag1010@gmail.com) wrote:

The above said parameters can be configured to pause, shutdown or prevent
promotion only after reaching the recovery target point.
To clarify, I am referring to a scenario where recovery target point is not
reached at all ( i mean, half-complete or in-complete recovery) and there
are lots of WALs still pending to be replayed - in this situation,

PG doesn't know that there are still WALs to be replayed.

PostgreSQL just completes the archive recovery until the end of the last
available WAL (WAL file "00000001000000000000001E" in my case) and
starts-up the cluster by generating an error message (saying
"00000001000000000000001F" not found).

That's not a PG error, that's an error from cp. From PG's perspective,
your restore command has said that all of the WAL has been replayed.

If that's not what you want then change your restore command to return
an exit code > 125, which tells PG that it's unable to restore that WAL
segment.

It would be nice if PostgreSQL pauses the recovery in-case its not complete
(because of missing or corrupt WAL), shutdown the cluster and allows the
DBA to restart the replay of the remaining WAL Archive files to continue
recovery (from where it stopped previously) until the recovery target point
is reached.

Reaching the end of WAL isn't an error and I don't believe it makes any
sense to treat it like it is. You can specify any recovery target point
you wish, including ones that don't exist, and that's not an error
either.

I could see supporting an additional "pause" option that means "pause at
the end of WAL if you don't reach the recovery target point". I'd also
be happy with a warning being emitted in the log if the recovery target
point isn't reached before reaching the end of WAL, but I don't think it
makes sense to change the existing behavior.

Thanks!

Stephen

On Wed, Aug 17, 2016 at 12:06 AM, Stephen Frost <sfrost@snowman.net> wrote:

Greetings,

* Venkata B Nagothi (nag1010@gmail.com) wrote:

The above said parameters can be configured to pause, shutdown or prevent
promotion only after reaching the recovery target point.
To clarify, I am referring to a scenario where recovery target point is

not

reached at all ( i mean, half-complete or in-complete recovery) and there
are lots of WALs still pending to be replayed - in this situation,

PG doesn't know that there are still WALs to be replayed.

PG doesn't know that there are still WALs to be replayed. Since, i have
given an particular recovery target and PG knows the current replay
position,
I would say, it would be good if PG warns and pauses there by saying
recovery target point is not reached.

It would be nice if PostgreSQL pauses the recovery in-case its not
complete

(because of missing or corrupt WAL), shutdown the cluster and allows the
DBA to restart the replay of the remaining WAL Archive files to continue
recovery (from where it stopped previously) until the recovery target

point

is reached.

Agreed. Reaching end-of-WAL is not an error. It sounds more like a
limitation in certain scenarios.

Reaching the end of WAL isn't an error and I don't believe it makes any

sense to treat it like it is. You can specify any recovery target point
you wish, including ones that don't exist, and that's not an error
either.

I could see supporting an additional "pause" option that means "pause at
the end of WAL if you don't reach the recovery target point". I'd also
be happy with a warning being emitted in the log if the recovery target
point isn't reached before reaching the end of WAL, but I don't think it
makes sense to change the existing behavior.

Agreed. Additional option like "pause" would. As long as there is an option
to ensure following happens if the recovery target is not reached -

a) PG pauses the recovery at the end of the WAL
b) Generates a warning in the log file saying that recovery target point
is not reached (there is a patch being worked upon on by Thom on this)
c) Does not open-up the database exiting from the recovery process by
giving room to resume the replay of WALs

Regards,
Venkata B N

Fujitsu Australia

Venkata,

* Venkata B Nagothi (nag1010@gmail.com) wrote:

Agreed. Additional option like "pause" would. As long as there is an option
to ensure following happens if the recovery target is not reached -

a) PG pauses the recovery at the end of the WAL
b) Generates a warning in the log file saying that recovery target point
is not reached (there is a patch being worked upon on by Thom on this)
c) Does not open-up the database exiting from the recovery process by
giving room to resume the replay of WALs

One thing to consider is just how different this is from simply bringing
PG up as a warm standby instead, with the warning added to indicate if
the recovery point wasn't reached.

Thanks!

Stephen

On Wed, Aug 17, 2016 at 11:27 PM, Stephen Frost <sfrost@snowman.net> wrote:

Venkata,

* Venkata B Nagothi (nag1010@gmail.com) wrote:

Agreed. Additional option like "pause" would. As long as there is an

option

to ensure following happens if the recovery target is not reached -

a) PG pauses the recovery at the end of the WAL
b) Generates a warning in the log file saying that recovery target point
is not reached (there is a patch being worked upon on by Thom on this)
c) Does not open-up the database exiting from the recovery process by
giving room to resume the replay of WALs

One thing to consider is just how different this is from simply bringing
PG up as a warm standby instead, with the warning added to indicate if
the recovery point wasn't reached.

I am referring to a specific scenario (performing point-in time recovery)
where-in a DBA attempts to bring up a standalone PG instance by restoring
the backup and performing recovery to a particular recover target (XID,
time or a named restore point) in the past by replaying all the available
WAL archives, which is not quite possible through a warm-standby setup.

Warm standby is more of a high-availability solution and i do not think so,
it addresses PITR kind of situation.

I will share more details defining PG behaviour across various recovery
scenarios (as asked by David Steele) when using various recovery*
parameters. Will also explain what difference the proposed patch could make.

Regards,

Venkata B N
Fujitsu Australia

On Tue, Aug 16, 2016 at 11:06 PM, Stephen Frost <sfrost@snowman.net> wrote:

I could see supporting an additional "pause" option that means "pause at
the end of WAL if you don't reach the recovery target point". I'd also
be happy with a warning being emitted in the log if the recovery target
point isn't reached before reaching the end of WAL, but I don't think it
makes sense to change the existing behavior.

Indeed, let's not change the existing behavior. A warning showing up
by default would be useful in itself, even if there are people that I
think set up overly high recovery targets to be sure to replay WAL as
much as possible. As recovery_target_action has meaning when a
recovery target has been reached, I would guess that we would want a
new option that has the same mapping value as recovery_target_action,
except that it activates when the target recovery is *not* reached.
Hence it would be possible to shutdown, pause or promote at will when
recovery completes, and be able to take a separate action is the
recovery target is indeed reached. The default of this parameter would
be "promote", which is what happens now.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Aug 18, 2016 at 3:37 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

On Tue, Aug 16, 2016 at 11:06 PM, Stephen Frost <sfrost@snowman.net>
wrote:

I could see supporting an additional "pause" option that means "pause at
the end of WAL if you don't reach the recovery target point". I'd also
be happy with a warning being emitted in the log if the recovery target
point isn't reached before reaching the end of WAL, but I don't think it
makes sense to change the existing behavior.

Indeed, let's not change the existing behavior. A warning showing up
by default would be useful in itself, even if there are people that I
think set up overly high recovery targets to be sure to replay WAL as
much as possible. As recovery_target_action has meaning when a
recovery target has been reached, I would guess that we would want a
new option that has the same mapping value as recovery_target_action,
except that it activates when the target recovery is *not* reached.
Hence it would be possible to shutdown, pause or promote at will when
recovery completes, and be able to take a separate action is the
recovery target is indeed reached. The default of this parameter would
be "promote", which is what happens now.

Agreed. I understand the complexities with backward compatibility on
changing the existing behaviour.
Even, I was more inclined towards introducing a new parameter and as
suggested, will consider the options pause, shutdown or promote for new
parameter.
Thanks for the inputs and advises.

Regards,
Venkata B N

Fujitsu Australia

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Wed, Aug 17, 2016 at 11:27 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

Agreed. Additional option like "pause" would. As long as there is an

option

to ensure following happens if the recovery target is not reached -

a) PG pauses the recovery at the end of the WAL
b) Generates a warning in the log file saying that recovery target point
is not reached (there is a patch being worked upon on by Thom on this)
c) Does not open-up the database exiting from the recovery process by
giving room to resume the replay of WALs

One thing to consider is just how different this is from simply bringing
PG up as a warm standby instead, with the warning added to indicate if
the recovery point wasn't reached.

I am referring to a specific scenario (performing point-in time recovery)
where-in a DBA attempts to bring up a standalone PG instance by restoring
the backup and performing recovery to a particular recover target (XID,
time or a named restore point) in the past by replaying all the available
WAL archives, which is not quite possible through a warm-standby setup.

Warm standby is more of a high-availability solution and i do not think so,
it addresses PITR kind of situation.

No, a warm standby is one which just plays through the WAL but doesn't
bring the database up or start its own set of WAL, which is what you're
asking about.

Thanks!

Stephen

On Thu, Aug 18, 2016 at 7:20 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Wed, Aug 17, 2016 at 11:27 PM, Stephen Frost <sfrost@snowman.net>

wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

Agreed. Additional option like "pause" would. As long as there is an

option

to ensure following happens if the recovery target is not reached -

a) PG pauses the recovery at the end of the WAL
b) Generates a warning in the log file saying that recovery target

point

is not reached (there is a patch being worked upon on by Thom on

this)

c) Does not open-up the database exiting from the recovery process

by

giving room to resume the replay of WALs

One thing to consider is just how different this is from simply

bringing

PG up as a warm standby instead, with the warning added to indicate if
the recovery point wasn't reached.

I am referring to a specific scenario (performing point-in time recovery)
where-in a DBA attempts to bring up a standalone PG instance by restoring
the backup and performing recovery to a particular recover target (XID,
time or a named restore point) in the past by replaying all the available
WAL archives, which is not quite possible through a warm-standby setup.

Warm standby is more of a high-availability solution and i do not think

so,

it addresses PITR kind of situation.

No, a warm standby is one which just plays through the WAL but doesn't
bring the database up or start its own set of WAL, which is what you're
asking about.

I understand that, in warm-standby, PG does continuously replay WAL without
bringing up the database as the database will be in standby mode, which
sounds ideal.

While recovering the database to a particular recovery target point ( and
there is no requirement to setup standby ), then, there is a risk that
database will get promoted due to missing or corrupt WALs. Which means,
there is no way to avoid promotion and resume WAL recovery. An option like
"pause" with a new parameter would be ideal to prevent database promotion
at a default recovery target, which is "immediate" or "end-of-WAL".

Regards,

Venkata B N
Fujitsu Australia

On Thu, Aug 18, 2016 at 3:37 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:

On Tue, Aug 16, 2016 at 11:06 PM, Stephen Frost <sfrost@snowman.net>
wrote:

I could see supporting an additional "pause" option that means "pause at
the end of WAL if you don't reach the recovery target point". I'd also
be happy with a warning being emitted in the log if the recovery target
point isn't reached before reaching the end of WAL, but I don't think it
makes sense to change the existing behavior.

Indeed, let's not change the existing behavior. A warning showing up
by default would be useful in itself, even if there are people that I
think set up overly high recovery targets to be sure to replay WAL as
much as possible. As recovery_target_action has meaning when a
recovery target has been reached, I would guess that we would want a
new option that has the same mapping value as recovery_target_action,
except that it activates when the target recovery is *not* reached.
Hence it would be possible to shutdown, pause or promote at will when
recovery completes, and be able to take a separate action is the
recovery target is indeed reached. The default of this parameter would
be "promote", which is what happens now.

Yes, a new parameter with same options as recovery_target_action is the
idea i had in mind as well and i have the following queries while working
through the patch design -

*Query 1*

What about the existing parameter called "recovery_target" which accepts
only one value "immediate", which will be similar to the "promote" option
with the to-be-introduced new parameter.
Since this parameter's behaviour will be incorporated into the new
parameter, I think, this parameter can be deprecated from the next
PostgreSQL version ?

*Query 2*

I am thinking that the new parameter name should be
"recovery_target_incomplete" or "recovery_target_incomplete_action" which
(by name) suggests that recovery target point is not yet reached and
accepts options "pause","promote" and "shutdown".

The other alternative name i thought of was -
"recovery_target_immediate_action", which (by name) suggests the action to
be taken when the recovery does not reach the actual set recovery target
and reaches immediate consistent point.

Any comments, suggestions ?

Regards,
Venkata B N

Fujitsu Australia

* Venkata B Nagothi (nag1010@gmail.com) wrote:

*Query 1*

What about the existing parameter called "recovery_target" which accepts
only one value "immediate", which will be similar to the "promote" option
with the to-be-introduced new parameter.
Since this parameter's behaviour will be incorporated into the new
parameter, I think, this parameter can be deprecated from the next
PostgreSQL version ?

I don't think we can really consider that without having a good answer
for what the "new parameter" is, in particular...

*Query 2*

I am thinking that the new parameter name should be
"recovery_target_incomplete" or "recovery_target_incomplete_action" which
(by name) suggests that recovery target point is not yet reached and
accepts options "pause","promote" and "shutdown".

The other alternative name i thought of was -
"recovery_target_immediate_action", which (by name) suggests the action to
be taken when the recovery does not reach the actual set recovery target
and reaches immediate consistent point.

I don't really care for any of those names. Note that "immediate" and
"the point at which we realize that we didn't find the recovery target"
are *not* necessairly the same. Whatever we do here needs to also cover
the 'recovery_target_name' option, where we're only going to realize
that we didn't find the restore point when we reach the end of the WAL
stream.

I'm not a fan of the "recovery_target" option, particularly as it's only
got one value even though it can mean two things (either "immediate" or
"not set"), but we need a complete solution before we can consider
deprecating it. Further, we could consider making it an alias for
whatever better name we come up with.

Thanks!

Stephen

On Thu, Aug 25, 2016 at 10:59 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

*Query 1*

What about the existing parameter called "recovery_target" which accepts
only one value "immediate", which will be similar to the "promote" option
with the to-be-introduced new parameter.
Since this parameter's behaviour will be incorporated into the new
parameter, I think, this parameter can be deprecated from the next
PostgreSQL version ?

I don't think we can really consider that without having a good answer
for what the "new parameter" is, in particular...

Sure. I Agree.

*Query 2*

I am thinking that the new parameter name should be
"recovery_target_incomplete" or "recovery_target_incomplete_action"

which

(by name) suggests that recovery target point is not yet reached and
accepts options "pause","promote" and "shutdown".

The other alternative name i thought of was -
"recovery_target_immediate_action", which (by name) suggests the action

to

be taken when the recovery does not reach the actual set recovery target
and reaches immediate consistent point.

I don't really care for any of those names. Note that "immediate" and
"the point at which we realize that we didn't find the recovery target"
are *not* necessairly the same. Whatever we do here needs to also cover
the 'recovery_target_name' option, where we're only going to realize
that we didn't find the restore point when we reach the end of the WAL
stream.

Yes, all the recovery targets (including recovery_target_name) will be
taken into consideration.
The whole idea about this patch is to ensure PG realises that the recovery
is incomplete if the specified recovery target point is not found at the
end of the WAL stream.

I'm not a fan of the "recovery_target" option, particularly as it's only
got one value even though it can mean two things (either "immediate" or
"not set"), but we need a complete solution before we can consider
deprecating it. Further, we could consider making it an alias for
whatever better name we come up with.

The new parameter will accept options : "pause", "shutdown" and "promote"

*"promote"*

This option will ensure database starts up once the "immediate" consistent
recovery point is reached even if it is well before the mentioned recovery
target point (XID, Name or time).
This behaviour will be similar to that of recovery_target="immediate" and
can be aliased.

*"pause"*

This option ensures database recovery pauses if any of the specified
recovery target ("XID", "Name" or "time") is not reached. So that WAL
replay can be resumed if required.

*"shutdown"*

This option ensures database shuts down if any of the mentioned recovery
target points (XID, Name or time) is not reached.

Regards,
Venkata B N

Fujitsu Australia

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Thu, Aug 25, 2016 at 10:59 PM, Stephen Frost <sfrost@snowman.net> wrote:

I'm not a fan of the "recovery_target" option, particularly as it's only
got one value even though it can mean two things (either "immediate" or
"not set"), but we need a complete solution before we can consider
deprecating it. Further, we could consider making it an alias for
whatever better name we come up with.

The new parameter will accept options : "pause", "shutdown" and "promote"

*"promote"*

This option will ensure database starts up once the "immediate" consistent
recovery point is reached even if it is well before the mentioned recovery
target point (XID, Name or time).
This behaviour will be similar to that of recovery_target="immediate" and
can be aliased.

I don't believe we're really going at this the right way. Clearly,
there will be cases where we'd like promotion at the end of the WAL
stream (as we currently have) even if the recovery point is not found,
but if the new option's "promote" is the same as "immediate" then we
don't have that.

We need to break this down into all the different possible combinations
and then come up with names for the options to define them. I don't
believe a single option is going to be able to cover all of the cases.

The cases which I'm considering are:

recovery target is immediate (as soon as we have consistency)
recovery target is a set point (name, xid, time, whatever)

action to take if recovery target is found
action to take if recovery target is not found

Generally, "action" is one of "promote", "pause", or "shutdown".
Clearly, not all actions are valid for all recovery target cases- in
particular, "immediate" with "recovery target not found" can not support
the "promote" or "pause" options. Otherwise, we can support:

Recovery Target | Found | Action
-----------------|---------|----------
immediate | Yes | promote
immediate | Yes | pause
immediate | Yes | shutdown

immediate | No | shutdown

name/xid/time | Yes | promote
name/xid/time | Yes | pause
name/xid/time | Yes | shutdown

name/xid/time | No | promote
name/xid/time | No | pause
name/xid/time | No | shutdown

We could clearly support this with these options:

recovery_target = immediate, other
recovery_action_target_found = promote, pause, shutdown
recovery_action_target_not_found = promote, pause, shutdown

One question to ask is if we need to support an option for xid and time
related to when we realize that we won't find the recovery target. If
consistency is reached at a time which is later than the recovery target
for time, what then? Do we go through the rest of the WAL and perform
the "not found" action at the end of the WAL stream? If we use that
approach, then at least all of the recovery target types are handled the
same, but I can definitely see cases where an administrator might prefer
an "error" option.

I'd suggest we attempt to support that also.

Thanks!

Stephen

On Fri, Aug 26, 2016 at 12:30 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Thu, Aug 25, 2016 at 10:59 PM, Stephen Frost <sfrost@snowman.net>

wrote:

I'm not a fan of the "recovery_target" option, particularly as it's

only

got one value even though it can mean two things (either "immediate" or
"not set"), but we need a complete solution before we can consider
deprecating it. Further, we could consider making it an alias for
whatever better name we come up with.

The new parameter will accept options : "pause", "shutdown" and "promote"

*"promote"*

This option will ensure database starts up once the "immediate"

consistent

recovery point is reached even if it is well before the mentioned

recovery

target point (XID, Name or time).
This behaviour will be similar to that of recovery_target="immediate" and
can be aliased.

I don't believe we're really going at this the right way. Clearly,
there will be cases where we'd like promotion at the end of the WAL
stream (as we currently have) even if the recovery point is not found,
but if the new option's "promote" is the same as "immediate" then we
don't have that.

My apologies for the confusion. Correction - I meant, "promote" option
would promote the database once the consistent point is reached at the
end-of-the-WAL.

We need to break this down into all the different possible combinations
and then come up with names for the options to define them. I don't
believe a single option is going to be able to cover all of the cases.

The cases which I'm considering are:

recovery target is immediate (as soon as we have consistency)
recovery target is a set point (name, xid, time, whatever)

action to take if recovery target is found
action to take if recovery target is not found

Generally, "action" is one of "promote", "pause", or "shutdown".
Clearly, not all actions are valid for all recovery target cases- in
particular, "immediate" with "recovery target not found" can not support
the "promote" or "pause" options. Otherwise, we can support:

I Agree. This is a valid point to consider. I might have few questions over
this at a later stage.

Recovery Target | Found | Action

-----------------|---------|----------
immediate | Yes | promote
immediate | Yes | pause
immediate | Yes | shutdown

immediate | No | shutdown

name/xid/time | Yes | promote
name/xid/time | Yes | pause
name/xid/time | Yes | shutdown

name/xid/time | No | promote
name/xid/time | No | pause
name/xid/time | No | shutdown

We could clearly support this with these options:

recovery_target = immediate, other

recovery_action_target_found = promote, pause, shutdown

This is currently supported by the existing parameter called
"recovery_target_action"

recovery_action_target_not_found = promote, pause, shutdown

This is exactly what newly proposed parameter will do.

One question to ask is if we need to support an option for xid and time
related to when we realize that we won't find the recovery target. If
consistency is reached at a time which is later than the recovery target
for time, what then? Do we go through the rest of the WAL and perform
the "not found" action at the end of the WAL stream? If we use that
approach, then at least all of the recovery target types are handled the
same, but I can definitely see cases where an administrator might prefer
an "error" option.

Currently, this situation is handled by recovery_target_inclusive
parameter. Administrator can choose to stop the recovery at any consistent
point before or after the specified recovery target. Is this what you were
referring to ?
I might need a bit of clarification here, the parameter i am proposing
would be effective only if the specified recovery target is not reached and
may not be effective if the recovery goes beyond recovery target point.

Regards,
Venkata B N

Fujitsu Australia

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Fri, Aug 26, 2016 at 12:30 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

This behaviour will be similar to that of recovery_target="immediate" and
can be aliased.

I don't believe we're really going at this the right way. Clearly,
there will be cases where we'd like promotion at the end of the WAL
stream (as we currently have) even if the recovery point is not found,
but if the new option's "promote" is the same as "immediate" then we
don't have that.

My apologies for the confusion. Correction - I meant, "promote" option
would promote the database once the consistent point is reached at the
end-of-the-WAL.

"consistent point" and "end-of-the-WAL" are not the same.

recovery_target = immediate, other

recovery_action_target_found = promote, pause, shutdown

This is currently supported by the existing parameter called
"recovery_target_action"

Right, sure, we could possibly use that, or create an alias, etc.

recovery_action_target_not_found = promote, pause, shutdown

This is exactly what newly proposed parameter will do.

Then it isn't the same as 'recovery_target = immediate'.

One question to ask is if we need to support an option for xid and time
related to when we realize that we won't find the recovery target. If
consistency is reached at a time which is later than the recovery target
for time, what then? Do we go through the rest of the WAL and perform
the "not found" action at the end of the WAL stream? If we use that
approach, then at least all of the recovery target types are handled the
same, but I can definitely see cases where an administrator might prefer
an "error" option.

Currently, this situation is handled by recovery_target_inclusive
parameter.

No, that's not the same.

Administrator can choose to stop the recovery at any consistent
point before or after the specified recovery target. Is this what you were
referring to ?

Not quite.

I might need a bit of clarification here, the parameter i am proposing
would be effective only if the specified recovery target is not reached and
may not be effective if the recovery goes beyond recovery target point.

Ok, let's consider this scenario:

Backup started at: 4/22D3B8E0, time: 2016-08-26 08:29:08
Backup completed (consistency) at: 4/22D3B8EF, 2016-08-26 08:30:00
recovery_target is not set
recovery_target_time = 2016-08-26 08:29:30
recovery_target_inclusive = false

In such a case, we will necessairly commit transactions which happened
between 8:29:30 and 8:30:00 and only stop (I believe..) once we've
reached consistency. We could possibly detect that case and throw an
error instead. The same could happen with xid.

Working through more scenarios would be useful, I believe. For example,
what if the xid or time specified happened before the backup started?

Basically, what I was trying to get at is that we might be able to
detect that we'll never find a given recovery target point without
actually replaying all of WAL and wondering if we should provide options
to control what to do in such a case.

Thanks!

Stephen

On Fri, Aug 26, 2016 at 10:58 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Fri, Aug 26, 2016 at 12:30 PM, Stephen Frost <sfrost@snowman.net>

wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

This behaviour will be similar to that of

recovery_target="immediate" and

can be aliased.

I don't believe we're really going at this the right way. Clearly,
there will be cases where we'd like promotion at the end of the WAL
stream (as we currently have) even if the recovery point is not found,
but if the new option's "promote" is the same as "immediate" then we
don't have that.

My apologies for the confusion. Correction - I meant, "promote" option
would promote the database once the consistent point is reached at the
end-of-the-WAL.

"consistent point" and "end-of-the-WAL" are not the same.

recovery_target = immediate, other

recovery_action_target_found = promote, pause, shutdown

This is currently supported by the existing parameter called
"recovery_target_action"

Right, sure, we could possibly use that, or create an alias, etc.

recovery_action_target_not_found = promote, pause, shutdown

This is exactly what newly proposed parameter will do.

Then it isn't the same as 'recovery_target = immediate'.

One question to ask is if we need to support an option for xid and time
related to when we realize that we won't find the recovery target. If
consistency is reached at a time which is later than the recovery

target

for time, what then? Do we go through the rest of the WAL and perform
the "not found" action at the end of the WAL stream? If we use that
approach, then at least all of the recovery target types are handled

the

same, but I can definitely see cases where an administrator might

prefer

an "error" option.

Currently, this situation is handled by recovery_target_inclusive
parameter.

No, that's not the same.

Administrator can choose to stop the recovery at any consistent
point before or after the specified recovery target. Is this what you

were

referring to ?

Not quite.

I might need a bit of clarification here, the parameter i am proposing
would be effective only if the specified recovery target is not reached

and

may not be effective if the recovery goes beyond recovery target point.

Ok, let's consider this scenario:

Backup started at: 4/22D3B8E0, time: 2016-08-26 08:29:08
Backup completed (consistency) at: 4/22D3B8EF, 2016-08-26 08:30:00
recovery_target is not set
recovery_target_time = 2016-08-26 08:29:30
recovery_target_inclusive = false

In such a case, we will necessairly commit transactions which happened
between 8:29:30 and 8:30:00 and only stop (I believe..) once we've
reached consistency. We could possibly detect that case and throw an
error instead. The same could happen with xid.

Yes, currently, PG just recovers until the consistency is reached. It makes
sense to throw an error instead.

Working through more scenarios would be useful, I believe. For example,
what if the xid or time specified happened before the backup started?

Basically, what I was trying to get at is that we might be able to
detect that we'll never find a given recovery target point without
actually replaying all of WAL and wondering if we should provide options
to control what to do in such a case.

Ah, Yes, I got the point and I agree. Thanks for the clarification.

Yes, good idea and it is important to ensure PG errors out and warn the
administrator with appropriate message indicating that... "a much earlier
backup must be used..."
if the specified recovery target xid, name or time is earlier than the
backup time.

Regards,

Venkata B N
Fujitsu Australia

Attached is the patch which introduces the new parameter
"recovery_target_incomplete" -

Currently this patch addresses two scenarios -

*Scenario 1 :*

Provide options to DBA when the recovery target is not reached and has
stopped mid-way due to unavailability of WALs

When performing recovery to a specific recovery target,
"recovery_target_incomplete" parameter can be used to either promote, pause
or shutdown when recovery does not reach the specified recovery target and
reaches end-of-the-wal.

*Scenario 2 :*

Generates Errors, Hints when the specified recovery target is prior to the
backup's current position (xid, time and lsn). This behaviour is integrated
with the parameters "recovery_target_time","recovery_target_lsn" and
"recovery_target_xid".

I would like to know if the approach this patch incorporates sounds ok ?

My other comments are inline

On Mon, Aug 29, 2016 at 3:17 PM, Venkata B Nagothi <nag1010@gmail.com>
wrote:

On Fri, Aug 26, 2016 at 10:58 PM, Stephen Frost <sfrost@snowman.net>
wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

On Fri, Aug 26, 2016 at 12:30 PM, Stephen Frost <sfrost@snowman.net>

wrote:

* Venkata B Nagothi (nag1010@gmail.com) wrote:

This behaviour will be similar to that of

recovery_target="immediate" and

can be aliased.

I don't believe we're really going at this the right way. Clearly,
there will be cases where we'd like promotion at the end of the WAL
stream (as we currently have) even if the recovery point is not found,
but if the new option's "promote" is the same as "immediate" then we
don't have that.

My apologies for the confusion. Correction - I meant, "promote" option
would promote the database once the consistent point is reached at the
end-of-the-WAL.

"consistent point" and "end-of-the-WAL" are not the same.

recovery_target = immediate, other

recovery_action_target_found = promote, pause, shutdown

This is currently supported by the existing parameter called
"recovery_target_action"

Right, sure, we could possibly use that, or create an alias, etc.

recovery_action_target_not_found = promote, pause, shutdown

This is exactly what newly proposed parameter will do.

Then it isn't the same as 'recovery_target = immediate'.

One question to ask is if we need to support an option for xid and

time

related to when we realize that we won't find the recovery target. If
consistency is reached at a time which is later than the recovery

target

for time, what then? Do we go through the rest of the WAL and perform
the "not found" action at the end of the WAL stream? If we use that
approach, then at least all of the recovery target types are handled

the

same, but I can definitely see cases where an administrator might

prefer

an "error" option.

Currently, this situation is handled by recovery_target_inclusive
parameter.

No, that's not the same.

Administrator can choose to stop the recovery at any consistent
point before or after the specified recovery target. Is this what you

were

referring to ?

Not quite.

I might need a bit of clarification here, the parameter i am proposing
would be effective only if the specified recovery target is not reached

and

may not be effective if the recovery goes beyond recovery target point.

Ok, let's consider this scenario:

Backup started at: 4/22D3B8E0, time: 2016-08-26 08:29:08
Backup completed (consistency) at: 4/22D3B8EF, 2016-08-26 08:30:00
recovery_target is not set
recovery_target_time = 2016-08-26 08:29:30
recovery_target_inclusive = false

In such a case, we will necessairly commit transactions which happened
between 8:29:30 and 8:30:00 and only stop (I believe..) once we've
reached consistency. We could possibly detect that case and throw an
error instead. The same could happen with xid.

Yes, currently, PG just recovers until the consistency is reached. It
makes sense to throw an error instead.

This has not been addressed yet. Currently, the patch only considers
generating an error if the recovery target position is prior the current
backup's position and this is irrespective of weather backup_label is
present or not.
I will share my updates on how this can be addressed.

Working through more scenarios would be useful, I believe. For example,
what if the xid or time specified happened before the backup started?

Basically, what I was trying to get at is that we might be able to
detect that we'll never find a given recovery target point without
actually replaying all of WAL and wondering if we should provide options
to control what to do in such a case.

Ah, Yes, I got the point and I agree. Thanks for the clarification.

Yes, good idea and it is important to ensure PG errors out and warn the
administrator with appropriate message indicating that... "a much earlier
backup must be used..."
if the specified recovery target xid, name or time is earlier than the
backup time.

This scenario has been addressed in this patch.

I have a question regarding the following comment -

/*
* Override any inconsistent requests. Not that this is a change of
* behaviour in 9.5; prior to this we simply ignored a request to
pause if
* hot_standby = off, which was surprising behaviour.
*/
if (recoveryTargetAction == RECOVERY_TARGET_ACTION_PAUSE &&
recoveryTargetActionSet &&
!EnableHotStandby)
recoveryTargetAction = RECOVERY_TARGET_ACTION_SHUTDOWN;

Because of which, option "pause" is ignored and the database is shutdown
instead.

The "pause" request is ignored for recovery_target_action parameter and
that is because of the following lines in recoveryPausesHere() function ?
am i getting it right ?

/* Don't pause unless users can connect! */
if (!LocalHotStandbyActive)
return;

Not sure why.

The parameter i am introducing faced the similar problem while testing and
i have introduced a new function called IncompleteRecoveryPause() without
the above condition which will pause the recovery at end-of-the-wal. Hope
that is OK ?
If this is ok, i can change the function name and use the similar function
for recovery_target_action='pause' as well.

Please note that documentation is still pending from my end.

This is my first patch to the community and i am very new the code base.
Please let me know if this patch needs any changes.

Regards,

Venkata B N

Database Consultant
Fujitsu Australia