Re: Sequences not moved to new tablespace

[Guillaume Drolet <droletguillaume@xxxxxxxxx>]

2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguillaume@xxxxxxxxx>:

2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

On 02/23/2015 10:08 AM, Guillaume Drolet wrote:

Hello,

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:

psql -U postgres -d mydb < move_tables_to_pg_default.sql | findstr /R /C:"[ALTER]" | psql -d mydb -U postgres

I got many errors like this one:

ERROR:  permission denied: "pg_event_trigger" is a system catalog

If I can't move tables from pg_catalog, how will I be able to drop that tablespace I don't want to use anymore?

I am thinking that maybe using "ALTER DATABASE mydb SET TABLESPACE pg_default;" instead would take care of all this, no?

But when I tried it last week, I got a message like: some relations already in target tablespace...

Any help will be much appreciated.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx