Re: Sequences not moved to new tablespace

From: Guillaume Drolet <droletguillaume(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Sequences not moved to new tablespace
Date: 2015-02-24 15:59:55
Message-ID: CAOkiyv4-5_kQfkHQX+=C858AwqHXLqy2o3-9YK+aARjC1vxQEw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

2015-02-24 10:32 GMT-05:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

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

Thanks a lot Tom for this very useful clarification. I am indeed trying to
move everything from a created tablespace to the installation's default
directory (pg_default) under $PGDATA. When I first created the database I
want to move, I had set the default tablespace to my created tablespace,
diamonds.

Now with your explanations I understand why sequences and other tables
where not moved: because of the pg_tables view. Based on these details,
here's what I understand I should do now:

1) Run the SQL scripts for moving tables and indexes back to "diamonds"
3) Set database default tablespace to "diamonds": ALTER DATABASE SET
default_tablespace = diamonds;
2) Check that nothing else but entries with reltablespace equal to 1664
remain in pg_class. Everything else should equal 0
4) Move the database to pg_default with: ALTER DATABASE mydb SET TABLESPACE
pg_default;
5) DROP TABLESPACE diamonds;

Is this correct?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Arup Rakshit 2015-02-24 16:02:59 What is the alternate of FILTER below Postgresql 9.4 ?
Previous Message Guillaume Drolet 2015-02-24 15:47:05 Re: Sequences not moved to new tablespace