Обсуждение: Rescue data after power off

Is there anyway to rescue data afer power off.
I have postgres database version 8.3.9 on windows 2003.
Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it.

Please help me. I am very confuse.
Tuan Hoang Anh

In response to tuanhoanganh :
> Is there anyway to rescue data afer power off.
> I have postgres database version 8.3.9 on windows 2003.
> Yesterday my server is power off, when i start server, some of table is blank.
> Is there anyway to rescue it.

Restore the data from your backup. You haven't a backup? Your problem.



Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Yes, I don't have backup.

When I use pgadmin to view data of table dmvt, it have data. But when I "select * from dmvt" there is error on log

2010-07-19 17:32:17 ICTWARNING:  invalid page header in block 207 of relation "dmvt"; zeroing out page
2010-07-19 17:32:35 ICTLOG:  server process (PID 3480) was terminated by exception 0xC0000005
2010-07-19 17:32:35 ICTHINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2010-07-19 17:32:35 ICTLOG:  terminating any other active server processes
2010-07-19 17:32:35 ICTWARNING:  terminating connection because of crash of another server process
2010-07-19 17:32:35 ICTDETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-07-19 17:32:35 ICTHINT:  In a moment you should be able to reconnect to the database and repeat your command.
2010-07-19 17:32:35 ICTWARNING:  terminating connection because of crash of another server process
2010-07-19 17:32:35 ICTDETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-07-19 17:32:35 ICTHINT:  In a moment you should be able to reconnect to the database and repeat your command.
2010-07-19 17:32:36 ICTLOG:  all server processes terminated; reinitializing
2010-07-19 17:32:46 ICTFATAL:  pre-existing shared memory block is still in use
2010-07-19 17:32:46 ICTHINT:  Check if there are any old server processes still running, and terminate them.

Is there anyway to fix it.

> Yesterday my server is power off, when i start server, some of table is
blank. Is there anyway to rescue it.
The "Power Off" - is it a planned/regular one? If yes, it makes sense to
have a normal shutdown of the database before the "Power Off".
You said "some of table is blank". If those tables just got populated
before the "Power Off" and the transactions were committed, PostgreSQL
might have used WALS and recovered.
Some information on what data was expected to be in those tables and how
they were populated , messages you got when you restarted PostgreSQL
server etc might give a better idea to those in forum who are in a
position to help.
Regards,
Jayadevan



From:   tuanhoanganh <hatuan05@gmail.com>
To:     pgsql-general@postgresql.org
Date:   07/19/2010 02:45 PM
Subject:        [GENERAL] Rescue data after power off
Sent by:        pgsql-general-owner@postgresql.org



Is there anyway to rescue data afer power off.
I have postgres database version 8.3.9 on windows 2003.
Yesterday my server is power off, when i start server, some of table is
blank. Is there anyway to rescue it.

Please help me. I am very confuse.
Tuan Hoang Anh







DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On 19/07/10 19:02, Jayadevan M wrote:
>> Yesterday my server is power off, when i start server, some of table is
> blank. Is there anyway to rescue it.
> The "Power Off" - is it a planned/regular one? If yes, it makes sense to
> have a normal shutdown of the database before the "Power Off".

... but even if you yank the power plug out of the back of the server,
PostgreSQL should *NEVER* lose comitted data (unless you've told it its
allowed to with a commit delay) and should certainly NEVER damage the
database structure. It's one of PostgreSQL's most important and basic
features.

Unless you have set "fsync=off" in postgresl.conf, in which case the
data loss is entirely your own fault, this should not happen. If you do
not know if fsync is on or off, check by running "SHOW fsync;" in psql
or PgAdmin. It should say "on". If it says "off" then you or someone
else manually told the database not to try to protect your data from
power loss or other failures.

If you have lost data and fsync is on, then most likely your RAID
controller/disks are doing something they should not be like caching
writes in volatile storage. Make sure your RAID controller has write
caching disabled or has a tested and working battery backup unit. If in
doubt, your raid controller should have its write cache turned off, ie
it should be in "write-through" mode and NOT in "write-back" mode.

--
Craig Ringer

On 19/07/10 18:37, tuanhoanganh wrote:
> When I use pgadmin to view data of table dmvt, it have data. But when I
> "select * from dmvt" there is error on log
>
> 2010-07-19 17:32:17 ICTWARNING:  invalid page header in block 207 of
> relation "dmvt"; zeroing out page
> 2010-07-19 17:32:35 ICTLOG:  server process (PID 3480) was terminated by
> exception 0xC0000005

If that message is always the same, it's likely that there's a bug
somewhere in the PostgreSQL database backend's handling of damaged
database files.

This almost certainly has nothing to do with whatever damaged the data
in the first place. Fixing it is very unlikely to bring your data back.

Your database file is still damaged, and if this error wasn't happening
it'd probably just give you an error saying it can't read the relation
instead of terminating like that. Nonetheless, it'd be nice to know
where it is crashing.


Since you can make the crash happen reliably every time, please consider
following the instructions at:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

to collect some debugging information that might help.

It is unlikely that fixing this problem will bring your data back, but
it might provide more information that could help.


Please also post your postgresql.conf .

--
Craig Ringer

I have checked SHOW fsync, It is ON.
When I view table dmvt on PgAdmin, it only has 1332 rows, but command "SELECT count(*) FROM dmvt" return 2449 rows.

My postgresql.conf is default of EnterpriseDB Postgres 8.3.9.

Please help me. Sorry for my English.
Tuan Hoang Anh.

On Mon, Jul 19, 2010 at 5:57 PM, tuanhoanganh <hatuan05@gmail.com> wrote:
> I have checked SHOW fsync, It is ON.
> When I view table dmvt on PgAdmin, it only has 1332 rows, but command
> "SELECT count(*) FROM dmvt" return 2449 rows.

By default pgAdmin shows the number of rows listed in pg_class, which
is not always accurate (for all but the smallest of tables).
Right-click the table and select the count options to get an exact
count.

Normally, a large mismatch between those two values is a sign you need
to vacuum the table. That may well not be the case if you're suffering
from corruption though.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company