BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
The following bug has been logged online:
Bug reference: 4796
Logged by: Mikael Krantz
Email address: mk@zigamorph.se
PostgreSQL version: 8.3.7-0lenny1
Operating system: Linux (debian lenny)
Description: Recovery followed by backup creates unrecoverable
WAL-file
Details:
If you perform a recovery form a file system level backup postgres will
switch to a new timeline but the first WAL-log in with the new timeline will
contain the previous timeline.
If you start a backup immediately after recovery have completed the start of
the backup will be in this bad WAL file. This makes the backup unrecoverable
as it will fail with an error similar to:
LOG: unexpected timeline ID 54 in log file 4, segment 236, offset 0
LOG: invalid checkpoint record
PANIC: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try removing the file
"/var/lib/postgresql/8.3/main/backup_label".
How to reproduce:
* restore from backup
* SELECT pg_start_backup('label');
* take a new backup
* SELECT pg_stop_backup();
* copy the relevant WAL-files
* try to restore the backup
It is also visible in the first WAL-file of a new timeline:
# od -t x4 /var/lib/postgresql/8.3/main/pg_xlog/0000003D0000000500000001
|head -1
0000000 0002d062 0000003c 00000005 01000000
The timeline tag 0000003c is in a file named 0000003D which causes it to be
unrecoverable.
Workaround:
Wait for or force a xlog switch before pg_start_backup. Possibly a simple
fix would be to make pg_start_backup force this switch automatically.
Mikael Krantz wrote:
If you perform a recovery form a file system level backup postgres will
switch to a new timeline but the first WAL-log in with the new timeline will
contain the previous timeline.If you start a backup immediately after recovery have completed the start of
the backup will be in this bad WAL file. This makes the backup unrecoverable
as it will fail with an error similar to:LOG: unexpected timeline ID 54 in log file 4, segment 236, offset 0
LOG: invalid checkpoint record
PANIC: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try removing the file
"/var/lib/postgresql/8.3/main/backup_label".How to reproduce:
* restore from backup
* SELECT pg_start_backup('label');
* take a new backup
* SELECT pg_stop_backup();
* copy the relevant WAL-files
* try to restore the backup
I failed to reproduce this. Is it possible that the history file went
missing in the process? That's needed to recover WAL files from
timelines other than the latest one. You should only get that
"unexpected timeline ID" message if the history file doesn't contain a
line for that timeline ID.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Wed, May 6, 2009 at 6:26 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
How to reproduce:
* restore from backup
* SELECT pg_start_backup('label');
* take a new backup
* SELECT pg_stop_backup();
* copy the relevant WAL-files
* try to restore the backupI failed to reproduce this. Is it possible that the history file went
missing in the process? That's needed to recover WAL files from timelines
other than the latest one. You should only get that "unexpected timeline ID"
message if the history file doesn't contain a line for that timeline ID.
Yes that's true. The history file is not included in the backup. It is
archived before the backup starts and is not included in the
range specified in the backup file (e.g:
0000003B00000004000000FC.00000020.backup).
Doesn't this mean that the range of log-files in the backup file is
incorrect? If the first WAL-file in the range contain records
referring to earlier timelines I will have to backup the .history-file
of that timeline in addition to the WAL-files explicitly required for
the backup. Or force a switch of log-files before starting the backup
as I'm currently doing.
The reason I stumbled onto this is that I've setup an automatic test
that sets up a warm standby, fails over, sets up a new warm server and
so on. This causes me to take new base backups very soon after a
finished recovery process.
/M
Mikael Krantz wrote:
On Wed, May 6, 2009 at 6:26 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:How to reproduce:
* restore from backup
* SELECT pg_start_backup('label');
* take a new backup
* SELECT pg_stop_backup();
* copy the relevant WAL-files
* try to restore the backupI failed to reproduce this. Is it possible that the history file went
missing in the process? That's needed to recover WAL files from timelines
other than the latest one. You should only get that "unexpected timeline ID"
message if the history file doesn't contain a line for that timeline ID.Yes that's true. The history file is not included in the backup. It is
archived before the backup starts and is not included in the
range specified in the backup file (e.g:
0000003B00000004000000FC.00000020.backup).Doesn't this mean that the range of log-files in the backup file is
incorrect? If the first WAL-file in the range contain records
referring to earlier timelines I will have to backup the .history-file
of that timeline in addition to the WAL-files explicitly required for
the backup. Or force a switch of log-files before starting the backup
as I'm currently doing.
Yeah, I think you're right. If you omit pg_xlog from the base backup, as
we recommend in the manual, and clear the old files from the archive
too, then you won't have the old history file around.
I'll make pg_start_backup() to request xlog switch before the checkpoint
as you suggested. That's an easy fix that can be easily back-patched.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
I wrote:
I'll make pg_start_backup() to request xlog switch before the checkpoint
as you suggested. That's an easy fix that can be easily back-patched.
Done. I only back-patched it down to 8.2, because earlier versions
didn't have pg_xlog_switch(). They would've required more invasive
changes which don't seem worth the effort.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote:
Yeah, I think you're right. If you omit pg_xlog from the base backup,
as we recommend in the manual, and clear the old files from the
archive too, then you won't have the old history file around.
Sorry about this, but I don't agree with that fix and think it needs
more discussion, at very least. (I'm also not sure why this fix needs to
applied with such haste, even taking priority over other unapplied
patches.)
The error seems to come from deleting the history file from the archive,
rather than from the sequence of actions.
A more useful thing might be to do an xlog switch before we do the
shutdown checkpoint at end of recovery. That gives the same sequence of
actions without modifying the existing sequence of activities for
backups, which is delicate enough for me to not want to touch it.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote:
Yeah, I think you're right. If you omit pg_xlog from the base backup,
as we recommend in the manual, and clear the old files from the
archive too, then you won't have the old history file around....
A more useful thing might be to do an xlog switch before we do the
shutdown checkpoint at end of recovery. That gives the same sequence of
actions without modifying the existing sequence of activities for
backups, which is delicate enough for me to not want to touch it.
Hmm, yeah should work as well. I find the recovery sequence to be even
more delicate, though, than pg_start_backup(). I think you'd need to
write the XLOG switch record using the old timeline ID, as we currently
require that the timeline changes only at a shutdown checkpoint record.
That's not hard, but does make me a bit nervous.
The advantage of that over switching xlog segment in pg_start_backup()
would be that you would go through fewer XLOG segments if you took
backups often.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Thu, 2009-05-07 at 17:54 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote:
Yeah, I think you're right. If you omit pg_xlog from the base backup,
as we recommend in the manual, and clear the old files from the
archive too, then you won't have the old history file around....
A more useful thing might be to do an xlog switch before we do the
shutdown checkpoint at end of recovery. That gives the same sequence of
actions without modifying the existing sequence of activities for
backups, which is delicate enough for me to not want to touch it.Hmm, yeah should work as well. I find the recovery sequence to be even
more delicate, though, than pg_start_backup(). I think you'd need to
write the XLOG switch record using the old timeline ID, as we currently
require that the timeline changes only at a shutdown checkpoint record.
That's not hard, but does make me a bit nervous.The advantage of that over switching xlog segment in pg_start_backup()
would be that you would go through fewer XLOG segments if you took
backups often.
Yes, you're right about the delicacy of all of this so both suggestions
sound kludgey - the problem is to do with timelines not with sequencing
of checkpoints and log switches. The problem is Mikael deleted the
history file and he shouldn't have done that. We need some explicit
protection for when that occurs, I feel, to avoid it breaking again in
the future with various changes we have planned.
If the history file is so important, we shouldn't only store it in the
archive. We should keep a copy locally as well and refer to it if the
archived copy is missing.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Thu, 2009-05-07 at 17:54 +0300, Heikki Linnakangas wrote:
Simon Riggs wrote:
A more useful thing might be to do an xlog switch before we do the
shutdown checkpoint at end of recovery. That gives the same sequence of
actions without modifying the existing sequence of activities for
backups, which is delicate enough for me to not want to touch it.Hmm, yeah should work as well. I find the recovery sequence to be even
more delicate, though, than pg_start_backup(). I think you'd need to
write the XLOG switch record using the old timeline ID, as we currently
require that the timeline changes only at a shutdown checkpoint record.
That's not hard, but does make me a bit nervous.Yes, you're right about the delicacy of all of this so both suggestions
sound kludgey - the problem is to do with timelines not with sequencing
of checkpoints and log switches. The problem is Mikael deleted the
history file and he shouldn't have done that.
I don't see any user error here. What he did was:
1. Restore from backup A
2. Clear old WAL archive
3. pg_start_backup() + tar all but pg_xlog + pg_stop_backup();
4. Restore new backup B
There's no history file in the archive because it was cleared in step 2.
There's nothing wrong with that; you only need to retain WAL files from
the point that you call pg_start_backup(). There's no history file
either in the tar, because pg_xlog was not tarred as we recommend in the
manual.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Thu, 2009-05-07 at 18:57 +0300, Heikki Linnakangas wrote:
I don't see any user error here.
Just observing that the error occurs because we rely on a file being
there when we haven't even documented that it needs to be there for it
to work. File deletion with %r from the archive would not have removed
that file at that point. We should have an explicit statement about
which files can be deleted from the archive and which should not be, but
in general it is dangerous to remove files that have not been explicitly
described as removable.
Playing with the order of events seems fragile and I would prefer a more
explicit solution. Recording the timeline history permanently with each
server would be a sensible and useful thing (IIRC DB2 does this).
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Thu, 2009-05-07 at 18:57 +0300, Heikki Linnakangas wrote:
I don't see any user error here.
Just observing that the error occurs because we rely on a file being
there when we haven't even documented that it needs to be there for it
to work. File deletion with %r from the archive would not have removed
that file at that point. We should have an explicit statement about
which files can be deleted from the archive and which should not be, but
in general it is dangerous to remove files that have not been explicitly
described as removable.
When you create a new base backup, you shouldn't need any files archived
before starting the backup. You might not even have had archiving
enabled before that, or you might change archive_command to archive into
a new location before tarting the backup.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Hi,
On Fri, May 8, 2009 at 2:42 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
When you create a new base backup, you shouldn't need any files archived
before starting the backup.
If so, this fix is not enough, since findNewestTimeLine() is
still based on the premise that *all* the history files exist.
So, as Simon says, we should clearly say that a history file
must not be deleted from the archive. Or, we should create
a new solution.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Fujii Masao wrote:
Hi,
On Fri, May 8, 2009 at 2:42 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:When you create a new base backup, you shouldn't need any files archived
before starting the backup.If so, this fix is not enough, since findNewestTimeLine() is
still based on the premise that *all* the history files exist.
So, as Simon says, we should clearly say that a history file
must not be deleted from the archive. Or, we should create
a new solution.
The probe in findNewestTimeLine() initialized to recovery target
timeline + 1. It doesn't require history files for any old timelines to
be present. The purpose of findNewestTimeLine() is to ensure that if you
e.g recover to a point in time in timeline 5, and there's already WAL
files for timelines 6 and 7 in the archive, we pick a unique timeline id.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Fri, 2009-05-15 at 20:11 +0900, Fujii Masao wrote:
Hi,
On Fri, May 8, 2009 at 2:42 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:When you create a new base backup, you shouldn't need any files archived
before starting the backup.If so, this fix is not enough, since findNewestTimeLine() is
still based on the premise that *all* the history files exist.
So, as Simon says, we should clearly say that a history file
must not be deleted from the archive. Or, we should create
a new solution.
I will feel safer if we keep history files in the main data directory
(somehow), not just send them to the archive.
The history files together describe the provenance of the current
database and I think it takes almost no space to record that, so it
seems like a good idea to keep them.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Hi,
On Fri, May 15, 2009 at 8:20 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
The probe in findNewestTimeLine() initialized to recovery target timeline +
1. It doesn't require history files for any old timelines to be present.
What if recovery_target_timeline = 'latest'? The unexpected (not latest)
recovery target timeline might be chosen when some timeline history
files don't exist.
The
purpose of findNewestTimeLine() is to ensure that if you e.g recover to a
point in time in timeline 5, and there's already WAL files for timelines 6
and 7 in the archive, we pick a unique timeline id.
When only the history file for timeline 6 is deleted, timeline 6 would be
assigned as the newest one *again* at the end of archive recovery.
Is this safe?
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
On Fri, 2009-05-15 at 20:38 +0900, Fujii Masao wrote:
On Fri, May 15, 2009 at 8:20 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:The probe in findNewestTimeLine() initialized to recovery target timeline +
1. It doesn't require history files for any old timelines to be present.What if recovery_target_timeline = 'latest'? The unexpected (not latest)
recovery target timeline might be chosen when some timeline history
files don't exist.The
purpose of findNewestTimeLine() is to ensure that if you e.g recover to a
point in time in timeline 5, and there's already WAL files for timelines 6
and 7 in the archive, we pick a unique timeline id.When only the history file for timeline 6 is deleted, timeline 6 would be
assigned as the newest one *again* at the end of archive recovery.
Is this safe?
Yeh, those cases screw us up. I'm sure we can think of others, I had
time to analyse things in more detail. I'd be happier with the general
assessment that "it's unsafe to keep history files in the archive".
My suggestion is that we keep history files in a new directory under the
data directory. That way they get copied as part of the base backup,
rather than sent off to the archive where DBAs can have mad moments and
delete all, or worse, just some of them. Implementation for this
proposal is really easy and safe for where we are now: we just access
the appropriate local directory. Call it pg_history or pg_timeline etc..
Not under pg_xlog!
There is no particular reason to send history files to the archive,
since new ones are only ever generated at the end of an archive
recovery.
Now that we increment the timeline more often this is a more visible
problem than previously.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Fujii Masao wrote:
On Fri, May 15, 2009 at 8:20 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:The probe in findNewestTimeLine() initialized to recovery target timeline +
1. It doesn't require history files for any old timelines to be present.What if recovery_target_timeline = 'latest'? The unexpected (not latest)
recovery target timeline might be chosen when some timeline history
files don't exist.The
purpose of findNewestTimeLine() is to ensure that if you e.g recover to a
point in time in timeline 5, and there's already WAL files for timelines 6
and 7 in the archive, we pick a unique timeline id.When only the history file for timeline 6 is deleted, timeline 6 would be
assigned as the newest one *again* at the end of archive recovery.
Is this safe?
If you delete history file and all the WAL for timeline 6, yeah, nothing
stops it from being reused. It will work just fine, as if it never
existed. If you still have the history file and WAL for the old timeline
6 lying around somewhere else like an older offsite backup, it's easy
for the administrator to get confused, but there isn't much we can do
about that.
Simon's idea of keeping a copy of all the history files in the data
directory wouldn't help here. In fact, I think we already never delete
history files in the server, it's just that if you omit the pg_xlog
directory in the base backup they won't be included. But even if they
are included in the base backup, that wouldn't help in this scenario
because the base backup still wouldn't contain the history files for the
later timelines.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Fri, 2009-05-15 at 12:56 +0100, Simon Riggs wrote:
There is no particular reason to send history files to the archive,
since new ones are only ever generated at the end of an archive
recovery.
It also clears up a long standing confusion between backup history files
and timeline history files. The backup history file(s) do need to go to
the archive, whereas the timeline file(s) do not.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Fri, 2009-05-15 at 20:38 +0900, Fujii Masao wrote:
On Fri, May 15, 2009 at 8:20 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:The probe in findNewestTimeLine() initialized to recovery target timeline +
1. It doesn't require history files for any old timelines to be present.What if recovery_target_timeline = 'latest'? The unexpected (not latest)
recovery target timeline might be chosen when some timeline history
files don't exist.The
purpose of findNewestTimeLine() is to ensure that if you e.g recover to a
point in time in timeline 5, and there's already WAL files for timelines 6
and 7 in the archive, we pick a unique timeline id.When only the history file for timeline 6 is deleted, timeline 6 would be
assigned as the newest one *again* at the end of archive recovery.
Is this safe?Yeh, those cases screw us up. I'm sure we can think of others, I had
time to analyse things in more detail. I'd be happier with the general
assessment that "it's unsafe to keep history files in the archive".My suggestion is that we keep history files in a new directory under the
data directory. That way they get copied as part of the base backup,
rather than sent off to the archive where DBAs can have mad moments and
delete all, or worse, just some of them. Implementation for this
proposal is really easy and safe for where we are now: we just access
the appropriate local directory. Call it pg_history or pg_timeline etc..
Not under pg_xlog!There is no particular reason to send history files to the archive,
since new ones are only ever generated at the end of an archive
recovery.
Consider this:
1. Take base backup, on timeline 1. Archive to directory X
2. Disaster.
3. restore from base backup and the archive. Timeline ID is incremented
to 2. Keep archiving to directory X.
4. Another disaster.
5. Restore again from the base backup and archive. Timeline ID is
incremented to 3.
If the history files are not in the archive, where is the restore at
step 5 going to get the history file for timeline 2? You certainly need
the history files in the archive.
The history files should be considered as part of the WAL data. They
need to be archived together with the WAL segments. When you take a new
base backup, you no longer need the history files for old timelines,
just like you don't need old WAL.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Fri, 2009-05-15 at 14:56 +0300, Heikki Linnakangas wrote:
Simon's idea of keeping a copy of all the history files in the data
directory wouldn't help here. In fact, I think we already never delete
history files in the server, it's just that if you omit the pg_xlog
directory in the base backup they won't be included. But even if they
are included in the base backup, that wouldn't help in this scenario
because the base backup still wouldn't contain the history files for the
later timelines.
You're right there.
That still leaves the problem that we need to know the later history,
even if we don't use it.
If you delete history file and all the WAL for timeline 6, yeah, nothing
stops it from being reused. It will work just fine, as if it never
existed. If you still have the history file and WAL for the old timeline
6 lying around somewhere else like an older offsite backup, it's easy
for the administrator to get confused, but there isn't much we can do
about that.
ehem, "It will work fine" isn't correct, as Fujii-san observes.
Let's document that timeline files should not be deleted from the
archive iff there exists a base backup made during a lower numbered
timeline.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
