>> 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. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
