Why can't I connect to postgres from Perl?

Question

I believe I have set up Pg properly, but my script doesn't seem to be connecting to the database. I am testing with:

$database="networkem";
$user="postgres";
$password="";
$host="localhost";

$dbh = DBI->connect("DBI:Pg:dbname=$dbname;host=$host", $user, $password);

My pg_hba reads:

host    all    postgres   127.0.0.1    255.255.255.255    trust

I can use psql just fine via command-line and have started postmaster with -i option. What am I missing?

I also tried with another user that works fine via psql:

$user="jimbo"; $password="p2ssw0rd";

with pg_hba reading:

host    all    jimbo   127.0.0.1    255.255.255.255    trust

Answer 1

Rather than play 20 questions to debug your setup, DBI->errstr will say why the connection failed.

my $dbh = DBI->connect(...) or die DBI->errstr;

Though if I had to guess... since Postgres authenticates based on host and login user, I suspect the confusion lies between the user name you're giving to the Postgres connection and the Unix user you're logged in as.

Answer 2

Besides Schwern's excellent response, you can also check PostgreSQL log which, depending on the options selected in postgresql.conf may tell you a lot about what was wrong.

Answer 3

It is recommended that you use the 'listen_addresses' configuration option in your postgresql.conf instead of '-i' on the command line. For example:

listen_addresses = '*'

Try executing the following command as the same user you are running your perl script with:

psql -U postgres -h localhost networkem

The '-h localhost' forces a network connection instead of a Unix socket connection. If that command works, your perl script should also work.

Answer 4

I had the same issue. The hint above for trying "-h localhost" confirmed that I had a problem connecting over the network.

Adding the following to pg_hba.conf fixed the problem.

host all postgres 127.0.0.1/32 trust

Answer 5

hey, can you show me an example on how to insert into postgresql database using perl web front end. I know how to retrieve record from the database.