Re: Sequences not moved to new tablespace

On 02/24/2015 07:32 AM, Tom Lane wrote:
> 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.

I see now, imprecise terminology on my part.

>
> 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.

Can sequences be moved?
I tried and could not get it to work.

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com