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
