Thread: rebuilding a table from a datafile
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. Brian -- Brian Baquiran <brianb@edsamail.com> http://www.baquiran.com/ AIM: bbaquiran Work: +63(2)7182222 Home: +63(2) 9227123 I'm smarter than average. Therefore, average, to me, seems kind of stupid. People weren't purposely being stupid. It just came naturally. -- Bruce "Tog" Toganazzini
On Wed, Aug 16, 2000 at 02:36:10PM +0000, brianb-pggeneral@edsamail.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@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Ross J. Reedstrom writes: > On Wed, Aug 16, 2000 at 02:36:10PM +0000, brianb-pggeneral@edsamail.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. I am getting a "ERROR: cannot find attribute 1 of relation foo" when I try to SELECT * from the table. Also, \d foo produces "Couldn't find table foo!", but it is listed in pg_class. Is it safe/advisable to modify pg_attribute directly in this case? I have several other tables with the exact same structure that I can use as reference. Brian -- Brian Baquiran <brianb@edsamail.com> http://www.baquiran.com/ AIM: bbaquiran Work: +63(2)7182222 Home: +63(2) 9227123 I'm smarter than average. Therefore, average, to me, seems kind of stupid. People weren't purposely being stupid. It just came naturally. -- Bruce "Tog" Toganazzini
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
Tom Lane writes: > 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. This is the tablespaces feature, right? Is there any news on when Postgres will support this? > 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. Yep, I got away with it alright. I can see my data now. > After that, update to 7.0.2 ;-) Will do! Thanks Tom! Brian -- Brian Baquiran <brianb@edsamail.com> http://www.baquiran.com/ AIM: bbaquiran Work: +63(2)7182222 Home: +63(2) 9227123 I'm smarter than average. Therefore, average, to me, seems kind of stupid. People weren't purposely being stupid. It just came naturally. -- Bruce "Tog" Toganazzini