Re: rebuilding a table from a datafile

On Wed, Aug 16, 2000 at 02:36:10PM +0000, brianb-pggeneral(at)edsamail(dot)com wrote:
>
> This is with regards to Postgres 6.5.
>
> While trying to ALTER RENAME a large table (203MB data file), an error
> occured. \d displays the new table name, but there is no corresponding
> file. a file with the original table name still exists, so presumably the
> data is not lost. Is it possible for me to undo the RENAME by massaging the
> pg_* tables, or otherwise retrieve the data in the table? It was an
> insert-only table, and no deletes or updates were ever performed on it.

Hmm, what kind of failure mode caused this? Reading the renamrel code for
6.5.3, it does the file rename first, then updates pg_class. Anyway,
I'd try manually renaming the file to the name that appears in pg_class,
rather than trying to fixup the system relations. There're indices on
pg_class that're hard to rebuild under 6.5.X.

If that doesn't work, try updating pg_class.relname to mach the filename.
you'll need to be a DB superuser to do that.

Oh, make a backup of the table file first, just in case.

In any case, once you can read the table, do a dump/restore cycle. And
think about upgrading to 7.0.X. As someone else said, it's good, and
stay's crunchy in milk! (Seriously, lots of bug fixes)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005