Re: Sequences not moved to new tablespace

On 02/24/2015 05:06 AM, Guillaume Drolet wrote:

2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguillaume@gmail.com
<mailto:droletguillaume@gmail.com>>:



    2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
    <mailto:adrian.klaver@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?

The answer would seem to be just reverse whatever you did in answer to the question above.

        --
        Adrian Klaver
        adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com