Re: WARNINGs after starting backup server created with PITR

On Jan 19, 2008, at 2:26 AM, Simon Riggs wrote:

> On Fri, 2008-01-18 at 19:58 -0500, Tom Lane wrote:
>> In any case, 125 different zeroed pages is pretty hard to explain
>> by such a mechanism (especially if they were scattered rather than
>> in contiguous clumps).
>
> Can you show us the messages, so we can understand the distribution of
> the pages?
>
> Are there different numbers of rows in the two tables? Just a select
> count(*) might do, but any way you have of verifying data between the
> two systems would be very useful.
>
> The page numbers are identical between both systems, so use the
> contrib/pageinspect get_raw_page() function to record the contents on
> both systems before they diverge too much. (BTW, the final commit of
> those tools seems to have removed the docs I wrote for the original
> version and haven't been replaced with a README -- huh!?).
>
> create table bad_blocks as select <blockid> as blockid,
> get_raw_page(<relname>, <blockid>)::bytea as raw_page;
>
> I've got a bad feeling about rsync. Mason's recent problems used rsync
> and so far they are not properly explained, except as a hardware
> problem.

All of the warnings are below. For tables that had multiple warnings
they seem to be for consecutive pages. All of these tables were
seeing some pretty decent write traffic during the base backup which
took place Tuesday night. The good news, for us, is that none of the
queue tables matter -- that was transient data. For the other two
history tables, since the PITR recovery ended at 4:58 am Wednesday
morning when it ran up against the dropped WAL file they've already
seen a good amount of traffic. However, I've been able to verify
missing data by checking the newest timestamp field is older than
those in other tables that had no errors, finding differences of as
much as 11 hours.

If the page_inspect module would work with 8.2 I wouldn't have a
problem building and using it, I already do that with pg_standby.
But, with three days of traffic on these table in the production
server and, having already verified some missing data, I don't see
the point now. Let me know if it would work, though, so that in
future I'll be aware of an available tool a little sooner.

And, yes, rsync does seem to be suspect, especially given that I'm
not the only one who has reported issues when using it. So far,
though, we haven't had any issues when using it over local networks,
just between our co-locations. As I mentioned before, where WAL
shipping is concerned, this can be beaten with a more complete and
versatile WAL shipping solution which will be a new personal project
for me. For base backups, though, it's probably time to explore
other options here. Also, since iirc, rsync is mentioned directly in
at least one of the chapters dealing with PITR, it may be a good idea
to qualify it's usage with a warning to the effect that some people
have reported inconsistent results from it's use.

2008-01-17 17:34:57 CST 7598 :WARNING: relation
"userdata_4212_messages_queue" page 170 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1320 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1321 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1322 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1323 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1324 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1325 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1326 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1327 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1328 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1329 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1330 is uninitialized --- fixing
2008-01-17 17:44:48 CST 7598 :WARNING: relation
"userdata_12176_messages_queue" page 1331 is uninitialized --- fixing
2008-01-17 19:57:05 CST 7598 :WARNING: relation
"userdata_8520_messages_queue" page 90 is uninitialized --- fixing
2008-01-17 20:02:33 CST 7598 :WARNING: relation
"userdata_1708_messages_queue" page 262 is uninitialized --- fixing
2008-01-17 20:03:36 CST 7598 :WARNING: relation
"userdata_7496_messages_queue" page 145 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30712 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30713 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30714 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30715 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30716 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30717 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30718 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30719 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30720 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30721 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30722 is uninitialized --- fixing
2008-01-17 21:10:04 CST 7598 :WARNING: relation
"userdata_7829_messages_history" page 30723 is uninitialized --- fixing
2008-01-17 21:42:05 CST 7598 :WARNING: relation
"userdata_16498_messages_queue" page 159 is uninitialized --- fixing
2008-01-17 21:42:05 CST 7598 :WARNING: relation
"userdata_16498_messages_queue" page 160 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8491 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8492 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8493 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8494 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8495 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8496 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8497 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8498 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8499 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8500 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8501 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8502 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8503 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8504 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8505 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8506 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8507 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8508 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8509 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8510 is uninitialized --- fixing
2008-01-17 21:47:05 CST 7598 :WARNING: relation
"userdata_16649_messages_history" page 8511 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5671 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5672 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5673 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5674 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5675 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5676 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5677 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5678 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5679 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5680 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5681 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5682 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5683 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5684 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5685 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5686 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5687 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5688 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5689 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5690 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5691 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5692 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5693 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5694 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5695 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5696 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5697 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5698 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5699 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5700 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5701 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5702 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5703 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5704 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5705 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5706 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5707 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5708 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5709 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5710 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5711 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5712 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5713 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5714 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5715 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5716 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5717 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5718 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5719 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5720 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5721 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5722 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5723 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5724 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5725 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5726 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5727 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5728 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5729 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5730 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5731 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5732 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5733 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5734 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5735 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5736 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5737 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5738 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5739 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5740 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5741 is uninitialized --- fixing
2008-01-17 21:47:34 CST 7598 :WARNING: relation
"userdata_16649_messages_queue" page 5742 is uninitialized --- fixing
2008-01-17 22:09:35 CST 7598 :WARNING: relation
"userdata_14692_messages_queue" page 449 is uninitialized --- fixing
2008-01-17 22:17:49 CST 7598 :WARNING: relation
"userdata_15375_messages_queue" page 24 is uninitialized --- fixing

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com