Connection Failure use DBI::Pg

[W. Lincoln Harvey]

I am trying to connect to PostgreSQL with DBI on Mac OSX 10.8. Everything appears to be installed correctly, and I can connect manually to my db with psql. However, when I try to connect with DBI::Pg, I get the following error:

DBI connect('db=mydb','lincoln',...) failed: 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"? at ./dbipic2.pl line 41.

Any help is greatly appreciated.

Lincoln

[Greg Sabino Mullane]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> I am trying to connect to PostgreSQL with DBI on Mac OSX 10.8.

...

> Is the server running locally and accepting
> connections on Unix domain socket
> "/var/pgsql_socket/.s.PGSQL.5432"? at ./dbipic2.pl line 41.

This is probably caused by the socket directory not being
where DBD::Pg thinks it is. Connect as psql and run this:

show unix_socket_directory;

(Or 'directories' for newer versions of Postgres)

If that is not showing /var/pgsql_socket then you have half
solved the problem. Next step is to figure out why the diconnect.
The solution is to get Postgres to run in the "correct" directory,
or to compile DBD::Pg to use the same socket directory as
what Postgres is using.

For a stopgap measure (and to test), you can specify the socket
directory as the hostname in your connection string: just make
sure it starts with a slash. For example:

$dsn = "dbi:Pg:db=foobar;port=5432;host=/var/pgsql_socket";
$dbh = DBI->connect($dsn, $user, $pass, ...)

You can also set the environment variable PGHOST for the same effect.

- --
Greg Sabino Mullane gr...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201401101028
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8




-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlLQEcUACgkQvJuQZxSWSsh9YwCffLuZiKvbSgYBtFNsZ+nLT1kv
CxgAoMnB8VNccdg9EMjxF+XH8zreHu/4
=iIkZ
-----END PGP SIGNATURE-----

[W. Lincoln Harvey]

On Jan 10, 2014, at 7:30 AM, Greg Sabino Mullane <gr...@turnstep.com> wrote:

Greg,

You have solved the problem correctly:

>> I am trying to connect to PostgreSQL with DBI on Mac OSX 10.8.
> ...
>> Is the server running locally and accepting
>> connections on Unix domain socket
>> "/var/pgsql_socket/.s.PGSQL.5432"? at ./dbipic2.pl line 41.
>
> This is probably caused by the socket directory not being
> where DBD::Pg thinks it is. Connect as psql and run this:
>
> show unix_socket_directory;
>
> (Or 'directories' for newer versions of Postgres)

You are correct: 'show unix_socket_directories' reveals: /tmp

>
> If that is not showing /var/pgsql_socket then you have half
> solved the problem. Next step is to figure out why the diconnect.
> The solution is to get Postgres to run in the "correct" directory,
> or to compile DBD::Pg to use the same socket directory as
> what Postgres is using.
>
> For a stopgap measure (and to test), you can specify the socket
> directory as the hostname in your connection string: just make
> sure it starts with a slash. For example:
>
> $dsn = "dbi:Pg:db=foobar;port=5432;host=/var/pgsql_socket";
> $dbh = DBI->connect($dsn, $user, $pass, ...)
>
> You can also set the environment variable PGHOST for the same effect.

I set PGHOST to '/tmp' and it now works. Thank you for your help!

Question: Is PostgreSQL configured incorrectly so that it uses /tmp instead of /var, or is this not really an issue or problem?
I compiled PostgreSQL from source and installed it into the unix default directories, but I see that Enterprise DB does things very differently for
the Macintosh; they provide a binary and an installer that creates a completely new user account under the Mac OS; I did not like that
method so I just compiled it, but perhaps there is something fundamentally better about doing it their way? I thought they were doing it that
way for security reasons, but I have no real security issues since I am the only user of this machine and will only be running it locally.

Thanks again for your help.

Lincoln