2015-02-24 10:32 GMT-05:00 Tom Lane <t...@sss.pgh.pa.us>: > Adrian Klaver <adrian.kla...@aklaver.com> 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 >
Thanks a lot Tom for this very useful clarification. I am indeed trying to move everything from a created tablespace to the installation's default directory (pg_default) under $PGDATA. When I first created the database I want to move, I had set the default tablespace to my created tablespace, diamonds. Now with your explanations I understand why sequences and other tables where not moved: because of the pg_tables view. Based on these details, here's what I understand I should do now: 1) Run the SQL scripts for moving tables and indexes back to "diamonds" 3) Set database default tablespace to "diamonds": ALTER DATABASE SET default_tablespace = diamonds; 2) Check that nothing else but entries with reltablespace equal to 1664 remain in pg_class. Everything else should equal 0 4) Move the database to pg_default with: ALTER DATABASE mydb SET TABLESPACE pg_default; 5) DROP TABLESPACE diamonds; Is this correct?
