From: | "Andrus" <eetasoft(at)online(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Postgres crashes,help to recover |
Date: | 2005-07-20 09:04:54 |
Message-ID: | dbl443$kq0$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> Today morning suddenly one table in one database, firma1.klient is
>> corrupted. When trying to backup it using pgAdmin III I get the log
>> below and backup is not created.
>
> What caused this? Presumably you had a power/system-failure or similar?
Windows XP does not respond and I pressed reset key yesterday evening.
However, after that the database continues working yesterday.
I use default postgres.conf file created by installer. In my knowledge this
crash does not occur.
I'm very intresting about reasons of this crash.
I created copy of the whole data directory.
running
select * from firma1.klient;
from pgAdmin yields:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
in windows eventlog this writes:
LOG: checkpoint record is at 0/4FD3ECB8
LOG: all server processes terminated; reinitializing
WARNING: terminating connection because of crash of another server process
DETAIL: 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.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: terminating any other active server processes
LOG: server process (PID 2756) was terminated by signal 5
>> I have:
>>
>> 1. Compressed backup of the whole database as of 15.7
>> 2. Compressed backup of the firma1 schema of this database as of 19.7
>> where corrupted table klient resides.
>
> Do you mean file-level backups, or backups taken using pg_dump/pgadmin?
I have compressed backups created using pgadmin.
Whole database backup (database contains two, andmed and firma1 schemas) is
from 15.7 and fresh, firma1 schema backup is form 19.7
>> How to get the database back working by repairing firma1.klient table or
>> by restoring this from schema backup.
>
> If your backup is recent enough, that's probably the quickest route.
My problem is that I have whole backup only as 15.7 evening
I have the current backup (as 19.7 evening) only firma1 schema.
Corrupted table klient resides in firma1 schema.
I need to restore firma1 backup as of 19.7 to the new database created from
15.7 backup.
Unfortunately, firma1 schema is cross-referenced with other schema (andmed)
So I have no idea how to restore.
Is it possible to convert compressed backup file to plain text or to get
data from it ?
>> Why Postgres crashes ? I use default postgres.conf file which has
>> probably fsync on
>
> *WHEN* did Postgresql crash, originally that is? This error was caused by
> something - when did something go horribly wrong?
Yesterday evening I pressed the reset button because windows task manager
stops responding ( By experimenting with setforegroundwindow Windows API
call I ran 20 copies of charmap.exe and tried to kill them all from task
manager). However, after re-booting computer database continues working
yesterday.
I also restored new database yesterday with 500 tables to this cluster.
> And do your disks honour the fsync? Was a power failure the cause of this?
I have usual office PC using Quantum FireballP LM20.5 20 GB IDE HDD with
XP drivers.
How to determine is fsync working or not ?
I use default postgres.conf file ( added only listen_addresses = '*' )
>> Log when trying to backup table:
>>
>> .....
> Well - unless you have a piece of data that's 544MB that certainly looks
> like corruption.
Tables are small. Whole data directory (including wal segments and 2 other
nonimportant databases) sizes is about 350 MB.
> It's entirely possible you can identify the row that's causing this
> problem and dump all the data either side of it. However, if your backup
> is good, then I'd just restore that.
I have up-to date backup of firma1 schema only. Whole backup is a bit old.
Is it possible to dump the corrupted table, truncate it and re-load it? I
think thank referential integrity is not checked in truncate and refrential
integrity does not prevent loading this table.
Will truncate command fix the corrupted table?
Andrus.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2005-07-20 09:20:20 | Re: Postgres crashes,help to recover |
Previous Message | Richard Huxton | 2005-07-20 08:15:16 | Re: Postgres crashes,help to recover |