unable to connect to pgsql server

I have a working system on OpenBSD 2.9 / PHP 4.0.4pl1 / pgsql 7.1 / phplib 7.4
I created a new system on OpenBSD 3.5 / PHP 4.3.5RC3 / pgsql 7.3.5 / phplib 7.4
I copied an existing database and php/phplib application to the new box for
testing, but when I try to access the application webpage, I get the
following message in apache error log:
PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: unknown
host name: localhost in /php/class/phplib-7.4/db_pgsql.inc on line 51

Line 51 of db_pgsql.inc calls pg_connect (obviously?). Since all code came
from a working system, I think I missed something during setup, but I'm not
sure what that might be. Based on similar questions in archives, I think
this info is relevant:

# cat /var/pgsql/data/postgresql.conf | grep -v ^# | grep -v ^$
tcpip_socket = true
shared_buffers = 512
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

# cat /var/pgsql/data/pg_hba.conf | grep -v ^#
local all all trust
host all all 127.0.0.1 255.255.255.255 trust

# cat /etc/hosts | grep -v ^#
::1 localhost
127.0.0.1 localhost

Frank


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: unable to connect to pgsql server

At 10:18 AM 5/24/04, Majolee InfoTech wrote:
>This means that PHP is not configured to work with PostgreSQL.


If this were true, how can phpinfo() say pgsql support is enabled?

PostgreSQL Support enabled
PostgreSQL(libpq) Version 7.3.5
Multibyte character support enabled
SSL support disabled
Active Persistent Links 0
Active Links 0

Directive Local Value Master Value
pgsql.allow_persistent On On
pgsql.auto_reset_persistent Off Off
pgsql.ignore_notice Off Off
pgsql.log_notice Off Off
pgsql.max_links Unlimited Unlimited
pgsql.max_persistent Unlimited Unlimited



>By the way checking for postmaster process being started before doing
>anything is worth here
>
>Try removing apache and PHP and then go thro this steps..
>
>[root@majolee7:/usr/local] # cd /root/src/
>
>[root@majolee7:/root/src] # gunzip apache_1.3.29.tar.gz
>
>[root@majolee7:/root/src] # tar -xvf apache_1.3.29.tar
>[root@majolee7:/root/src] # tar -xvf php-4.3.4.tar
>
>[root@majolee7:/root/src] # mv apache_1.3.29/ /usr/src
>
>[root@majolee7:/root/src] # mv php-4.3.4/ /usr/src
>
>[root@majolee7:/root/src] # cd /usr/src
>
>[root@majolee7:/usr/src] # cd apache_1.3.29/
>
>[root@majolee7:/usr/src/apache_1.3.29] # ./configure
>
>[root@majolee7:/usr/src/apache_1.3.29] # cd ..
>
>[root@majolee7:/usr/src/] # cd php-4.3.4/
>
>[root@majolee7:/usr/src/php-4.3.4] #
>
>[root@majolee7:/usr/src/php-4.3.4] #
>./configure --with-pgsql --with-apache=../apache_1.3.29 --enable-track-vars
>
>[root@majolee7:/usr/src/php-4.3.4] # make
>
>[root@majolee7:/usr/src/php-4.3.4] # make install
>
>[root@majolee7:/usr/src/php-4.3.4] # cd ..
>
>[root@majolee7:/usr/src] # cd apache_1.3.29/
>
>[root@majolee7:/usr/src/apache_1.3.29] #
>./configure --activate-module=src/modules/php4/libphp4.a
>
>[root@majolee7:/usr/src/apache_1.3.29] # make
>
>[root@majolee7:/usr/src/apache_1.3.29] # make install
>
>Open /usr/local/apache/conf/srm.conf File in to gEdit or Any Text Editor
>Add Follwing Line to the File & save the File
>Addtype application/x-httpd-php .php
>
>Open /etc/rc.d/rc.local File in to gEdit or Any Text Editor
>Add Follwing Line to the File & save the File
>/usr/local/apache/bin/apachectl start
>
>Hope this helps..
>
>===============
>Mihir & Biren
>Partners
>Majolee InfoTech
>===============
>----- Original Message -----
>From: "Frank Bax"
>To:
>Sent: Monday, May 24, 2004 7:07 PM
>Subject: [PHP] unable to connect to pgsql server
>
>
> > I have a working system on OpenBSD 2.9 / PHP 4.0.4pl1 / pgsql 7.1 / phplib
>7.4
> > I created a new system on OpenBSD 3.5 / PHP 4.3.5RC3 / pgsql 7.3.5 /
>phplib 7.4
> > I copied an existing database and php/phplib application to the new box
>for
> > testing, but when I try to access the application webpage, I get the
> > following message in apache error log:
> > PHP Warning: pg_connect(): Unable to connect to PostgreSQL server:
>unknown
> > host name: localhost in /php/class/phplib-7.4/db_pgsql.inc on line 51
> >
> > Line 51 of db_pgsql.inc calls pg_connect (obviously?). Since all code
>came
> > from a working system, I think I missed something during setup, but I'm
>not
> > sure what that might be. Based on similar questions in archives, I think
> > this info is relevant:
> >
> > # cat /var/pgsql/data/postgresql.conf | grep -v ^# | grep -v ^$
> > tcpip_socket = true
> > shared_buffers = 512
> > LC_MESSAGES = 'C'
> > LC_MONETARY = 'C'
> > LC_NUMERIC = 'C'
> > LC_TIME = 'C'
> >
> > # cat /var/pgsql/data/pg_hba.conf | grep -v ^#
> > local all all trust
> > host all all 127.0.0.1 255.255.255.255 trust
> >
> > # cat /etc/hosts | grep -v ^#
> > ::1 localhost
> > 127.0.0.1 localhost
> >
> > Frank


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: unable to connect to pgsql server

on 5/24/04 7:37 AM, fbax@sympatico.ca purportedly said:

> I have a working system on OpenBSD 2.9 / PHP 4.0.4pl1 / pgsql 7.1 / phplib 7.4
> I created a new system on OpenBSD 3.5 / PHP 4.3.5RC3 / pgsql 7.3.5 / phplib
> 7.4
> I copied an existing database and php/phplib application to the new box for
> testing, but when I try to access the application webpage, I get the
> following message in apache error log:
> PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: unknown
> host name: localhost in /php/class/phplib-7.4/db_pgsql.inc on line 51

Usually, this is either because PHP can't resolve localhost, or the Postgres
server can't/isn't responding. Can you use localhost otherwise? E.g.:
$ telnet localhost
Even if telnetd isn't running, you should get two different messages--one
that indicates the host won't resolve, and the other indicating that the
host isn't responding (if telnetd isn't running). If you cannot use
localhost from the command line you probably have some other configuration
problem. Also check your nsswitch.conf.

If the above works, make sure the postmaster (Postgres server) has enabled
IP connections. First check if Postmaster is listening using netstat. On my
Linux box, I would use netstat -lnp (but your options may be different). If
it is, check to make sure you are using the correct port. The default is
5432, so if you don't specify the port in the connect string the default
port will be used.

Another possibility is that something is blocking the connection, like a
firewall.

> # cat /etc/hosts | grep -v ^#
> ::1 localhost
> 127.0.0.1 localhost

If nothing else works, this is a wild guess, but I don't know if PHP has
IPv6 support. Just for the heck of it, you may want to try reversing the
above lines in your hosts file so PHP sees the IPv4 address first.

Best,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: unable to connect to pgsql server

At 12:02 PM 5/24/04, Keary Suska wrote:

>on 5/24/04 7:37 AM, fbax@sympatico.ca purportedly said:
>
> > I have a working system on OpenBSD 2.9 / PHP 4.0.4pl1 / pgsql 7.1 /
> phplib 7.4
> > I created a new system on OpenBSD 3.5 / PHP 4.3.5RC3 / pgsql 7.3.5 / phplib
> > 7.4
> > I copied an existing database and php/phplib application to the new box for
> > testing, but when I try to access the application webpage, I get the
> > following message in apache error log:
> > PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: unknown
> > host name: localhost in /php/class/phplib-7.4/db_pgsql.inc on line 51
>
>Usually, this is either because PHP can't resolve localhost, or the Postgres
>server can't/isn't responding. Can you use localhost otherwise? E.g.:
> $ telnet localhost
>Even if telnetd isn't running, you should get two different messages--one
>that indicates the host won't resolve, and the other indicating that the
>host isn't responding (if telnetd isn't running). If you cannot use
>localhost from the command line you probably have some other configuration
>problem.

telnet is disabled on OpenBSD by default by "ssh localhost" works fine.

> Also check your nsswitch.conf.

No such file on the system.

>If the above works, make sure the postmaster (Postgres server) has enabled
>IP connections. First check if Postmaster is listening using netstat. On my
>Linux box, I would use netstat -lnp (but your options may be different). If
>it is, check to make sure you are using the correct port. The default is
>5432, so if you don't specify the port in the connect string the default
>port will be used.


I couldn't figure out OpenBSD options for netstat. Do these prove the same
thing?
# netstat -l | grep 5432
0xd187f7d8 stream 0 0 0xe8844498 0x0 0x0 0x0
/tmp/.s.PGSQL.5432
# fstat | head -n 1 ; fstat | grep 5432
USER CMD PID FD MOUNT INUM MODE R/W DV|SZ
_pgsql postgres 5444 3* internet stream tcp 0xd18729dc *:5432

This also works:
$ psql -h localhost dbname dbname


> > # cat /etc/hosts | grep -v ^#
> > ::1 localhost
> > 127.0.0.1 localhost
>
>If nothing else works, this is a wild guess, but I don't know if PHP has
>IPv6 support. Just for the heck of it, you may want to try reversing the
>above lines in your hosts file so PHP sees the IPv4 address first.

Change order and/or removal of IPV6 entry has no effect.

>Another possibility is that something is blocking the connection, like a
>firewall.

I don't think any firewall software is installed/active by default and I
didn't install/activate any.

Frank


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: unable to connect to pgsql server

on 5/24/04 2:59 PM, fbax@sympatico.ca purportedly said:

> I couldn't figure out OpenBSD options for netstat. Do these prove the same
> thing?
> # netstat -l | grep 5432
> 0xd187f7d8 stream 0 0 0xe8844498 0x0 0x0 0x0
> /tmp/.s.PGSQL.5432

You could probably do this:
# netstat -ln | grep 5432

Which should show two entries--one for TCP/IP, one for Unix Domain Socket.

> # fstat | head -n 1 ; fstat | grep 5432
> USER CMD PID FD MOUNT INUM MODE R/W DV|SZ
> _pgsql postgres 5444 3* internet stream tcp 0xd18729dc *:5432

I am not familiar with this command, but it seems to be saying that you have
a listening connection on the right port.

Some other questions:

1) If you use the IP instead of host name, do you get the same effect?
2) Have you tried not specifying a host--in which case the Unix domain
socket will be used--and does it work?

If any of the above workarounds work, I recommend using them, as I can't
imagine what the problem may be.

Best regards,

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: unable to connect to pgsql server

At 01:02 PM 5/25/04, Keary Suska wrote:

>on 5/24/04 2:59 PM, fbax@sympatico.ca purportedly said:
>
> > I couldn't figure out OpenBSD options for netstat. Do these prove the same
> > thing?
> > # netstat -l | grep 5432
> > 0xd187f7d8 stream 0 0 0xe8844498 0x0 0x0 0x0
> > /tmp/.s.PGSQL.5432
>
>You could probably do this:
># netstat -ln | grep 5432
>
>Which should show two entries--one for TCP/IP, one for Unix Domain Socket.


# netstat -ln | grep 5432
tcp 0 0 192.168.1.90.29958 192.168.1.90.5432 TIME_WAIT
0xd187f7d8 stream 0 0 0xe8844498 0x0 0x0 0x0
/tmp/.s.PGSQL.5432


> > # fstat | head -n 1 ; fstat | grep 5432
> > USER CMD PID FD MOUNT INUM MODE R/W DV|SZ
> > _pgsql postgres 5444 3* internet stream tcp 0xd18729dc *:5432
>
>I am not familiar with this command, but it seems to be saying that you have
>a listening connection on the right port.
>
>Some other questions:
>
>1) If you use the IP instead of host name, do you get the same effect?


First I got error about ip not in pg_hba.conf, so I added
host all all 192.168.1.90 255.255.255.255 trust
- when I fixed that and rebooted, the system works!!! I then removed the
above line, rebooted and tried host=127.0.0.1 in pg_connect instead of
host=localhost and that works too! So why doesn't host=localhost work?


>2) Have you tried not specifying a host--in which case the Unix domain
>socket will be used--and does it work?

It doesn't work - but different error.

PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: 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"?
in /php/class/phplib-7.4/db_pgsql.inc on line 51

I think I know what's causing this problem - apache is running with "chroot
/var/www" - a change in default install sometime between OpenBSD 2.9 and
3.5 - could this also be causing the problem with tcpip socket?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: unable to connect to pgsql server

on 5/26/04 7:50 AM, fbax@sympatico.ca purportedly said:

> First I got error about ip not in pg_hba.conf, so I added
> host all all 192.168.1.90 255.255.255.255 trust
> - when I fixed that and rebooted, the system works!!! I then removed the
> above line, rebooted and tried host=127.0.0.1 in pg_connect instead of
> host=localhost and that works too! So why doesn't host=localhost work?
>
>
>> 2) Have you tried not specifying a host--in which case the Unix domain
>> socket will be used--and does it work?
>
> It doesn't work - but different error.
>
> PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: 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"?
> in /php/class/phplib-7.4/db_pgsql.inc on line 51
>
> I think I know what's causing this problem - apache is running with "chroot
> /var/www" - a change in default install sometime between OpenBSD 2.9 and
> 3.5 - could this also be causing the problem with tcpip socket?

It very well could be. They way to know is to try it sans chroot. If it
works, the next headache is trying to figure out what precisely is causing
the problem. From what I have heard, using Apache with chroot is such a pain
in the ass that most security conscious admins don't think it's worth the
effort.

But if you are set on using chroot: First, you should ldd the PHP binary and
make sure all libs are in the chroot space. Also, ldd each lib and make sure
any libs they call are in as well. Second, make sure any DNS resolving files
are also in the chroot space. I am not sure which ones are needed, but
usually at least the hosts file and resolv.conf.

Good luck,

Keary Suska
(719) 473-6431
(719) 440-9952 (cell)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org