Обсуждение: 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 GoldbergTo: postgres-adminSent: Thursday, March 27, 2003 6:45 PMSubject: [ADMIN] lost tablesI 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@4dmatrix.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@sss.pgh.pa.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@4dmatrix.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@postgresql.org [mailto:pgsql-admin-owner@postgresql.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@postgresql.org