MOVE A POSTGRESQL DATABASE TO A DIFFERENT TABLESPACE

Moving a complete database to a different tablespace involves 3 steps :

1. You have to tell PostgreSQL to create new tables and indexes for the database in the new tablespace. This can be done with :

   1	ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];

2. Then you have to move each table in that database to the new tablespace with :

   1	ALTER TABLE "[table_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each table in the database.
3. And that’s not all, because the indexes also have to be moved. That’s done with :

   1	ALTER INDEX "[index_name]" SET TABLESPACE "[new_tablespace]";

   This line of SQL code is needed for each index in the database.