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 t.relname, t.reltablespace, sp.spcname
>> FROM pg_class t LEFT JOIN
>> pg_tablespace sp ON sp.oid = t.reltablespace;
> relname | reltablespace | spcname
> ----------------------------------------------------------+---------------+------------
[...]
> mod09a1_sur_refl_b05_amonth_idx | 1663 | pg_default
> mod44b_cloud_rid_seq | 0 |
> pg_toast_2619 | 0 |
> pg_type | 0 |
> pg_authid_rolname_index | 1664 | pg_global
> pg_authid_oid_index | 1664 | pg_global
[...]
Like Tom suggested, you should move the tables from "pg_default" back to the database's
default tablespace and then use ALTER DATABASE to move the database tablespace.
Yours,
Laurenz Albe