Lists: | pgsql-admin |
---|
From: | "Josh Goldberg" <josh(at)4dmatrix(dot)com> |
---|---|
To: | "postgres-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | lost tables |
Date: | 2003-03-28 02:45:27 |
Message-ID: | 04c601c2f4d4$16f90da0$6e02a8c0@4dmatrix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | "Josh Goldberg" <josh(at)4dmatrix(dot)com> |
---|---|
To: | "Josh Goldberg" <josh(at)4dmatrix(dot)com>, "postgres-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: lost tables |
Date: | 2003-03-31 20:23:18 |
Message-ID: | 069801c2f7c3$5e33c650$6e02a8c0@4dmatrix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Josh Goldberg" <josh(at)4dmatrix(dot)com> |
Cc: | "postgres-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: lost tables |
Date: | 2003-04-01 03:42:44 |
Message-ID: | 27717.1049168564@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
"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
From: | "Josh Goldberg" <josh(at)4dmatrix(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "postgres-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: lost tables |
Date: | 2003-04-01 19:36:11 |
Message-ID: | 072901c2f885$f3895e00$6e02a8c0@4dmatrix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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'")
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Josh Goldberg" <josh(at)4dmatrix(dot)com> |
Cc: | "postgres-admin" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: lost tables |
Date: | 2003-04-01 19:54:18 |
Message-ID: | 2682.1049226858@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
"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
From: | "Chris White" <cjwhite(at)cisco(dot)com> |
---|---|
To: | "'postgres-admin'" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Documentation for the \lo_export and \lo_import commands |
Date: | 2003-04-01 20:15:37 |
Message-ID: | 01e601c2f88b$756d5fc0$ff926b80@amer.cisco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Where can I find documentation or help on the \lo_export and \lo_import
commands?
Thanks
Chris White
From: | "Chris White" <cjwhite(at)cisco(dot)com> |
---|---|
To: | <cjwhite(at)cisco(dot)com>, "'postgres-admin'" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Documentation for the \lo_export and \lo_import commands |
Date: | 2003-04-02 00:02:49 |
Message-ID: | 020201c2f8ab$33af55a0$ff926b80@amer.cisco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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