move data directory postgresql

Directions on how to move data directory for PostgreSQL on Redhat 6.

Get the data directory from pgsql command line. This can be done by running the following command either in pgsql or pgAdminIII.

SHOW data_directory;

To access the pgsql command line you must fire the following command. Depending on where  Postgres is installed you must use the correct path.

[command]# ./usr/pgsql-9.1/bin/psql -U postgres dp_toolkit_core

dp_toolkit_core=# SHOW data_directory;

data_directory

-------------------------------------------------

/var/lib/pgsql/9.1/data

(1 row)

dp_toolkit_core=# \q

Now after we have the directory lets try the following, lets stop the postgres service to see the name of the service we can run the following command to see all the services running.

[command]# service --status-all

This will give us a list of all the services available if it is not available or you can find it.. since we know this version of postgres is 9.1 we can try the following command to see if gives us any information.

[command]# service postgresql-9.1 status

(pid 1234) is running…

So now lets stop the service with the following command

[command]# service postgresql-9.1 stop

Once the service is stopped lets copy the data to a different directory whichever is your choice.  For reference go to the following post http://stackoverflow.com/questions/16678872/how-can-i-move-postgresql-data-to-another-directory-on-ubuntu-over-amazon-ec2

Now in my case the reason for moving the data directory is because the original install location is running out of space. So we will move to a different partition that has more space. It should be simple based on the stackoverflow.com answer.

Im going to move my directory to a folder called /home by issuing the following command. Please note

-a = preserve current permissions

-R = recursive

-v = verbose  - show the progress of the function

also make sure directories are made before you try to move the data

[command]# cp -aRv /var/lib/pgsql/9.1 /home/pgsql/9.1/data

Lets rename the folder from data to data old so it doesn’t cause any problems and we can also have it there in case we need a back up

[command]# mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data_old

now lets create a symlink to our new directory in the old directory, the flag -s means this is a soft link, make sure you are in the original pgsql directory first and then issue the symlink command

[command]# cd /var/lib/pgsql/9.1/

[command]# ln -s /home/pgsql/9.1/data datadir

Now that we are done lets start the db server again  and see if we get any errors

[command]# service postgresql-9.1 start

If you have trouble starting the service go back and retrace the steps and make sure everything was done in order.

Now double check and make sure inserts, selects and other db functions are working normally.