Re: Sequences not moved to new tablespace

On Tue, Feb 24, 2015 at 10:32:42AM -0500, Tom Lane wrote:
> 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.

FYI, I added docs for this to the 9.5 ALTER DATABASE manual page:

The fourth form changes the default tablespace of the database.
Only the database owner or a superuser can do this; you must also have
create privilege for the new tablespace. This command physically moves
any tables or indexes in the database's old default tablespace to the
new tablespace. The new default tablespace must be empty for this
database, and no one can be connected to the database. Tables and
indexes in non-default tablespaces are unaffected.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +