Re: Postgres crashes,help to recover

>> 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.