0

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?

Community
  • 1
  • 1
GChorn
  • 1,267
  • 1
  • 19
  • 36

1 Answers1

2

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
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • Thanks for the answer, Daniel! Is there a way to do all this without needing to use `sudo` each time? Like if I create a new user with my username, can I just switch to that user and create databases that way? Or would there be an issue with directory permissions, etc? – GChorn Apr 03 '13 at 03:22
  • Also, where do these commands create the database? I can't seem to find it on my computer. – GChorn Apr 03 '13 at 06:25
  • 1
    @GChorn: see my edit about createuser. As for the database, there's a directory per database inside PG's datadir but it's entirely managed by postgres, the user is not allowed to do anything with those directories and files. – Daniel Vérité Apr 03 '13 at 11:58
  • Thanks Daniel. You've been extremely helpful! – GChorn Apr 04 '13 at 06:31
  • Any advice if I used brew install? – quantumpotato Jan 03 '14 at 06:37