SEO

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.

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.

How to get the database back working by repairing firma1.klient table or by
restoring this from schema backup.
Why Postgres crashes ? I use default postgres.conf file which has probably
fsync on


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.
pg_dump: SQL command to dump the contents of table "klient" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR: out of memory
DETAIL: Failed on request of size 544565107.
pg_dump: The command was: COPY firma1.klient (kood, nimi, a_a, p_kood,
regnr, vatpayno, piirkond, postiindek, tanav, kontaktisi, telefon, faks,
email, wwwpage, liik, viitenr, riik, riik2, riigikood, hinnak, erihinnak,
myygikood, objekt2, objekt5, objekt7, maksetin, omakseti, krediit,
ostukredii, masin, info, maksja, "timestamp", atimestamp, elanikud, pindala,
grmaja, apindala, kpindala, idmakett, tulemus, omandisuhe, username,
changedby, parool, hinnaale, mitteakt, kontakteer, klikaart, mhprotsent,
aadress, grupp, verskp, firma_enne, tegevusala, instkuupae, firmarv,
tookohti, versioon, teenlepkp, jur, kasutab, est, rus, miniest, minirus,
plakat, keel) TO stdout;
pg_dump: *** aborted because of error

Process returned exit code 1.

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

It seems that corrupted table klient contains data from some other table.

select * from firma1.klient limit 3686;

Seems to return all data from table.
Starting at row 3687 table contains data from other table.

VACUUM command returns:

INFO: vacuuming "firma1.klient"

ERROR: invalid page header in block 1639 of relation "klient"

klient table has oid 66079

file 66079 size is 13 MB

in correct copy klient table file size is 5MB

it seems that other table, nomenkla oid=65783 is added to the end of
klient table.

How to repair file 66079 so that it contains only 3686 rows from beginning ?