What should my Postgres port file look like?

Question

Firstly apologies if 'port file isn't the correct terminology', but Postgres is not one of my strong points. I'm trying to find out what this port file should look like as it seems to have gone missing from my system. Please read on for an explanation and my (quite possibly incorrect) trail of thought.

I'm using Postgres with a rails project (through the postgres gem) and it has been working fine until yesterday when it stopped working while I was updating some HTML. I restarted my rails server with the usual

rails s

Only to get:

/Users/pedr/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.2.1/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `initialize': could not connect to server: No such file or directory (PG::Error) \n \n
Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I'm using Lunchy to start and stop postgres and there seems to be no issue there.

However I've used the following Terminal command to check what port Postgres is listening on:

sudo lsof -p 286| awk '$5 == "unix" && $NF ~ /\// { print $NF }'

and it doesn't list anything. So I'm thinking the port has been deleted somehow.

My limited understanding of ports leads me to look in the following location for a port file:

/tmp/.s.PGSQL.5432

There is a file called:

.s.PGSQL.5432.lock

As far as I understand it, this file prevents the editing of the port file which is missing.

I tried using Time machine to find this missing file but it seems that Time Machine doesn't store anything in the /tmp directory.

So how can I recreate this file? What should it look like?

[UPDATE]

In response to @wildplasser's suggestions:

Test whether postgres is running using:

ps aux | grep postgres

gets me:

633 0.0 0.0 2442564 392 ?? Ss 9:23am 0:00.09 postgres: stats collector process 632 0.0 0.0 2446480 1516 ?? Ss 9:23am 0:00.09 postgres: autovacuum launcher process 631 0.0 0.0 2446348 520 ?? Ss 9:23am 0:00.32 postgres: wal writer process 630 0.0 0.0 2446348 580 ?? Ss 9:23am 0:00.48 postgres: writer process 520 0.0 0.1 2446348 3640 ?? S 9:22am 0:00.33 /usr/local/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log myusername 1187 0.0 0.0 2434892 540 s000 S+ 10:35am 0:00.00 grep postgres

Test whether the Unix domain socket is running:

ls -l /tmp/ | grep PGSQL | grep -v grep

gets me nothing at all.

Is postgres listening on internet protocol(s) localhost?

psql -H localhost

gets me:

psql: FATAL: database "localhost" does not exist

Is postgres listening on the Unix domain socket?

psql -H /tmp/.s.PGSQL.5432

gets me:

psql: FATAL: database "/tmp/.s.PGSQL.5432" does not exist

Answer 1

I found this answer helpful

Repairing Postgresql after upgrading to OSX 10.7 Lion

and resolved my error by putting this..

export PATH=/usr/local/bin:$PATH

in my .bash_profile

Answer 2

It seems like the .s.PGSQL.5432.lock which prevents editing of the socket file .s.PGSQL.5432 was left in place when it should have been deleted. This meant that when postgres tried to boot it was unable to because the lock file prevented it creating a new socket, even though there was no socket already in place for the lock file to protect.

Deleting .s.PGSQL.5432.lock and restarting postgres, then starting the (Rails) server normally worked fine.

Answer 3

The "port file" is a unix socket that PostgreSQL is listening on. It will create it on start-up. Stop the PostgreSQL server, delete the .lock file if it is still there, restart PG and see if your socket comes back.

I'm puzzled as to how it can not be there actually. I've never heard of that happening before.