Re: Sequences not moved to new tablespace

2015-02-24 10:06 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 02/24/2015 05:06 AM, Guillaume Drolet wrote:
>
>>
>>
>> 2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguillaume(at)gmail(dot)com
>> <mailto:droletguillaume(at)gmail(dot)com>>:
>>
>>
>>
>> 2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>