I moved all my tables and indexes from one tablespace to pg_default using
ALTER TABLE ... SET TABLESPACE pg_default; ALTER INDEX ... SET TABLESPACE pg_default;
Some 2500 files were moved to pg_default but 461 files remain in the tablespace and so I cannot drop it.
When I query, for example:
SELECT oid, relname, relkind FROM pg_catalog.pg_class WHERE oid IN (943602, 2650968, 2650971);
I see that most of these files are sequences. Why didn't they get moved and how can I move them to pg_default (and all other remaining files) so that I can drop the tablespace?
Well round file my previous suggestion. Just tried it and it did not work.
Thanks Adrian.
So, anybody else have some piece of advice on this?
Thanks!
Digging a little more, I found that not only sequences were not moved but also many tables in pg_catalog are still in my old tablespace. This is expected since the query in the SQL files I used to move the tables and indexes had a WHERE clause like this:
SELECT ' ALTER TABLE ' || schemaname || '.' || tablename || ' SET TABLESPACE pg_default;' FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
So I tried removing the WHERE clause and running the script again: