PostgreSQL trouble

Hello,
I have some trouble connecting my PostgreSQL-Server.
When I execute this small script:
#!/usr/bin/perl

use warnings;
use strict;

use DBI;

my $dbh = DBI->connect ("DBI:Pg:dbname=test2", "postgres", "wunderbar")
or die "Keine Verbindung mit der DB!\n";

exit;

I get this error:
DBI connect('dbname=test2','postgres',...) failed: FATAL:
Ident-Authentifizierung für Benutzer »postgres« fehlgeschlagen
at ./postgre_test_1.pl line 8
Keine Verbindung mit der DB!

What I have to change for the Cluster? Where are the changes? What I have to
consider, and can I use your PostgreSQL-Server without the cluster?

Thank you.

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: PostgreSQL trouble

--------------010001020308020009000400
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Christian,
What does your "pg_hba.conf" file look like? If you haven't made any
changes in there, for where/who/how is allowed to connect to this
postgreSQL db instance, it's possible that's the first place to start.
Second, it looks like in your connect string, you haven't identified a
host to connect to.

("DBI:Pg:dbname=test2;host=HOSTNAME;port=PORT_IF_OTHER_THAN_ 5432",
"postgres"... seems correct)

Those would be the first two places to check. Let me know how:
1) you have/have not configured the pg_hba.conf file
2) from where is script executing from? (remote - TCP socket -, local
UNIX/Windows file socket)

Regards,

Christian Stalp wrote:

>Hello,
>I have some trouble connecting my PostgreSQL-Server.
>When I execute this small script:
>#!/usr/bin/perl
>
>use warnings;
>use strict;
>
>use DBI;
>
>my $dbh = DBI->connect ("DBI:Pg:dbname=test2", "postgres", "wunderbar")
> or die "Keine Verbindung mit der DB!\n";
>
>exit;
>
>I get this error:
>DBI connect('dbname=test2','postgres',...) failed: FATAL:
>Ident-Authentifizierung für Benutzer »postgres« fehlgeschlagen
> at ./postgre_test_1.pl line 8
>Keine Verbindung mit der DB!
>
>What I have to change for the Cluster? Where are the changes? What I have to
>consider, and can I use your PostgreSQL-Server without the cluster?
>
>Thank you.
>
>Gruss Christian
>
>
>


--------------010001020308020009000400--

Re: PostgreSQL trouble

Dear Lois,
thank you for your help.
And sorry for forgotten my "pg_hba.conf", that is really important.
Here it comes:
# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD
#host all all
# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1 255.255.255.255
trust
# IPv6 local connections:
host all all ::1/128 md5

I called this script from my home-dir under Linux. It worked allready with
PostgreSQL 7.5 but not with 8.1 and the postgre-datebase-cluster.

Gruss Christian


> Christian,
> What does your "pg_hba.conf" file look like? If you haven't made any
> changes in there, for where/who/how is allowed to connect to this
> postgreSQL db instance, it's possible that's the first place to start.
> Second, it looks like in your connect string, you haven't identified a
> host to connect to.
>
> ("DBI:Pg:dbname=test2;host=HOSTNAME;port=PORT_IF_OTHER_THAN_ 5432",
> "postgres"... seems correct)
>
> Those would be the first two places to check. Let me know how:
> 1) you have/have not configured the pg_hba.conf file
> 2) from where is script executing from? (remote - TCP socket -, local
> UNIX/Windows file socket)
>
> Regards,
>


--
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse für Mail, Message, More +++

Re: PostgreSQL trouble

I tried this:
my $dbh = DBI->connect ("DBI:Pg:dbname=test2", *host="localhost" )
and got this:
Name "main::host" used only once: possible typo at ./postgre_test_1.pl line
9.
DBI connect('dbname=test2','*main::localhost',...) failed: konnte nicht mit
demServer verbinden: øÿø·xü·
Läuft der Server lokal und akzeptiert er Verbindungen
auf dem Unix-Domain-Socket »/var/run/postgresql/.s.PGSQL.5432«?
at ./postgre_test_1.pl line 9
Keine Verbindung mit der DB!
Which means: could not connect to server. Does the server runs local and
does it accepts connections on UNINX-DOMAIN_Socket >>/var... rest you can
see.

My Linux is Debian 3.1 Sarge but the PostgreSQL-Server is a Backport. You
know about backports? www.backport.org

chris@venus:~/perl/dbi$ uname -a
Linux venus 2.6.14.5 #1 PREEMPT Sun Jan 8 23:30:58 CET 2006 i686 GNU/Linux

Gruss Christian

> --- Ursprüngliche Nachricht ---
> Von: Louis Gonzales
> An: Christian Stalp
> Betreff: Re: PostgreSQL trouble
> Datum: Mon, 23 Jan 2006 11:04:35 -0500
>
> Have you tried adding ( dbname="test";*host=____ ) where host = either
> 127.0.0.1 OR localhost*
> just try that before you go any further.
>
> Which version of Linux are you running? Send me the results of:
> uname -a
>
> what command are you using to start up postgreSQL?
>
>
> Christian Stalp wrote:
>
> >Dear Lois,
> >thank you for your help.
> >And sorry for forgotten my "pg_hba.conf", that is really important.
> >Here it comes:
> ># Database administrative login by UNIX sockets
> >local all postgres ident sameuser
> >
> ># TYPE DATABASE USER CIDR-ADDRESS METHOD
> >#host all all
> ># "local" is for Unix domain socket connections only
> >local all all ident sameuser
> ># IPv4 local connections:
> >host all all 127.0.0.1 255.255.255.255
> >trust
> ># IPv6 local connections:
> >host all all ::1/128 md5
> >
> >I called this script from my home-dir under Linux. It worked allready
> with
> >PostgreSQL 7.5 but not with 8.1 and the postgre-datebase-cluster.
> >
> >Gruss Christian
> >
> >
> >
> >
> >>Christian,
> >>What does your "pg_hba.conf" file look like? If you haven't made any
> >>changes in there, for where/who/how is allowed to connect to this
> >>postgreSQL db instance, it's possible that's the first place to start.
> >>Second, it looks like in your connect string, you haven't identified a
> >>host to connect to.
> >>
> >>("DBI:Pg:dbname=test2;host=HOSTNAME;port=PORT_IF_OTHER_THA N_5432",
> >>"postgres"... seems correct)
> >>
> >>Those would be the first two places to check. Let me know how:
> >>1) you have/have not configured the pg_hba.conf file
> >>2) from where is script executing from? (remote - TCP socket -, local
> >>UNIX/Windows file socket)
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
> >
> >
>
>

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: PostgreSQL trouble

> what command are you using to start up postgreSQL?
>

Oh yes, this is an entry-script in init.d:

#!/bin/bash

.. /usr/share/postgresql-common/init.d-functions

VERSION=8.1

case "$1" in
start)
start $VERSION
;;
stop)
stop "$VERSION"
;;
restart)
restart "$VERSION"
;;
force-reload | reload)
reload $VERSION
;;
status)
status $VERSION
;;
autovac-start)
autovac_start $VERSION
;;
autovac-stop)
autovac_stop $VERSION
;;
autovac-restart)
autovac_restart $VERSION
;;
*)
echo "Usage: $0
{start|stop|restart|reload|force-reload|status|autovac-start |autovac-stop|autovac-restart}"
exit 1
;;
esac

exit 0


--
Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner

Re: PostgreSQL trouble

I tried this:

my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.01;port=5432" ,
"postgres") or die $DBI:errstr;
test2 is the name of the database.

and got this:
Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
line12.
syntax error at ./postgre_test_1.pl line 12, near "$DBI:"
Execution of ./postgre_test_1.pl aborted due to compilation errors.

Gruss Christian

--
DSL-Aktion wegen großer Nachfrage bis 28.2.2006 verlängert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: PostgreSQL trouble

I'm not being wise here, but did you do

use DBI;

at the top of your script?

Hardy Merrill

>>> "Christian Stalp" 1/23/2006 11:57 AM >>>
I tried this:

my $dbh =3D DBI->connect("dbi:Pg:database=3Dtest2;host=3D127.0.01;port=3 D54=
32",
"postgres") or die $DBI:errstr;
test2 is the name of the database.

and got this:
Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
line12.
syntax error at ./postgre_test_1.pl line 12, near "$DBI:"
Execution of ./postgre_test_1.pl aborted due to compilation errors.

Gruss Christian

--=20
DSL-Aktion wegen gro*er Nachfrage bis 28.2.2006 verl*ngert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Re: PostgreSQL trouble

--------------030702020607000603010902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Stalp wrote:

>I tried this:
>
>my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.01;port=5432" ,
>"postgres") or die $DBI:errstr;
>test2 is the name of the database.
>
>and got this:
>Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
>line12.
>syntax error at ./postgre_test_1.pl line 12, near "$DBI:"
>Execution of ./postgre_test_1.pl aborted due to compilation errors.
>
>Gruss Christian
>
>
>
Christian,
Again...look at what you typed: host=127.0.01 and not host=127.0.0.1

--------------030702020607000603010902--

RE: PostgreSQL trouble

Christian Stalp [mailto:christian.stalp@gmx.de] wrote:

> I tried this:
>
> my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.01;port=5432" ,
> "postgres") or die $DBI:errstr;
^^^^^^^^^^^

$DBI::errstr, not $DBI:errstr. Note how the error messages call your
attention to this error:

> and got this:
> Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
> line12.
> syntax error at ./postgre_test_1.pl line 12, near "$DBI:"

Ronald

Re: PostgreSQL trouble

--------------010000010009080505000402
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Ronald J Kimball wrote:

>Christian Stalp [mailto:christian.stalp@gmx.de] wrote:
>
>
>
>>I tried this:
>>
>>my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.01;port=5432" ,
>>"postgres") or die $DBI:errstr;
>>
>>
> ^^^^^^^^^^^
>
>$DBI::errstr, not $DBI:errstr. Note how the error messages call your
>attention to this error:
>
>
>
>>and got this:
>>Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
>>line12.
>>syntax error at ./postgre_test_1.pl line 12, near "$DBI:"
>>
>>
>
>Ronald
>
>
>
>
Ronald,
that was my typo:.

Christian,
here is the correct format:

my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.0.1;port=5432 ", "postgres") or die $DBI::errstr;


--------------010000010009080505000402--

RE: PostgreSQL trouble

The whole script:

#!/usr/bin/perl

use warnings;
use strict;

use DBI;

#my $dbh = DBI->connect ("DBI:Pg:dbname=test2", "postgres", "sonderbar")
#my $dbh = DBI->connect ("DBI:Pg:dbname=test2", *host="localhost" )
# or die "Keine Verbindung mit der DB!\n";
my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.0.1;port=5432 ",
"postgres") or die $DBI::errstr;

But the result is the same, accept for it is a little bit more informativ.

DBI connect('database=test2;host=127.0.0.1;port=5432','postgres' ,...)
failed: konnte nicht mit dem Server verbinden: 8tã·ø€ù·
Läuft der Server auf dem Host »127.0.0.1« und akzeptiert er
TCP/IP-Verbindungen auf Port 5432?
at ./postgre_test_1.pl line 11
konnte nicht mit dem Server verbinden: 8tã·ø€ù·
Läuft der Server auf dem Host »127.0.0.1« und akzeptiert er
TCP/IP-Verbindungen auf Port 5432?
Means: could not connect to your server, does it really allow conections
over net? I think its really the server.

Here comes the pg_hba.conf again:

# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD
#host all all
# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1 255.255.255.255 trust
# IPv6 local connections:
host all all ::1/128 md5

Gruss Christian

--
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse für Mail, Message, More +++

Re: PostgreSQL trouble

Christian Stalp wrote:

>The whole script:
>
>#!/usr/bin/perl
>
>use warnings;
>use strict;
>
>use DBI;
>
>#my $dbh = DBI->connect ("DBI:Pg:dbname=test2", "postgres", "sonderbar")
>#my $dbh = DBI->connect ("DBI:Pg:dbname=test2", *host="localhost" )
># or die "Keine Verbindung mit der DB!\n";
>my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.0.1;port=5432 ",
>"postgres") or die $DBI::errstr;
>
>But the result is the same, accept for it is a little bit more informativ.
>
>DBI connect('database=test2;host=127.0.0.1;port=5432','postgres' ,...)
>failed: konnte nicht mit dem Server verbinden: 8tã·ø€ù·
> Läuft der Server auf dem Host »127.0.0.1« und akzeptiert er
> TCP/IP-Verbindungen auf Port 5432?
> at ./postgre_test_1.pl line 11
>konnte nicht mit dem Server verbinden: 8tã·ø€ù·
> Läuft der Server auf dem Host »127.0.0.1« und akzeptiert er
> TCP/IP-Verbindungen auf Port 5432?
>Means: could not connect to your server, does it really allow conections
>over net? I think its really the server.
>
>Here comes the pg_hba.conf again:
>
># Database administrative login by UNIX sockets
>local all postgres ident sameuser
>
># TYPE DATABASE USER CIDR-ADDRESS METHOD
>#host all all
># "local" is for Unix domain socket connections only
>local all all ident sameuser
># IPv4 local connections:
>host all all 127.0.0.1 255.255.255.255 trust
># IPv6 local connections:
>host all all ::1/128 md5
>
>Gruss Christian
>
>
>
Have you got
tcpip_socket = true
in postgresql.conf?

Re: PostgreSQL trouble

> Christian,
> what is the result of this:
>
> netstat -an | grep 5432
nothing ;-) The server does not here on the net. But with pure DBI, can i
do it without IP? For the first connection I mean? I mean with an
UNIX-Socket or however DBI connects to a local server?

Gruss Christian

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: PostgreSQL trouble

> Have you got
> tcpip_socket = true
> in postgresql.conf?
>
Yea, exactly. This is funny. I allready tried this. See:
venus:/home/chris# pg_ctlcluster 8.1 main restart
The PostgreSQL server failed to start. Please check the log output:
FATAL: unbekannter Konfigurationsparameter »tcpip_connections«
means: unknown konfig param: »tcpip_connections«

I was wondering about this. This parameter is in the documentation!!!

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie

Re: PostgreSQL trouble

--------------070905060102060603000805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Stalp wrote:

>>Christian,
>>what is the result of this:
>>
>>netstat -an | grep 5432
>>
>>
>nothing ;-) The server does not here on the net. But with pure DBI, can i
>do it without IP? For the first connection I mean? I mean with an
>UNIX-Socket or however DBI connects to a local server?
>
>Gruss Christian
>
>
>
Christian,
that explains a lot. Even with your original syntax, your script was
attempting to connect to the localhost by default and port 5432. you're
just running a local unix domain socket not a TCP/IP socket. can't you
start up postgresql on localhost and port 5432?

pg_ctl stop - will stop the database


pg_ctl -w -o "-h 127.0.0.1 -p 5432" -l logfile start - will start it
backup try this, then run your script.

you have to be the postgres user.

--------------070905060102060603000805--

Re: PostgreSQL trouble

--------------000701070205060206070704
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Christian Stalp wrote:

>>Christian,
>>what is the result of this:
>>
>>netstat -an | grep 5432
>>
>>
>nothing ;-) The server does not here on the net. But with pure DBI, can i
>do it without IP? For the first connection I mean? I mean with an
>UNIX-Socket or however DBI connects to a local server?
>
>Gruss Christian
>
>
>
Christian,
your database= then must be:

database=/tmp/.s.PGSQL.5432

my $dbh = DBI->connect("dbi:Pg:database=/tmp/.s.PGSQL.5432", "postgres",
"") or die $DBI::errstr;

or whatever the unix domain socket is!

also RTFM for postgreSQL 8.1 @
http://www.postgresql.org/docs/8.1/interactive/libpq.html

try that... and let me know what happens.

--------------000701070205060206070704--

Re: PostgreSQL trouble

--------------030302080507030206050901
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Christian,
THIS WILL WORK:

my $dbh = DBI->connect("dbi:Pg:database=test2;host=/tmp;port=5432",
"postgres", "") or die $DBI:errstr;



--------------030302080507030206050901--

Re: PostgreSQL trouble

Hello Lois,
I have to check my PostgreSQL-Server first. Now as it seems I have not only
a pure server but a cluster. And this means some changes in configuration. I
don't know if I have time today to solfe this. But I give you answer when Im
finished.

Thank you so far, until tomorrow.

Gruss Christian


> --- Ursprüngliche Nachricht ---
> Von: Louis Gonzales
> An: Louis Gonzales
> Kopie: Christian Stalp , dbi-users@perl.org
> Betreff: Re: PostgreSQL trouble
> Datum: Mon, 23 Jan 2006 13:28:00 -0500
>
> Christian,
> THIS WILL WORK:
>
> my $dbh = DBI->connect("dbi:Pg:database=test2;host=/tmp;port=5432",
> "postgres", "") or die $DBI:errstr;
>
>
>

--
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse für Mail, Message, More +++

Re: PostgreSQL trouble

Christian Stalp wrote:

>
>
>>Have you got
>>tcpip_socket = true
>>in postgresql.conf?
>>
>>
>>
>Yea, exactly. This is funny. I allready tried this. See:
>venus:/home/chris# pg_ctlcluster 8.1 main restart
>The PostgreSQL server failed to start. Please check the log output:
>FATAL: unbekannter Konfigurationsparameter »tcpip_connections«
> means: unknown konfig param: »tcpip_connections«
>
>I was wondering about this. This parameter is in the documentation!!!
>
>
>
No - not tcpip_connections

tcpip_socket

It must be set to true in postgresql.conf

Re: PostgreSQL trouble

> --- Ursprüngliche Nachricht ---
> Von: Louis Gonzales
> An: Louis Gonzales
> Kopie: Christian Stalp , dbi-users@perl.org
> Betreff: Re: PostgreSQL trouble
> Datum: Mon, 23 Jan 2006 13:28:00 -0500
>
> Christian,
> THIS WILL WORK:
>
> my $dbh = DBI->connect("dbi:Pg:database=test2;host=/tmp;port=5432",
> "postgres", "") or die $DBI:errstr;
>
>
>

Hello Louis,
I just want you to know that I found a solution.
I was interrupted by an other problem, so sorry for the delay.
First of all I made some changes in the pg_hba.conf:

# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD
#host all all
# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1 255.255.255.255 md5
# IPv6 local connections:
host all all ::1/128 md5

The difference is at host all all 127.0.0.1 255... md5. Before I made this
with trust, and tried to log in as user postgres. Now I made an extra-user
for this operations, and with this combination and with the DBI-connection:
my $dbh = DBI->connect("dbi:Pg:database=$db_name;host=127.0.0.1;port=5 432",
"$db_user", "$db_passwd") or die $DBI::errstr;
.... it works ;-)
It is no longer necessary to write any "tcp-Socket=true" (or something else)
Option in to the postgresql.conf.

Thanks all for your help.

Gruss Christian

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie