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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
