Re: [GENERAL] Rescue data after power off
On Mon, Jul 19, 2010 at 5:57 PM, tuanhoanganh 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rescue data after power off
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 9:36 PM, Craig Ringer wrote: > 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 >
Re: [GENERAL] Rescue data after power off
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 0xC005 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rescue data after power off
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rescue data after power off
> 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 To: pgsql-general@postgresql.org Date: 07/19/2010 02:45 PM Subject: [GENERAL] Rescue data after power off Sent by:pgsql-general-ow...@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." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rescue data after power off
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 0xC005 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. On Mon, Jul 19, 2010 at 5:00 PM, tuanhoanganh wrote: > Yes, I don't have backup. > > On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer < > andreas.kretsch...@schollglas.com> wrote: > >> 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 >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
Re: [GENERAL] Rescue data after power off
Yes, I don't have backup. On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Rescue data after power off
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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