Re: corrupted table postgresql 8.3

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 to another 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