From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | cant drop tablespace although it is empty |
Date: | 2020-03-24 12:38:33 |
Message-ID: | CA+t6e1nmtH88yuhRRRCVCAJw4KatJexDK=4vZiJobK6i5UokVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hey,
One of my dbs was on a dedicated tablespace and I decided to move it back
to the pg_default tablespace. I moved the db to the pg_default tbs and it
was successful.
Now when I'm trying to drop the tbs I'm getting the following error :
mydb=# drop tablespace my_custom_tbs
ERROR: tablespace "my_custom_tbs" is not empty
mydb=#
I checked what objects are inside :
select oid,* from pg_Tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+----------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16400 | my_custom_tbs | 10 | |
(3 rows)
select count(*) from pg_class where reltablespace=16400;
count
-------
0
(1 row)
I dont have any db with this tbs as its default tbs :
select distinct dattablespace from pg_database;
dattablespace
---------------
1663
(1 row)
I went to the location on disk of the tbs and saw that it contains files :
pwd
/var/lib/pgsql/custom_tbs/PG_9.6_201608131/16406
ls -l | wc -l
318
Now I tried to find the objects that belong to those files, some of them
are pg objects that belong to tbs 0 which is very weird and some arent
belong to any object :
select relname,relkind,reltablespace from pg_class where relfilenode=13129
;
relname | relkind | reltablespace
----------------------+---------+---------------
pg_toast_13125_index | i | 0
(1 row)
select relname,relkind,reltablespace from pg_class where relfilenode=16533;
relname | relkind | reltablespace
---------+---------+---------------
(0 rows)
Now all those who are exist located in reltablespace 0 .
*I was working on pg9.6 .
bug ?
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas Reed | 2020-03-24 16:24:07 | Problem with replication |
Previous Message | Michael Paquier | 2020-03-24 06:12:38 | Re: PG12 autovac issues |