Thread: corrupted table postgresql 8.3
Hi people! I have a pg 8.3. Today I issued in a database that comand: =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE; WARNING: unexpected attrdef record found for attr 22 of rel cliente WARNING: unexpected attrdef record found for attr 22 of rel cliente WARNING: unexpected attrdef record found for attr 22 of rel cliente ALTER TABLE Time: 1184.404 ms After that the table was empty. SELECT * from cliente; 0 rows ;) Should contain about 90k records. I checked in the logs and there are not disk/memory issues on the server. If I try to execute a vacuum full I get this error. ERROR: could not open relation 1663/36509/28638634: No such file or directory At this time I saw two entries of table "cliente" in the pg_tables. At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation. I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog to anotherpg server 8.3. after that in the server where i got that problem I did this: - renamed the table to cliente_prova - removed all the foreign key that are pointing to that table - recreated the table - populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable,but I don't know if the method that I used to "recover" the table was technically correct... It was a try...) - all up and running again Now i'm facing this: - if I type \d cliente, I see the schema twice, one without the field "pwd_expired" and the right one that is without it(the table that I restored) - unable to drop the table renamed cliente_prova =# DROP TABLE cliente_prova; WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova NOTICE: default for table cliente column id depends on sequence cliente_id_seq WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova ERROR: cannot drop table cliente_prova because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. I'll stop here to describe further detail... can you suppose what is happened please? After that can we discuss how to cleanup the situation...;) Thanks a lot! Matteo
On 6.3.2012 21:24, Matteo Sgalaberni wrote: > Hi people! > > I have a pg 8.3. Today I issued in a database that comand: Which minor version? The last one in this branch is 8.3.18 and if you're running an old one, there might be an important bugfix ... > =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE; > WARNING: unexpected attrdef record found for attr 22 of rel cliente > WARNING: unexpected attrdef record found for attr 22 of rel cliente > WARNING: unexpected attrdef record found for attr 22 of rel cliente > ALTER TABLE > Time: 1184.404 ms > > After that the table was empty. > > SELECT * from cliente; > 0 rows ;) > Should contain about 90k records. > > I checked in the logs and there are not disk/memory issues on the server. That proves nothing. It might be a PostgreSQL bug but just as well it might be a silent disk corruption somewhere, unspotted for a long time. > If I try to execute a vacuum full I get this error. > ERROR: could not open relation 1663/36509/28638634: No such file or directory > > At this time I saw two entries of table "cliente" in the pg_tables. > > At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation. > > I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog toanother pg server 8.3. > after that in the server where i got that problem I did this: Not sure what you mean by 'physical data file of the cluster' but you should do a file-level backup of the whole cluster right now. Before trying to fix the issues (possibly damaging the data). Then get the last 8.3.x release (if you're using an old one). > - renamed the table to cliente_prova > - removed all the foreign key that are pointing to that table > - recreated the table > - populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable,but I don't know if the method that I used to "recover" the table was technically correct... It was a try...) > - all up and running again What do you mean by 'populated the table' with the production data? How did you do that? kind regards Tomas
----- Original Message ----- > On 6.3.2012 21:24, Matteo Sgalaberni wrote: > > Hi people! > > > > I have a pg 8.3. Today I issued in a database that comand: > > Which minor version? The last one in this branch is 8.3.18 and if > you're > running an old one, there might be an important bugfix ... 8.3.0, I read quickly all releases notes of 8.3.x and I didn't saw any fix that could be directly related to what is happenedto me...there are issues related to ALTER TABLE but with other options like PRIMARY KEY or other parameters. Are there fixes that could be related to what's happened to me? > Not sure what you mean by 'physical data file of the cluster' but you > should do a file-level backup of the whole cluster right now. Before > trying to fix the issues (possibly damaging the data). > I copied the file of the filesystem that contain the table. (grep exampledata * and found the $file that contain the tabledata). On another server i did this: CREATE TABLE cliente... SELECT oid,relname from pg_class where relname = 'cliente'; cp $file $oid cp $production_pg_clog local/pg_clog/ SELECT id,etc.... from table into tmp_table; all my recovered data was into tmp_table dumped the tmp_table and copied to the production server > What do you mean by 'populated the table' with the production data? > How > did you do that? on the production server: ALTER TABLE cliente RENAME TO cliente_prova; ALTER TABLE DROP CONSTRAINT etc.... (removed all foreign key that are pointing to cliente) CREATE TABLE cliente .... psql database < dumpof_tmp_table.sql So I recovered the table cliente. Now, if I try to drop the cliente_prova I receive the error posted in the previous email, and if I type the \d cliente on psql, it return the schema twice of the table "cliente"... For cleanup the situation I think that I need to remove the "old table" and repair the catalog that seems to be damaged... What do you suggest me? Thanks! Matteo
On Tue, Mar 6, 2012 at 6:34 PM, Matteo Sgalaberni <sgala@sgala.com> wrote: > ----- Original Message ----- >> On 6.3.2012 21:24, Matteo Sgalaberni wrote: >> > Hi people! >> > >> > I have a pg 8.3. Today I issued in a database that comand: >> >> Which minor version? The last one in this branch is 8.3.18 and if >> you're >> running an old one, there might be an important bugfix ... > > 8.3.0, I read quickly all releases notes of 8.3.x and I didn't saw any fix that could be directly related to what is happenedto me...there are issues related to ALTER TABLE but with other options like PRIMARY KEY or other parameters. > Are there fixes that could be related to what's happened to me? hm: * Also, a VACUUM FULL that failed partway through vacuuming a system catalog could result in cache corruption in concurrent database sessions. * Fix btree index corruption from insertions concurrent with vacuuming (Tom Lane) * These bugs could result in index corruption after reindexing a system catalog. They are not believed to affect user indexes. I mostly agree, but it's possible you got hit by a fixed bug. merlin
----- Original Message ----- > I mostly agree, but it's possible you got hit by a fixed bug. I'm lucky ;) someone know how I can correct the catalog and drop the old/renamed table? Or probably is simpler: - pg_dump database - DROP database - CREATE DATABASE - psql database < dump Or do I need to destroy and init the whole pg cluster? I would like to be pretty sure of what can happen so I can plan the maintenance window... Thanks for any hint! Matteo