Change the Postgres data_directory folder location

I recently had to install postgres 9.1 on a server and move a large database over to it.   The machine this is running on is an Ubuntu 64 bit server running on a ESXi VMware server.  As such, I decided to add an extra hard drive to the Ubuntu server and put the database files there.    Here are the steps I used to accomplish that.

First determine where the current data_directory is (the database folder)

>  sudo su postgres         >    psql -d postgres –U postgres         >    select version();         >    SHOW data_directory;

Log in as the postgres user and log into the current database.  Use the show data_directory(); to see where the current data_directory is.

After a fresh install of postgres 9.1  the data_directory was at

/var/lib/postgresql/9.1/main/postgresql.conf

Log out of the postgres user

In my case my second hard drive was mounted at /database

I decided to create a subdirectory “postgresqldata” to place the DB in.

> sudo su root > mkdir -p /database/postgresqldata > chown -R postgres:postgres /database/postgresqldata > su postgres > /usr/lib/postgresql/9.1/bin/initdb -D /database/postgresqldata

Here I  created the /database/postgresqldata folder and then set its owner as postgres.   I then ran the initdb  program as the postgres user.

Now edit postgressql.conf

Open it up for editing

>    sudo vi +41 /etc/postgresql/9.1/main/postgresql.conf

Change

data_directory = '/var/lib/postgresql/9.1/main/'

To

data_directory = '/database/postgresqldata/'

Start postgres

>    sudo /etc/init.d/postgresql restart

For some reason I got the following error

* The PostgreSQL server failed to start. Please check the log output:

2013-07-26 10:13:43 PDT FATAL:  could not load server certificate file "server.crt": No such file or directory

   ...fail!

I am not using SSL with my database so I can just turn it off

After it restarts log into the db and check the data_directory location

> sudo vi +80 /etc/postgresql/9.1/main/postgresql.conf

Set

ssl= true

to

ssl = false

Now Start postgres

> sudo /etc/init.d/postgresql start

Confirm the changed directory

>    sudo su postgres         >    psql -d postgres –U postgres         >    SHOW data_directory;

Here are my results.  You can see the data_directory has changed to /database/postgresqldata

References

[1]  17.2. File Locations, 

       http://www.postgresql.org/docs/8.1/static/runtime-config-file-locations.html

       Visited 4/2012

[2]  change postgresql's data directory, 

       http://www.virtualmin.com/node/9461

       Visited 4/2012