Re: Connection Failure use DBI::Pg

On Jan 10, 2014, at 7:30 AM, Greg Sabino Mullane <[email protected]> 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