MOVE POSTGRESQL DATA DIRECTORY ON CENTOS LINUX

Sometimes the hard drive with the Operating system on it runs out of space and you want to move the PGSQL directory elsewhere, this is how you do it.

Firstly and most importantly, Disable SELINUX (either by editing /etc/selinux/config and changing to disable or type in setup > Firewall authentication)

Secondly, if the firewall is enabled, ensure that TCP port 5432 is allowed if it wasn’t already…

#This Assumes that we are moving the whole /var/lib/pgsql directory to /data/pgsql, /data being a different physical drive, but it could be anywhere.

service postgresql stop

#Edit /etc/init.d/postgresql and change the following three lines from this:

PGDATA=/var/lib/pgsql/data
if [ -f “$PGDATA/PG_VERSION” ] && [ -d “$PGDATA/base/template1” ]
then
echo “Using old-style directory structure”
else
PGDATA=/var/lib/pgsql/data

fi
PGLOG=/var/lib/pgsql/pgstartup.log

#To this:

PGDATA=/data/pgsql/data
if [ -f “$PGDATA/PG_VERSION” ] && [ -d “$PGDATA/base/template1” ]
then
echo “Using old-style directory structure”
else
PGDATA=/data/pgsql/data

fi
PGLOG=/data/pgsql/pgstartup.log

mv /var/lib/pgsql /data

#Check permissions on the folder to make sure – /data/pgsql and directories below should have permissions of 700 and postgres and user and group owner.

#If you are installing fresh and have not initialised the Postgres with a service postgresql initdb you will need to change ownership of the directory you intend to install it into – ie, cd /data | chown postgres.postgres * -Rf

service postgresql start

#If you have any issues with it failing to start here, please check pgstartup.log in the root directory of pgsql (in this case /data/pgsql) for details and troubleshooting.