Обсуждение: Postgresql + corrupted disk = data loss. (Need help for database recover)

Hello, i have a problem.

I've got a production server, working fine. Then i've got strange error:
> ERROR:  right sibling's left-link doesn't match: block 147 links to 407 instead of expected 146 in index "order_status_key"'
And decidet to backup all server. So i shut-down VPS with server and backup all data.
Then, after i booted it - and then - i've got Data loss.

I've lost data, that have been written to DB around 10-100 hours (different tables, have different last updated value).

Then i've analyzed log, and found this:
7 days ago appears this errors:
db= LOG:  could not rename temporary statistics file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat":
db= WARNING:  pgstat wait timeout
 ERROR:  missing chunk number 0 for toast value 2550017 in pg_toast_17076

5 days ago:
a lot of: ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/526512E0
  83238 db= WARNING:  could not write block 54 of base/16384/2619
  83239 db= CONTEXT:  writing block 54 of relation base/16384/2619

And today:
     18 db= LOG:  could not open file "pg_xlog/000000010000000F00000052" (log file 15, segment 82):
     19 db= ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/52FDF0E0
   
There is any ability to recover fresh data from database?

Thanks!

And, i'm an idiot.

My DB version:
PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit

2011/12/2 Oleg Serov <serovov@gmail.com>

Hello, i have a problem.

I've got a production server, working fine. Then i've got strange error:
> ERROR:  right sibling's left-link doesn't match: block 147 links to 407 instead of expected 146 in index "order_status_key"'
And decidet to backup all server. So i shut-down VPS with server and backup all data.
Then, after i booted it - and then - i've got Data loss.

I've lost data, that have been written to DB around 10-100 hours (different tables, have different last updated value).

Then i've analyzed log, and found this:
7 days ago appears this errors:
db= LOG:  could not rename temporary statistics file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat":
db= WARNING:  pgstat wait timeout
 ERROR:  missing chunk number 0 for toast value 2550017 in pg_toast_17076

5 days ago:
a lot of: ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/526512E0
  83238 db= WARNING:  could not write block 54 of base/16384/2619
  83239 db= CONTEXT:  writing block 54 of relation base/16384/2619

And today:
     18 db= LOG:  could not open file "pg_xlog/000000010000000F00000052" (log file 15, segment 82):
     19 db= ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/52FDF0E0

    
There is any ability to recover fresh data from database?

2011/12/2 Oleg Serov <serovov@gmail.com>

And, i'm an idiot.

My DB version:
PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit

2011/12/2 Oleg Serov <serovov@gmail.com>

Hello, i have a problem.

I've got a production server, working fine. Then i've got strange error:
> ERROR:  right sibling's left-link doesn't match: block 147 links to 407 instead of expected 146 in index "order_status_key"'
And decidet to backup all server. So i shut-down VPS with server and backup all data.
Then, after i booted it - and then - i've got Data loss.

This seems to be an Index corruption. Did you try re-indexing ? Index creation might have failed, re-indexing would re-organize the Index tuples. If you are sure about disk corruption, try and "re-create" or "create concurrent Index" on a different disk.

 

I've lost data, that have been written to DB around 10-100 hours (different tables, have different last updated value).

Then i've analyzed log, and found this:
7 days ago appears this errors:
db= LOG:  could not rename temporary statistics file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat":
db= WARNING:  pgstat wait timeout
 ERROR:  missing chunk number 0 for toast value 2550017 in pg_toast_17076

This should be a free space issue, do you have enough space in "pg_stat_tmp" disk ?

5 days ago:
a lot of: ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/526512E0
  83238 db= WARNING:  could not write block 54 of base/16384/2619
  83239 db= CONTEXT:  writing block 54 of relation base/16384/2619

And today:
     18 db= LOG:  could not open file "pg_xlog/000000010000000F00000052" (log file 15, segment 82):
     19 db= ERROR:  xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/52FDF0E0

    
There is any ability to recover fresh data from database?

What kind of backups you have available ?

Thanks

VB

On 2.12.2011 09:16, Oleg Serov wrote:
> Hello!
>
> i've don't try to do reindex. There was enough space.

Not sure whether you tried to reindex or not. And what do you mean by
'there was enough space'? For example with ext2 (and ext3/ext4) it was
rather simple to exhaust inodes long before the device was actually
full. What filesystem are you using, anyway?

This seems like a I/O issue, you should check the hardware and the
settings (e.g. what caches are enabled etc.). Post more details, if
possible. Have you checked S.M.A.R.T. info from the drives?

> And i have a full data-directory backup, when i've stop server, before
> start.

Good. Have you moved it to a different machine? Otherwise you don't have
a backup, just a copy.

Tomas

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