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