Re: Sequences not moved to new tablespace

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> On 02/24/2015 07:10 AM, Guillaume Drolet wrote:
>> That makes sense. I will give it a try. Thanks.

> Alright, now I am thoroughly confused:) I thought this is how you to
> this point, using the above commands to move from the non-default
> tablespace back to the default tablespace:

> "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;"

> And that the issue was that sequences where not moved back.

I think part of the issue here is confusion over what "default" means.
pg_default refers to an installation's default tablespace, to wit
storage under the $PGDATA directory. This is not necessarily the
same thing as a database's default tablespace, which might have been
set to something else.

We now know why Guillaume was having a problem with sequences: he built
his movement script on the basis of the pg_tables view, which does not
include sequences. But in any case, if I'm understanding his desires
correctly, changing the database's default tablespace would have been
far easier and more reliable than manually moving tables one at a time.

For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the
case where the database already has some tables that have been explicitly
placed into that tablespace. (I forget the exact reason for this, but
it's got something to do with needing to preserve a distinction between
tables that have had a tablespace explicitly assigned and those that
are just inheriting the database's default tablespace.) So the best
bet at this point seems to be to move everything back to the database's
original tablespace and then use ALTER DATABASE SET TABLESPACE.

If you're not sure what remains to move, try looking at the
pg_class.reltablespace column. There will be a few entries with
tablespace 1664 (pg_global) which you can't and shouldn't move.
You want everything else to be shown as tablespace 0, which means
"use the database's default".

regards, tom lane