Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes: > On 02/24/2015 07:10 AM, Guillaume Drolet wrote: >> That makes sense. I will give it a try. Thanks. > Alright, now I am thoroughly confused:) I thought this is how you to > this point, using the above commands to move from the non-default > tablespace back to the default tablespace: > "I moved all my tables and indexes from one tablespace to pg_default using > ALTER TABLE ... SET TABLESPACE pg_default; > ALTER INDEX ... SET TABLESPACE pg_default;" > And that the issue was that sequences where not moved back. I think part of the issue here is confusion over what "default" means. pg_default refers to an installation's default tablespace, to wit storage under the $PGDATA directory. This is not necessarily the same thing as a database's default tablespace, which might have been set to something else. We now know why Guillaume was having a problem with sequences: he built his movement script on the basis of the pg_tables view, which does not include sequences. But in any case, if I'm understanding his desires correctly, changing the database's default tablespace would have been far easier and more reliable than manually moving tables one at a time. For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the case where the database already has some tables that have been explicitly placed into that tablespace. (I forget the exact reason for this, but it's got something to do with needing to preserve a distinction between tables that have had a tablespace explicitly assigned and those that are just inheriting the database's default tablespace.) So the best bet at this point seems to be to move everything back to the database's original tablespace and then use ALTER DATABASE SET TABLESPACE. If you're not sure what remains to move, try looking at the pg_class.reltablespace column. There will be a few entries with tablespace 1664 (pg_global) which you can't and shouldn't move. You want everything else to be shown as tablespace 0, which means "use the database's default". regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
