1

I have an instance of a Postgres server running that I've started with the command:

pg_ctl -D /usr/local/var/postgres/data -l /usr/local/var/postgres/data/server.log start

Running the command createdb test prompts me for my password twice, then I get this error:

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

Also, when I try to stop the server using 

pg_ctl -D /usr/local/var/postgres/data stop -m smart

I get this error message: 

pg_ctl: PID file "/usr/local/var/postgres/data/postmaster.pid" does not exist
Is server running?

Is there something I'm missing or forgot to initialize/install? I used these instructions to install.

I checked this answer and this answer and neither of the two fixed my problem.

Community
  • 1
  • 1
joeyyang
  • 1,164
  • 8
  • 10
  • Is there anything in the log file you specified (/usr/local/var/postgres/data/server.log)? Also can you see the postgresql processes running (ie. if you run ps -ef do you see 'postmaster')? – harmic Jan 14 '14 at 07:24
  • Hey @harmic -- https://gist.github.com/joeyyang/bd63d8a6908020bf23fb and https://gist.github.com/joeyyang/8bf619ae6034d04eee0d are the results of server.log and ps -ef, respectively. – joeyyang Jan 14 '14 at 07:34

1 Answers1

0

From the log file you added in your comment, it looks as if either there is another postgresql instance running on the machine (or possibly something else which is using the same port as postgresql wants to use):

LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  could not bind IPv6 socket: Address already in use
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING:  could not create listen socket for "localhost"
FATAL:  could not create any TCP/IP sockets

This is stopping the database from starting up to begin with.

To see what process that could be, you can use lsof:

$ sudo lsof | grep -i listen
...
postmaste 19732  postgres    3u     IPv6            1194355       0t0        TCP localhost:postgres (LISTEN)
postmaste 19732  postgres    4u     IPv4            1194356       0t0        TCP localhost:postgres (LISTEN)
...

In the above sample, from a Linux host, you can see that a process called postmaster (it is truncated in the printout) listens on localhost:postgres, meaning the localhost address port 5432 (lsof is translating the port 5432 into 'postgres' via the file /etc/services which contains a mapping between well known port numbers and corresponding services).

The fact that createdb is prompting for your password implies that it is connecting to a database somewhere, although I could not spot it the ps printout you sent.

The other part of your question was why createdb could not connect to your database (or whatever database is running on your machine). If it is a freshly created database cluster then it will not have any users defined other than the default 'postgres' user. You must issue commands with this user:

createdb -U postgres test

Without the -U option it will try to connect using your current login user, which won't exist in the database.

It might also be that you will need to authenticate as the postgres user. The file pg_hba.conf in the postgresql data directory controls what kind of authentication will be needed.

In general the postgresql documentation is excellent; I suggest you read through the section Server Setup and Operation to check that you have a valid configuration.

harmic
  • 28,606
  • 5
  • 67
  • 91
  • I was able to run the `createdb` command using the `-U` flag. I wasn't able to find a blocking operation on port 5432, although I did find a postgres server on port 2657 being run by a postgres process. Two additional notes: 1. I followed the Postgres documentation you linked to, and started the server with `su postgres -c`and got this error: `pg_ctl: could not open PID file "/usr/local/var/postgres/data/postmaster.pid": Permission denied`. 2. Here is a link to a gist with the contents of [pg_hba.conf](https://gist.github.com/joeyyang/50423baa831f26b23838). Do I need to make any changes? – joeyyang Jan 14 '14 at 19:59
  • 1. It means that the postgres 'nix user does not own the data directory (or some of the files in it). Fix with 'chown -R postgres:postgres /usr/local/var/postgres/data' (as root). 2. The pg_hba.conf is ok for testing, as long as your machine is not exposed. You should tighten it up before doing anything serious with the DB as it allows anyone access to the database with no password. – harmic Jan 14 '14 at 22:58
  • Tried running `chown -R postgres:postgres /usr/local/var/postgres/data` and got `chown: postgres: illegal group name`, then ran `'chown -R postgres :postgres /usr/local/var/postgres/data1` (with a space between `postgres` and `:postgres`) and got a lot of "Operation not permitted"s. – joeyyang Jan 15 '14 at 04:19
  • ok, try it without the group name: chown -R postgres /usr/local/var/postgres/data – harmic Jan 15 '14 at 04:39
  • Still got "Operation not permitted." Any ideas? Thank you for all your help thus far, by the way. – joeyyang Jan 15 '14 at 19:40
  • I'm doing this as root. – joeyyang Jan 15 '14 at 22:47
  • Ok then see this: http://superuser.com/questions/279235/why-does-chown-reports-operation-not-permitted-on-osx Seems OSX has some peculiarities ... – harmic Jan 16 '14 at 02:06