Moving Postgresql Data Folder

We had a old server which ran out space for partition where postgres was running… we had to move the data folder to a bigger partition… and here is how I did it…

Step 0:  Stop the Postgres if running

$service postgresql stop

Step 1 :  Copy  the whole  data/  folder  to  new parition

say  we have  /usr/local/pgsql/data   and wants to move to /home2/ partition

$cp -R /usr/local/pgsql/data  /home2/postgres/data

Step 2 :  Modify the Startup script to point to correct data directory

In  /etc/init.d/postgresql  file,  change the value of  PGDATA  variable to new location  which is /home2/postgres/data

Step 3 : Start the db server

$ service postgresql start

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.

Where does PostgreSQL store the database?

To see where the data directory is, use this query.

show data_directory;

To see all the run-time parameters, use

show all;

You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.

SELECT * FROM pg_tablespace;

Postgresql: Move database to new tablespace

ALTER DATABASE name SET TABLESPACE new_tablespace

Excerpt from the docs:

... changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.