Re: rebuilding a table from a datafile - Mailing list pgsql-general

brianb-pggeneral@edsamail.com writes:
> This is with regards to Postgres 6.5.
> While trying to ALTER RENAME a large table (203MB data file), an error
> occured.

Table RENAME is pretty risky under 6.5 :-(.  7.0 is a little better,
but RENAME will never be trustworthy until we abandon the linkage
between file names and table names.

> \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.

You could probably get away with

    CREATE TABLE someothername (same parameters)

and then rm the (presumably 0-size) file 'someothername' and copy the
original-table-named file to that file name.  This will not fill the
indexes, if any, on the new table, so drop and recreate them afterwards.

After that, update to 7.0.2 ;-)

            regards, tom lane