Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I have tried that to no avail: $ ll /home/fccoelho/Documentos/databases/postgresqldata total 0 drwxr-xr-x 1 postgres postgres 0 Abr 15 08:52 ./ drwxrwxrwx 1 fccoelho fccoelho 62 Abr 15 08:52 ../ –  fccoelho Apr 15 '13 at 12:21
    
Are you logged in with the postgres user? –  dezso Apr 15 '13 at 12:47
    
I think this happens because of rights in parent directories. Please have a look at my complete answer. –  eppesuig Apr 15 '13 at 13:12
    
@eppesuig Might be completely true, I tried to set up the data dir directly in the users home, which has that access by default. –  dezso Apr 15 '13 at 13:19
add comment

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!!

share|improve this answer
    
I tested this on my machine and I could run initdb on a directory in a user's home without problem. –  dezso Apr 15 '13 at 12:54
    
I think this happend because of rights in parent directories. Please have a look at my complete answer. –  eppesuig Apr 15 '13 at 13:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.