Thread: DATA corruption after promoting slave to master

Hello,

We are facing issues of some DB inconsistencies, while promoting the
slave to master.


[1] We have master-slave with Hot Standby Setup (log shipping).
[2]  We also have a daemon process which copies the latest partial WAL
log file (which postgres is currently writing to, under pg_xlog/) every
3 secs to a different location.

Master server crashed. We decided to promote the slave to master.
Before, creating the trigger file we passed the partial WAL log file [2]
and made the slave replay the partial file as well(So that the data loss
is minimal). Following link has the errors that we saw at different stage:

http://pastebin.com/raw.php?i=Ekr0VrQA

We are seeing these problems in the newly promoted master now:

     1. when we run queries on primary key, we don't get the rows even
if it exist in db. However if we force query not to use index, we get
those entries.
     2. there are duplicate values for primary keys


Postgres version that we use:

9.0.13

Postgres conf : http://pastebin.com/raw.php?i=B0diaitG

Any help here is appreciated.

Thanks in advance.

- Karthik Iyer

On 06/25/2014 06:29 AM, Karthik Iyer wrote:

> [2]  We also have a daemon process which copies the latest partial WAL
> log file (which postgres is currently writing to, under pg_xlog/) every
> 3 secs to a different location.

No. No, no, no, no no. No.

Also, no. Partial WAL files are not valid for recovery. In fact, I'm
surprised the standby node even applied it at all.

> We are seeing these problems in the newly promoted master now:
>
>      1. when we run queries on primary key, we don't get the rows even
> if it exist in db. However if we force query not to use index, we get
> those entries.
>      2. there are duplicate values for primary keys

This is no surprise. Your slave has partial data commits, which means
your table, index, or possibly both, are corrupt.

The first thing you need to do is back up any tables you've noticed are
having this behavior. Second, try to reindex the tables that are having
problems. The errors you are seeing are due to the data and indexes
being out of sync. If you get an error that says the reindex fails due
to duplicate values, you can do this:

SET enable_indexscan TO false;
SET enable_bitmapscan TO false;

SELECT primary_key, count(1)
   FROM broken_table
  GROUP BY 1
HAVING count(1) > 1;

For any ID that comes back, do this:

SELECT ctid, *
   FROM broken_table
  WHERE primary_key = [value(s) from above];

Then you need to delete one of the bad rows after deciding which. Use
the CTID of the row you want to delete:

DELETE FROM broken_table
  WHERE ctid = 'whatever';

Then reindex the table so the correct values are properly covered. Doing
this for all of your corrupt tables may take a while depending on how
many there are.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 06/26/2014 09:44 AM, Karthik Iyer wrote:

> We reindexed  all the primary and unique keys of all the tables, But we
> did not reindex the tables. You think we should do that also ?

Yes, you need to reindex. Part of the problem with this kind of table
corruption, is that PostgreSQL has applied data and index page
modifications to the binary files themselves. Due to this, there are
probably index rows pointing to incorrect or invalid data pages.

Reindexing the primary and unique indexes is a very good start, but any
queries that use regular indexes can still return invalid or mismatched
rows when compared to the primary/unique results. Those kind of
mismatches can result in unexpected behavior from applications.

> We managed to get the old server up. We are trying to play the
> difference in data by checking the log files(statement logs). You think
> there is any other easy alternatives ?

Probably not. Now that your slave has been promoted and had its own
write activity, the old primary is not useful as a source of binary
replay. You *can* use it to verify which rows are old/new while vetting
out corruption, though. It's a good source of data for checking the
consistency of the new primary.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 06/26/2014 10:34 AM, Karthik Iyer wrote:

> Any inputs here? You think a pgdump and restore would help more ?

A full dump and restore would definitely help. I tend not to suggest
that often because I work with very large databases that are usually
extremely cumbersome to dump and restore.

But yeah, if you can get a successful pg_dump from your database, a
restore should obviously clean up all of your data and index
inconsistencies if you're willing to wait.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Thanks Shaun.

We reindexed  all the primary and unique keys of all the tables, But we
did not reindex the tables. You think we should do that also ?

Also, do you think we should do a clean dump restore to eliminate all
data inconsistencies.

One more query :

We managed to get the old server up. We are trying to play the
difference in data by checking the log files(statement logs). You think
there is any other easy alternatives ?

Thanks in advance,

- Karthik


On Thursday 26 June 2014 01:09 AM, Shaun Thomas wrote:
> On 06/25/2014 06:29 AM, Karthik Iyer wrote:
>
>> [2]  We also have a daemon process which copies the latest partial WAL
>> log file (which postgres is currently writing to, under pg_xlog/) every
>> 3 secs to a different location.
>
> No. No, no, no, no no. No.
>
> Also, no. Partial WAL files are not valid for recovery. In fact, I'm
> surprised the standby node even applied it at all.
>
>> We are seeing these problems in the newly promoted master now:
>>
>>      1. when we run queries on primary key, we don't get the rows even
>> if it exist in db. However if we force query not to use index, we get
>> those entries.
>>      2. there are duplicate values for primary keys
>
> This is no surprise. Your slave has partial data commits, which means
> your table, index, or possibly both, are corrupt.
>
> The first thing you need to do is back up any tables you've noticed
> are having this behavior. Second, try to reindex the tables that are
> having problems. The errors you are seeing are due to the data and
> indexes being out of sync. If you get an error that says the reindex
> fails due to duplicate values, you can do this:
>
> SET enable_indexscan TO false;
> SET enable_bitmapscan TO false;
>
> SELECT primary_key, count(1)
>   FROM broken_table
>  GROUP BY 1
> HAVING count(1) > 1;
>
> For any ID that comes back, do this:
>
> SELECT ctid, *
>   FROM broken_table
>  WHERE primary_key = [value(s) from above];
>
> Then you need to delete one of the bad rows after deciding which. Use
> the CTID of the row you want to delete:
>
> DELETE FROM broken_table
>  WHERE ctid = 'whatever';
>
> Then reindex the table so the correct values are properly covered.
> Doing this for all of your corrupt tables may take a while depending
> on how many there are.
>

We reindexed  all the primary and unique keys of all the tables, But we
did not reindex the tables. You think we should do that also ?

Yes, you need to reindex. Part of the problem with this kind of table corruption, is that PostgreSQL has applied data and index page modifications to the binary files themselves. Due to this, there are probably index rows pointing to incorrect or invalid data pages.

Reindexing the primary and unique indexes is a very good start, but any queries that use regular indexes can still return invalid or mismatched rows when compared to the primary/unique results. Those kind of mismatches can result in unexpected behavior from applications.

Also, do you think we should do a clean dump restore to eliminate all data inconsistencies.

> A full dump and restore would definitely help. I tend not to suggest
> that often because I work with very large databases that are usually
> extremely cumbersome to dump and restore.
>
> But yeah, if you can get a successful pg_dump from your database, a
> restore should obviously clean up all of your data and index
> inconsistencies if you're willing to wait.

Thanks a lot Shaun. Appreciate the help.

- Karthik

Hi Krit,

It looks like your actual problem is here:

>  Index Scan using t1_orderid_creationtime_idx on t1
>  (cost=0.43..1181104.36 rows=9879754 width=158)
>  (actual time=0.021..60830.724 rows=2416614 loops=1

This index scan estimates 9.8M rows, and had to touch 2.4M. The issue is that your LIMIT clause makes the planner
overlyoptimistic. The worst case cost estimate for this part of the query is about 1.2M, which is much higher than the
SEQSCAN variation you posted. The planner must think it can get the rows without incurring the full cost, otherwise I
can'tsee how the 1.2M cost estimate wasn't rolled into the total estimate. 

Unfortunately behavior like this is pretty common when using LIMIT clauses. Sometimes the planner thinks it can get
resultsmuch faster than it actually can, and it ends up reading a much larger portion of the data than it assumed would
benecessary. 

Just out of curiosity, Can you tell me what your default_statistics_target is?

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email