[BUGS] BUG #5602: Recovering from Hot-Standby file backup leads to the currupted indexes
Chris
The following bug has been logged online:
Bug reference: 5602
Logged by: Chris
Email address: christoph...@magicinternet.de
PostgreSQL version: 9.0beta3
Operating system: Debian 5.0.3
Description: Recovering from Hot-Standby file backup leads to the
currupted indexes
Details:
A Hot-Standby database is being backed up to a new machine like this:
0. pause WAL file deletion from Hot-Standby
1. backup pg_control
2. sync data and tablespace files
3. wait for checkout using pg_controldata and waiting for UNDO address
change
4. copy all WAL files
5. recover the newly created database using WAL files
The database recovers and starts normally, but some of the indexes are
corrupt afterwards. I could not find any strange log record in logs for the
recovered database.
This is a definition of a index that is corrupt afterwards:
CREATE INDEX idx_tbl_textcol1_textcol2
ON tbl
USING btree
(textcol1, textcol2)
TABLESPACE data5_tbs;
Thank you in advance,
Chris
--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Tom Lane
> A Hot-Standby database is being backed up to a new machine like this:
> 0. pause WAL file deletion from Hot-Standby
> 1. backup pg_control
> 2. sync data and tablespace files
> 3. wait for checkout using pg_controldata and waiting for UNDO address
> change
> 4. copy all WAL files
> 5. recover the newly created database using WAL files
This process seems almost entirely unrelated to the documented way of
doing it; I'm not surprised that you end up with some files not in sync.
Please see pg_start_backup and friends.
regards, tom lane
valgog
> This process seems almost entirely unrelated to the documented way of
> doing it; I'm not surprised that you end up with some files not in sync.
> Please see pg_start_backup and friends.
>
> regards, tom lane
>
Hallo Tom,
It was done as documented in http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
With my best regards,
-- Valentine
Tom Lane
>> This process seems almost entirely unrelated to the documented way of
>> doing it; I'm not surprised that you end up with some files not in sync.
>> Please see pg_start_backup and friends.
> It was done as documented in http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
Oh, I missed that you were copying from a hot-standby slave rather than
the master. Still, your procedure doesn't clearly match step 2, and
that step is the weak point of the process --- the grandchild slave
isn't consistent until it's replayed WAL far enough, but we don't have
any automated support for verifying that. (I hope that's going to get
improved in 9.1.) I suspect you allowed the grandchild to go live
before it was really consistent.
Simon Riggs
> > This process seems almost entirely unrelated to the documented way of
> > doing it; I'm not surprised that you end up with some files not in sync.
> > Please see pg_start_backup and friends.
> It was done as documented in http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
The procedure used does differ from that documented. However, IMHO the
procedure *documented* is *not* safe and could lead to corrupt indexes
in the way described, since the last recovered point might be mid-way
between two halves of an index split record, which will never be
corrected during HS. What I find surprising is that the technique the OP
describes should be safe, assuming step 5 waits for the correct point of
consistency before attempting to run queries.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
Tom Lane
> On Thu, 2010-08-05 at 11:28 -0700, valgog wrote:
>> It was done as documented in http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
> The procedure used does differ from that documented. However, IMHO the
> procedure *documented* is *not* safe and could lead to corrupt indexes
> in the way described, since the last recovered point might be mid-way
> between two halves of an index split record, which will never be
> corrected during HS.
Hm, I was looking at that and thinking it seemed unsafe for entirely
different reasons. But if you didn't write it, who did?
regards, tom lane
Fujii Masao
> Hm, I was looking at that and thinking it seemed unsafe for entirely
> different reasons. But if you didn't write it, who did?
Incrementally Updated Backups technique has been in the document since 8.2.
In the development cycle of 9.0, I and Heikki appended some description to
make the technique more robust; pg_control file should be backed up first
and the backup end point should be checked before running query.
If it's unsafe, I'm happy to modify it. Which part looks unsafe?
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Chris
the procedure that waits for the checkpoint location change looks
like::
function wait_for_checkpoint_location_change() {
PRE_WAIT=$( pg_controldata $PGDATA | awk -F: '/Latest checkpoint
location/ { print $2 }' )
log "Waiting for checkpoint"
while true ; do
sleep 5
if [[ "$PRE_WAIT" != "$( pg_controldata $PGDATA | awk -F: '/
Latest checkpoint location/ { print $2 }' )" ]] ; then
log "Checkpoint."
return
fi
done
}
The grandchild database log has the following records about the
recovery process::
2010-08-06 09:48:31.266 CEST,,,30649,,4c5bbe46.77b9,15,,2010-08-06
09:48:22 CEST,,0,LOG,00000,"restored log file
""000000010000005B000000DC"" from archive",,,
,,,,,,""
2010-08-06 09:48:31.564 CEST,,,30649,,4c5bbe46.77b9,16,,2010-08-06
09:48:22 CEST,,0,LOG,00000,"consistent recovery state reached at 5B/
DD000000",,,,,,,,,""
2010-08-06 09:48:31.751 CEST,,,30649,,4c5bbe46.77b9,17,,2010-08-06
09:48:22 CEST,,0,LOG,00000,"restored log file
""000000010000005B000000DD"" from archive",,,
,,,,,,""
Regards,
-- Chris