Re: rebuilding a table from a datafile

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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-19 03:40:34
Message-ID: 21622.966656434@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

brianb-pggeneral(at)edsamail(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2000-08-19 03:45:08 Re: AS3AP Implementation for PostgreSQL/Linux ?
Previous Message Tom Lane 2000-08-19 03:17:28 Re: Tuning PostgreSQL to use more RAM...