Postgres:Changing Data Directory

Use this process to recover PostgreSQL data from the filesystem (i.e. when you can't just use a pg_dump from a functional instance).

1. Shut down PostgreSQL (switch to postgres user)

/usr/<postgres folder>/bin/pg_ctl stop -D /var/lib/pgsql/<version>/main/data

Example: /usr/pgsql-9.1/bin/pg_ctl stop -D /var/lib/pgsql/9.1/main/data

2. Make sure that PostgreSQL has been shut down successfully

/usr/<postgres folder>/bin/pg_ctl status -D /var/lib/pgsql/<version>/main/data

3. Find the PostgreSQL config file and edit it (usually in /etc/postgresql, but it might be in /var)

nano /etc/postgresql/<version>/main/postgresql.conf

change the data_directory, pg_hba, and pg_ident lines to the new paths

4. Change ownership of the new directory and subdirectories to make sure postgres user has access to those

sudo chown -R <ownername>:<groupname> <foldername>

5. Restart PostgreSQL (switch to postgres user)

/usr/<postgres folder>/bin/pg_ctl start -D new/path/to/data

/usr/<postgres folder>/bin/pg_ctl status -D new/path/to/data