Re: Sequences not moved to new tablespace
| От | Guillaume Drolet |
|---|---|
| Тема | Re: Sequences not moved to new tablespace |
| Дата | |
| Msg-id | CAOkiyv5VneCtLQnFjbbt_Oj_tAcN0WM_+Zy77DCS2rKGmGkz-g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Sequences not moved to new tablespace (Guillaume Drolet <droletguillaume@gmail.com>) |
| Ответы |
Re: Sequences not moved to new tablespace
Re: Sequences not moved to new tablespace |
| Список | pgsql-general |
2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguillaume@gmail.com>:
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');
2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com>:On 02/23/2015 10:08 AM, Guillaume Drolet wrote:Well round file my previous suggestion. Just tried it and it did not work.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?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
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
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.
В списке pgsql-general по дате отправления: