How to change the data directory on Postgresql 9.1 on Ubuntu

Question

I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04:

I first did a:

sudo pg_dropcluster --stop 9.1 main

and then attempted to create a new one in the desired path:

 sudo pg_createcluster -d /home/fccoelho/Documentos/databases/postgresqldata 9.1 main

but i fails with a permission denied:

Creating new cluster (configuration: /etc/postgresql/9.1/main, data: /home/fccoelho/Documentos/databases/postgresqldata/)...
initdb: could not access directory "/home/fccoelho/Documentos/databases/postgresqldata": Permission denied
Error: initdb failed

How do I make this work?

Answer 1

If you create a cluster in Debian/Ubuntu without specifying the owner (option --user of pg_createcluster) then you have to be sure that the directory you specify is accessibile in r/w to the postgres user. Please note that all parent directories should also be accessibile to the same user with bit x (list directory content).

Otherwise, you may specify a different owner using option --user. This should create a cluster ran by that user instead of postgres.

In order to check if postgres user may access that directory, execute this command:

sudo su - postgres -c 'touch /home/fccoelho/Documentos/databases/postgresqldata/test'

and check for any error.

Answer 2

When I wanted to switch my databases to another drive (with more space), and ask the question here: Create database on new partition , the answer was:

create tablespace dedicated_datastore owner postgres location '/my/mount/point/'

With it you can create database at the place you want. Just indicate the tablespace to use at creation. And you can of course have several tablespaces pointing at different mount points (and harddrive) if ever needed.

Answer 3

The data directory should be owned by the postgres user:

[postgres] user account should only own the data that is managed by the server

So you have to issue a command like

chown -R postgres:postgres /home/fccoelho/Documentos/databases/postgresqldata

first, then try to create the cluster.

Answer 4

I have found a workaround, but not really the solution:

I found out I cannot maintain the data directory within my home tree so I created a new directory /home/postgresqldata/ and ran:

sudo pg_createcluster -d /home/postgresqldata/ 9.1 main

Everything worked as expected!!