[ic] dbi:Pg DSN remote postgresql database connections

Matthew J. Harmon interchange-users@icdevgroup.org
Wed Sep 18 02:25:04 2002


Overview: remote PostgreSQL connections over TCP/IP instead of UNIX 
sockets are failing.

Summary: Although everything appears correctly configured, Interchange 
keeps trying local socket connections even when I specify a host and 
port in the DBI DSN.  The Postgres database is on another server, on the 
same network, all ports are available, command line perl connections 
work, telnet connections succeed.

Pardon the long Subject: I've spent the past day running tcpdump + 
strace, searching (interchange.redhat.com, postgres.org, perldoc and 
google.com, perlmonks, perl.org) for information on this, bugging the 
terrific (patient) guys on EFNet #postgresql so I want anyone else 
searching for this information to have many catchall's in the subject :)

I have installed Interchange on a Linux box (Kernel 2.4.19) with a CPAN 
update of all of the Interchange Perl (5.6.1) modules (DBI release 
2002/02/09) and this is a stable environment using PostgreSQL 7.2.

When I ran a command line connection for perl, it connected fine.

host:~/products$ perl -e 'use DBD::Pg; my $databh = 
DBI->connect("dbi:Pg:host=foo 
dbname=database","postgres","password",{AutoCommit => 1, PrintError=>1, 
RaiseError => 1}) || warn ("Non Encrypted Connect_Error: Cant Connect 
user to Live Database"); $databh->disconnect(); print "Okay \n"'
Okay

When I telnet directly to the host:port it works fine:
host:~/products$ telnet foo 5432
Trying 10.0.0.2...
Connected to foo.
Escape character is '^]'.

Yes, tcpip_socket = true is in interchange.cfg

The error I am receiving is:
run "createdb nexpedition"? y
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"?
createdb: database creation failed
Command returned error code 1: No such file or directory
done.

The problem appears to be that Interchange is not attempting a TCP/IP 
connection to the host, and keeps trying socket connections.  How do I 
change this?

The DBI DSN's I have tried are:
dbi:Pg:host=10.0.0.2 dbname=database
dbi:Pg:dbname=database host=10.0.0.2
dbi:Pg:host=10.0.0.2;dbname=database
dbi:Pg:dbname=database'host=10.0.0.2
dbi:Pg:host=foo.bar.com;dbname=database;port=5432
dbi:Pg:host=foo.bar.com dbname=database port=5432
dbi:Pg:host=foo;dbname=database;port=5432
dbi:Pg:host=foo dbname=database port=5432

Thanks for any tips.  I am "OK" with Perl and Postgres, a year or so 
with Perl (still need references) and about two months with Postgres.

-Matthew