Re: Sequences not moved to new tablespace

Guillaume Drolet wrote:
>> If you want to move a whole database to a different tablespace (the only reason
>> I can think of for doing what you are trying to so), use the command
>> ALTER DATABASE ... SET TABLESPACE ...

> Thanks Laurenz. I tried your suggestion:
> 
> psql -U postgres -c "ALTER DATABASE mydb SET TABLESPACE pg_default;"
> 
> I get this message:
> ERROR:  some relations of database "mortalite" are already in tablespace "pg_default"
> HINT : You must move them back to the database's default tablespace before using this command.
> 
> But if I do "SHOW default_tablespace;" in mydb, it showed "pg_default" as the default tablespace.
> 
> So I tried changing it back to the tablespace I want to get rid of to subsequently moved everything
> back there so that ultimately, it lets me move everything to pg_default:
> ALTER DATABASE mydb SET default_tablespace = diamonds;
> 
> And then:
> psql -U postgres -c "ALTER DATABASE mydb SET TABLESPACE diamonds;"
> 
> ALTER DATABASE is issued but nothing gets physically moved to diamonds. Why?

I guess the problem is that you already moved a lot of tables around.

Could you connect to the database and try the following:

SELECT d.datname, d.oid, sp.spcname, sp.oid
FROM pg_tablespace sp JOIN
     pg_database d ON sp.oid = d.dattablespace
WHERE datname = current_database();

and

SELECT t.relname, t.reltablespace, sp.spcname
FROM pg_class t LEFT JOIN
     pg_tablespace sp ON sp.oid = t.reltablespace;

Yours,
Laurenz Albe