Thread: Unix domain sockets

I have written a function that will allow connections to the postgresql
database using tcp/ip sockets written in 'C'.  I have one application (still
in load testing phase) that runs fine and I can still access the database
using psql on unix domain sockets for the first couple of days.  All this
application is doing is inserting records to the postgres database and doing
some querys.  However after about 3 days I start to get the error:

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

My application is still happy, and I can see the postgres process that I am
using is still chugging away.  I have also installed postgres on another
machine and established that psql will connect using tcp/ip to the host and
database I am trying to connect to with unix domain sockets.  When my
application completes and I down postmaster and restart it everything is OK
again.  Problem is I consider downing a database as the next thing to a
hardware fault even if it is controlled.

My next step is to add this host to pg_hba.conf and see if I can connect by
specifing the host as an option to psql.

Can anyone point me to the correct e-mail list or does anyone have any ideas
as to what could cause this?

Thanks, Randy

Randy Neumann writes:

> However after about 3 days I start to get the error:
>
> psql: could not connect to server: No such file or directory
>     Is the server running locally and accepting
>     connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Perhaps the system cleans "old" files from /tmp?

If that is the case, you can move the socket (but all clients need to
know), update the timestamp on the socket and lock file now and then,
or switch to using TCP/IP for even local connections.

On a system where I have this problem I run a cron job to "freshen"
the timestamp of the socket and the lock file via utimes(2).  I can
dig the code up if anyone wants it, but it's pretty much just:

    utimes("/tmp/.s.PGSQL.5432", (const struct timeval *) 0);

Depending on your system's implementation touch(1) might be enough.

Regards,

Giles

Giles Lean <giles@nemeton.com.au> writes:
> Randy Neumann writes:
>> However after about 3 days I start to get the error:
>>
>> psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

> Perhaps the system cleans "old" files from /tmp?

Almost certainly, an over-aggressive /tmp-cleaning script is the
problem.  I'd recommend fixing the cleaner script to not delete
socket files.

            regards, tom lane

Ahem,

> Almost certainly, an over-aggressive /tmp-cleaning script is the
> problem.  I'd recommend fixing the cleaner script to not delete
> socket files.

I consider "/tmp" just that. Temporary.

*tough*

I still consider that if you do:

% su -
# rm -rf /tmp/*

...then things should still work ok.

But maybe I'm just weird...

postgres, sql, query, mysql, wotever.

DSL