Hi,
I've run into an odd problem - I have what seems to be a "zombie" tablespace
that PostgreSQL won't let me drop, but nothing inside it is active.
% sudo -H -u postgres psql template1 -c "drop tablespace archive2;"
ERROR: tablespace "archive2" is not empty
% sudo find /media/ssd/archive2/ -type f -ls
36962439 393940 -rw------- 1 postgres postgres 403390464 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054
36962444 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.2
36962442 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056
36962446 808 -rw------- 1 postgres postgres 827392 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056_fsm
36962441 8 -rw------- 1 postgres postgres 8192 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_vm
36962445 282384 -rw------- 1 postgres postgres 289161216 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.3
36962440 120 -rw------- 1 postgres postgres 122880 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877054_fsm
36962443 1048580 -rw------- 1 postgres postgres 1073741824 Jun 27 2014 /media/ssd/archive2/postgresql/PG_9.1_201105231/117264/7877056.1
% sudo -H -u postgres psql template1 -c "SELECT datname FROM pg_database WHERE oid = 117264;"
datname
----------
mydb
(1 row)
% sudo -H -u postgres psql mydb -c "SELECT oid, relname, relkind FROM pg_catalog.pg_class where oid IN (7877054, 7877056);"
oid | relname | relkind
-----+---------+---------
(0 rows)
% sudo -H -u postgres psql mydb -c "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 in (7877054, 7877056);"
catalog | oid_dependent
---------+---------------
(0 rows)
% sudo -H -u postgres psql mydb -c "SELECT oid, datname FROM pg_database WHERE dattablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'archive2');"
oid | datname
-----+---------
(0 rows)
% sudo -H -u postgres psql giscloud -c "SELECT pg_tablespace_databases((SELECT oid FROM pg_tablespace WHERE spcname = 'archive2'));"
pg_tablespace_databases
-------------------------
117264
(1 row)
So there are no actual references to these files, yet the relationship
persists sufficiently for the DROP TABLESPACE to error out.
I've reviewed what seems to be a similar example from 2008 on this list:
http://grokbase.com/t/postgresql/pgsql-general/086g1yrpbq/error-when-trying-to-drop-a-tablespace
but there was no ultimate solution then. I'm hoping there would be now :)
Is it safe if I get rid of it by setting up a slave server to this machine,
but then shut that postmaster down and manually remove the contents of this
directory, bring it back up, let it get back in sync again, and then promote
the slave to become the master, with all the accompanying arrangements on
the side?
TIA.
--
2. That which causes joy or happiness.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
