Solved

a2billing cannot connect to postgre database

Posted on 2008-10-06
28
1,188 Views
Last Modified: 2012-06-27
I'm running two instances of postgre . The one listens on port 5432 and the other on 5434.
A2Billing is to use 5432. When it tries to connect it gets error: "Connection refused" , PLEASE HELP.

Setting in a2billing.conf are:
[database]
hostname = localhost
port = 5434
user = shaunw
password = xxxxx
dbname = a2billing
dbtype = postgres


netstat -tlnp

tcp        0      0 127.0.0.1:5434              0.0.0.0:*                   LISTEN      29109/postgres

ph_hba has entry
host    all         all         127.0.0.1/32          trust


Error I'm getting:

PHP Warning:  pg_connect() [<a href='function.pg-connect'>function.pg-connect</a>]: Unable to connect to PostgreSQL server: FATAL:  no pg_hba.conf entry for host &quot;127.0.0.1&quot;, user &quot;shaunw&quot;, database &quot;a2billing&quot;, SSL off in /home/shaunw/html/A2Billing_UI/lib/adodb/drivers/adodb-postgres64.inc.php on line 686, referer: http://www.DELETED BY ASKER REQUEST

VEE_MOD
0
Comment
Question by:shaunwingin
  • 12
  • 5
  • 5
  • +1
28 Comments
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22655223
If you've got two instances running they will obviously be in different data directories - are you confident that the configuration is the right one and that the pg_hba.conf you've quoted isn't the one for the other instance?

0
 

Author Comment

by:shaunwingin
ID: 22656514
Its the correct one, tx.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22660820
You said it is supposed to be hitting 5432, but your config shows 5434.  Which is it?

Either way, did you check the pg_hba.conf file for the appropriate host entry?

http://www.postgresql.org/docs/8.0/interactive/client-authentication.html#AUTH-PG-HBA-CONF
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22660844
>>> ph_hba has entry
>>> host    all         all         127.0.0.1/32          trust

never mind...I see it now.  :/

Please verify the configuration you're supposed to be using, and that you've checked the correct file.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22660886
Did you restart the server after adding that config to pg_hba?  Have you tried using local (sockets) instead of the loopback (127.0.0.1)?
0
 

Author Comment

by:shaunwingin
ID: 22661527
Sorry a typo: A2Billing is to use 5434 (as per the A2Billing config). I have tried restarting numerouse time. How would I use local (sockets)?
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22662131
Can you connect locally to the server from the command line using psql?  The error indicates that the server is running, but cannot find proper authentication for the user.  Are you sure the password is correct?  I don't want to beat a dead horse, but are you *sure* you added that line to the correct pg_hba file?

See here for how to start the server and allow it to use local sockets:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html

I don't have a lot of experience with Postgres, but I'll help you where I can.  Let's hope some other experts with a little more skill jump in.
0
 

Author Comment

by:shaunwingin
ID: 22662344
The line is actually there by default  - but I'm sure tx.
I can connect to it locally.
Using local sockets may not help A2billing to connect as it doesn't seem to offer them as an option.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22662527
Have you tried explicitly naming the user in pg_hba?
0
 

Author Comment

by:shaunwingin
ID: 22662718
I get same result.
I did a test where I renamed pg_hba.conf to pg_hba.con and this is the result. That proves its using it.

 /home/shaunw/postgre/bin/pg_ctl -D /home/shaunw/postgre/a2billing/ restart
waiting for server to shut down.... done
server stopped
server starting
[shaunw@messaging a2billing]$ FATAL:  could not open configuration file "/home/shaunw/postgre/a2billing/pg_hba.conf": No such file or directory

See the file below.
This is the last lines pf pg_hba.conf
 

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 

# "local" is for Unix domain socket connections only

local   all         shaunw                               trust

# IPv4 local connections:

#host     all         all         *                    trust

host    all         all         127.0.0.1/32           trust

# IPv6 local connections:

host    all         all         ::1/128               trust

Open in new window

0
 
LVL 28

Expert Comment

by:lesouef
ID: 22668652
do you have any admin tool to make sure postgre is OK?
could the fault be on php side? like 2 connect strings in different files or so??
0
 

Author Comment

by:shaunwingin
ID: 22681899
I don't have any admin tools. What can you suggest?
"psql -d a2billing -U shaunw -W" connects to db and I can see tables.
can't see how it can be in php as it reads the correct file.

I've created a second database called  asterisk  but when I try connecting to it with "psql -d asterisk -U shaunw -W" get an error"

[shaunw@messaging bin]$ psql -d asterisk -U shaunw -W
Password for user shaunw:
psql: FATAL:  database "asterisk" does not exist

log file:
 LOG:  connection received: host=[local]
LOG:  connection authorized: user=shaunw database=asterisk
FATAL:  database "asterisk" does not exist

However it exists! see....
[shaunw@messaging asterisk]$ ls
base  global  pg_clog  pg_hba.conf  pg_ident.conf  pg_multixact  pg_subtrans  pg_tblspc  pg_twophase  PG_VERSION  pg_xlog  postgresql.conf  postmaster.opts
[shaunw@messaging asterisk]$


This may be a clue,

Please see the method I used to install the postgres as I'm running two databases! the one is version 8.11 on port 5432 and the other 8.3 on port 5434 . I only own the one on 5434 and con't access the other one. They are installed in different directories.

http://www.experts-exchange.com/Networking/Misc/Q_23745637.html
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22682093
i can't really say more as I am using mysql, so I have no sharp knowledge in postgre.
obviously yr database is created, but not published. do you have a phpmyadmin tool for this?
can you see if the port is busy? use tcpview utility to check that
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:shaunwingin
ID: 22682195
The port isn't busy. Can someone using postgre please help.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22683087
if the port is not busy, then what can prevent it from working? internal windows firewall?
does it work if you disable the other one?
tools you can use:
http://www.softahead.com/download/Business/Databases_and_Tools/Navicat_PostgreSQL-62793.html
http://www.softahead.com/download/Business/Databases_and_Tools/AnySQL_Maestro-63719.html
0
 

Author Comment

by:shaunwingin
ID: 22685064
Teh operating system is Linux CentOS 5.1
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22685136
sorry.. I gave up linux in 95, (thats was a slackware 2!), so I won't be able to tell you the tricks for this OS.
but there should be a verbose mode for the postgre daemon which reports the startup problem.
0
 

Author Comment

by:shaunwingin
ID: 22685156
thanks will look
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22685170
any log file??
0
 

Author Comment

by:shaunwingin
ID: 22685703
Nothing being logged by postgre... can't see anything in firewall that may block it.
0
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22691802
The psql line suggested above will connect via local sockets. If you use psql -d a2billing -U shaunw -h 127.0.0.1 -p 5434 that will force connection via IP, and should (in principle) give the same results as you have had from PHP - does it?
0
 

Author Comment

by:shaunwingin
ID: 22694849
It works fine when I connect with psql -d a2billing -U shaunw -h 127.0.0.1 -p 5434
It would therefore seem to be an issue with the A2Billing.

Still getting the php error:

PHP Warning:  pg_connect() [<a href='function.pg-connect'>function.pg-connect</a>]: Unable to connect to PostgreSQL server: FATAL:  no pg_hba.conf entry for host &quot;127.0.0.1&quot;, user &quot;shaunw&quot;, database &quot;a2billing&quot;, SSL off in /home/shaunw/html/A2Billing_UI/lib/adodb/drivers/adodb-postgres64.inc.php on line 686, referer: http://www.xxx.xxx/index.php


Any idea how to debug the php futher
0
 
LVL 2

Expert Comment

by:DrCabbage
ID: 22694974
You're correct - it is an issue with A2billing. It is connecting to port 5432 and ignoring the "port" config parameter. The DB Conection in A2Billing is set up around line 2615 of common/lib/Class.A2Billing.php:

       if ($this->config['database']['dbtype'] == "postgres"){
          $datasource = 'pgsql://'.$this->config['database']['user'].':'.$this->config['database']['password'].'@'.$this->config['database']['hostname'].'/'.$this->config['database']['dbname'];

Adodb doesn't make it easy to pass in the port. I'll have a look and see if there's an obvious change to suggest. Otherwise, it may be you;re forced to have the a2billing database on the cluster running behind port 5432.
0
 

Author Comment

by:shaunwingin
ID: 22694986
Thanks, By the way its a 64 Bit CentOS installation and the following file may play a part? A2Billing_UI/lib/adodb/drivers/adodb-postgres64.inc.php
0
 
LVL 2

Accepted Solution

by:
DrCabbage earned 500 total points
ID: 22695191
I think the 64 there is a reference to Postgres v6.4 - but in fact that is the file that actually handles making the connection and setting the parameters appropriately.  If you change the host line in a2billing.conf to read

host=localhost:5434

does that help? I think (from looking at the code) the Postgres driver in ADODB will accept a port specification on the hostname, but I don't have a setup to hand to try it right now.

If that works, I suggest you submit a bug report to a2billing asking them to change the line mentioned above to
$datasource = 'pgsql://'.$this->config['database']['user'].':'.$this->config['database']['password'].'@'.$this->config['database']['hostname'].':'.$this->config['database']['port'].'/'.$this->config['database']['dbname'];

... as a permanent fix for the issue. Their code already defaults the port to 5432 if not specified in the config file.
0
 

Author Comment

by:shaunwingin
ID: 22695964
Thank you very much that worked.

Just a small typo 'host=localhost:5434' --> hostname=localhost:5434

Will submit bug. I'm very impressed at the simplicity of your solution.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now