2015-02-24 10:06 GMT-05:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 02/24/2015 05:06 AM, Guillaume Drolet wrote: > >> >> >> 2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguilla...@gmail.com >> <mailto:droletguilla...@gmail.com>>: >> >> >> >> 2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>>: >> >> >> 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. >> > > So how did all this stuff get into the non-default tablespace in the first > place? > Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files mentionned in my previous post. > > The answer would seem to be just reverse whatever you did in answer to the > question above. > That makes sense. I will give it a try. Thanks. > > >> >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
