Albe Laurenz wrote:
Cyril SCETBON wrote:
I get the following error :
postgres=# DROP TABLESPACE IF EXISTS my_tbs;
ERROR: tablespace "my_tbs" is not empty
I've searched in pg_class and I'm not able to find a relation which
refers to my_tbs with :
Find out the directory:
SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';
is there anything in this directory?
cd spclocation
find .
.
./100456
./100456/100738
./100456/102333
./100456/103442
[...]
A tablespace does not belong to a specific database, so it could be that
these objects belong to another database that also uses this tablespace.
Try to look for objects that depend on the tablespace in other databases.
I also forgot pg_shdepend which contains relationships to shared objects
such as roles - look there too.
VALUES=`find /path/100456/ -type f -exec basename {} \;|xargs|sed -e 's/ /,/g'`
for db in `/usr/lib/postgresql/8.2/bin/oid2name|grep -v template|tail -n
+4|awk '{print $2}'`; do psql $db -c "select count(*) from pg_shdepend
where objid in ($VALUES) or refobjid in ($VALUES)"; done
nothing :-(
Did you also look in pg_depend in all the other databases?
You could run the following in all databases:
SELECT t.relname AS catalog, d.objid AS oid_dependent
FROM pg_catalog.pg_class t JOIN
pg_catalog.pg_depend d ON (t.oid = d.classid)
WHERE refobjid = 100456;
catalog | oid_dependent
---------+---------------
(0 rows)
catalog | oid_dependent
---------+---------------
(0 rows)
catalog | oid_dependent
---------+---------------
(0 rows)
You could also try the following in all databases:
SELECT oid, relname, relkind FROM pg_catalog.pg_class
WHERE oid IN (100738, 102333, 103442);
oid | relname | relkind
-----+---------+---------
(0 rows)
oid | relname | relkind
-----+---------+---------
(0 rows)
oid | relname | relkind
-----+---------+---------
(0 rows)
I'm at the end of my wits.
If there is nothing in pg_depends and pg_shdepends referring to that
tablespace, I don't know what the files in the tablespace directory could be.
Maybe somebody else has an idea.
Could it be that they are garbage left behind by - e.g. - a database restore?
Were any of them used recently (file access times)?
Ah, there is another, rather tedious thing you could try:
- Take a pg_dumpall of the cluster
- Install PostgreSQL on a second machine and create the tablespace directories
(same path as on the original machine).
- Restore the dump there and see if any objects get created in the directories.
I did pg_dumpall but not yet restored it
Oh, one more question: What is the absolute path of the tablespace directory?
It is not a subdirectory of the cluster directory, is it?
Yours,
Laurenz Albe
--
Cyril SCETBON
