Search code examples
djangopostgresqlosx-mountain-lionvirtualenv

Password issue on createdb - PostgreSQL on OSX Mountain Lion

I was getting the famous 

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

error when trying to createdb in the Terminal in OSX Mountain Lion, so following the instructions here, I added export PATH=/opt/local/lib/postgresql92/bin:$PATH to my .bash_profile and now if I type which psql I get /opt/local/lib/postgresql92/bin/psql (where previously I was getting /usr/bin/psql).

However, if I try createdb database now, I get prompted for a password I don't know (it's not my default user password), and if I enter it wrong twice, I get:

createdb: could not connect to database template1: 
FATAL:  password authentication failed for user "username"

where username is my default username in Terminal. What I'm trying to do is create a PostgreSQL database I can access from a Django project living inside a virtualenv. I'm using PostgreSQL 9.2. What am I doing wrong?

Solution

  • createdb has to connect to PostgreSQL under the hood in order to execute the SQL command that will create the new database. By default it uses your OS username for that. It happens to fail, according to the error message, either because this user does not exist as a database user (most plausible), or because he has a password that you don't remember.

    To fix that, assuming you're using the macports package which seems plausible given the installation path, you may run:

    sudo su postgres -c '/opt/local/lib/postgresql92/bin/createdb dbname'

    The new database will be owned by the postgres user, which is not always ideal. A more sensible choice would be to create a normal db user with your username, and make it the owner of the database:

    sudo su postgres -c '/opt/local/lib/postgresql92/bin/createuser username'
sudo su postgres -c '/opt/local/lib/postgresql92/bin/createdb -O username dbname'

    EDIT: The createuser command prompts these questions (from the manpage):

          $ createuser joe
       Shall the new role be a superuser? (y/n) n
       Shall the new role be allowed to create databases? (y/n) n
       Shall the new role be allowed to create more new roles? (y/n) n

    By answering yes to the last two, the new user will have sufficient rights so that it can be used subsequently instread of postgres through sudo, e.g. this will work:

    createuser -U username newuser
createdb -U username newdatabase