Change the Postgres data_directory folder location

Posted on Wednesday, April 11, 2012



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, 
       Visited 4/2012
[2]  change postgresql's data directory, 
       Visited 4/2012

9 comments:

  1. Hi Patrick,
    nice tutorial. But I think there is a tiny mistake in it:
    chmod a-rx /database/postgresqldata
    causes the postgres user to also lose access the directory thus preventing initdb to work properly. After granting read access to the postgres user it worked.
    Kind regards
    Tobias

    ReplyDelete
    Replies
    1. Thanks for pointing this out.

      I went and updated the instructions and found another small issue as well. This time around I tested it all out and I think I have it all working now. But as always corrections are welcome

      Delete
    2. Hi sir your document is awesome but after changing the directory i want backupdata which i copied /etc/postgresql/9.1/main/postgresql.conf
      to datadir_bk

      Delete
  2. Very nice tutorial. You can fix the ssl error by copying the missing certificates from the old data folder

    ReplyDelete
  3. thanku so much for your tutorial....

    ReplyDelete
  4. Nice article, but I missing one important information:
    After change of data_directory, can I simply delete original folder via terminal (e.g. rm -rf /var/lib/postgresql/9.1/main/), or is it more complicated?
    Thank you very much.

    ReplyDelete
  5. Thanks for the post. I followed the steps and I could see the db data_directory path changed. However, after reboot of server, it goes back to the default path. Is there any entry required anywhere to make this change permanent?

    ReplyDelete