Re: Postgres crashes,help to recover

Andrus wrote:
> I have Postgres 8 running on Windows XP
> The size of data subdirectory is 326 MB
>
> 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?

> 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?
You can't take file-backups of anything less than the entire data directory.

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

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

And do your disks honour the fsync? Was a power failure the cause of this?

> Log when trying to backup table:
>
> .....
> pg_dump: restoring data for table "klient"
> pg_dump: dumping contents of table klient
> pg_dump: ERROR: out of memory
> DETAIL: Failed on request of size 544565107.

Well - unless you have a piece of data that's 544MB that certainly looks
like corruption.
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.

--
Richard Huxton
Archonet Ltd