Re: rebuilding a table from a datafile

From: "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu>
To: brianb-pggeneral(at)edsamail(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: rebuilding a table from a datafile
Date: 2000-08-16 15:51:25
Message-ID: 20000816105125.B7283@rice.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephan Szabo 2000-08-16 16:11:03 Re: CREATE TABLE from inside a function...
Previous Message Ned Lilly 2000-08-16 15:43:01 Re: AS3AP Implementation for PostgreSQL/Linux ?