Re: lost tables

I moved a few frequently used tables to a separate drive/partition with a symlink to my real data dir. Thank you Murphy, my other drive died yesterday and I hadn't been making backups of those tables. Now I have my db back up and running but those tables are hosed. I can't drop them using DROP TABLE, and I can't do a pg_dump anymore either. everything complains about pg_toast_xxxxxxxxxx where xxxxxxxx is the filename of the table. How do I clean this up so I can rebuild the tables?

thanks in advance,

Josh Goldberg

bueller?
----- Original Message -----
From: Josh Goldberg
To: postgres-admin
Sent: Thursday, March 27, 2003 6:45 PM
Subject: [ADMIN] lost tables

I moved a few frequently used tables to a separate drive/partition with a symlink to my real data dir. Thank you Murphy, my other drive died yesterday and I hadn't been making backups of those tables. Now I have my db back up and running but those tables are hosed. I can't drop them using DROP TABLE, and I can't do a pg_dump anymore either. everything complains about pg_toast_xxxxxxxxxx where xxxxxxxx is the filename of the table. How do I clean this up so I can rebuild the tables?

thanks in advance,

Josh Goldberg

"Josh Goldberg" <josh(at)4dmatrix(dot)com> writes:
> I moved a few frequently used tables to a separate drive/partition with a=
> symlink to my real data dir. Thank you Murphy, my other drive died yester=
> day and I hadn't been making backups of those tables. Now I have my db bac=
> k up and running but those tables are hosed. I can't drop them using DROP =
> TABLE, and I can't do a pg_dump anymore either. everything complains about=
> pg_toast_xxxxxxxxxx where xxxxxxxx is the filename of the table. How do I=
> clean this up so I can rebuild the tables?

Can't tell without more details. What's the *exact* error message you
get from a DROP TABLE? Also it'd be useful to see the pg_class rows
for the problem tables ("select oid,* from pg_class where relname = 'xxx'")

regards, tom lane

thanks for the reply. There are a few tables I did this to, here's one of
them:
cms3=# select oid,* from pg_class where relname='view_log';
oid | relname | reltype | relowner | relam | relfilenode | relpages |
reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared |
relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs
| relhasoids | relhaspkey | relhasrules | relhassubclass | relacl
-------+----------+---------+----------+-------+-------------+----------+---
--------+---------------+---------------+-------------+-------------+-------
--+----------+-----------+-------------+----------+----------+---------+----
--------+------------+-------------+----------------+----------------------
19433 | view_log | 19434 | 1 | 0 | 19433 | 10 |
1000 | 836121 | 0 | t | f | r |
5 | 0 | 0 | 0 | 0 | 0 | t | t
| f | t | {=,postgres=arwdRxt}
(1 row)

cms3=# drop table view_log;
NOTICE: RelationBuildDesc: can't open view_log: No such file or directory
ERROR: cannot open view_log: No such file or directory
cms3=#

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>
> Can't tell without more details. What's the *exact* error message you
> get from a DROP TABLE? Also it'd be useful to see the pg_class rows
> for the problem tables ("select oid,* from pg_class where relname =
'xxx'")

"Josh Goldberg" <josh(at)4dmatrix(dot)com> writes:
> cms3=# drop table view_log;
> NOTICE: RelationBuildDesc: can't open view_log: No such file or directory
> ERROR: cannot open view_log: No such file or directory

You should be able to get to a state where you can drop the table by
doing

touch $PGDATA/base/dbnumber/filenumber

where filenumber is the pg_class.relfilenode entry for the table (19433
here), and dbnumber is the pg_database.oid entry for the database.

If there is still a symlink in that position, either remove it and do
the touch to make a plain file, or do a touch where the symlink points
instead.

You may need to repeat for each index used by the table, if you moved
those off too.

BTW, recent versions (7.3 at least, and I think 7.2) will allow a DROP
TABLE without insisting on finding an underlying file, for example:

regression=# create table z(f1 int);
CREATE TABLE
regression=# select relfilenode from pg_class where relname = 'z';
relfilenode
-------------
2204132
(1 row)

-- as postgres, rm the file 2204132

regression=# drop table z;
WARNING: cannot unlink 2066720/2204132: No such file or directory
DROP TABLE

regards, tom lane

Where can I find documentation or help on the \lo_export and \lo_import
commands?

Thanks
Chris White

Found the info in SQL documentation

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Chris White
Sent: Tuesday, April 01, 2003 12:16 PM
To: 'postgres-admin'
Subject: [ADMIN] Documentation for the \lo_export and \lo_import
commands

Where can I find documentation or help on the \lo_export and \lo_import
commands?

Thanks
Chris White

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org