a2billing cannot connect to postgre database

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
shaunwinginAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DrCabbageConnect With a Mentor Commented:
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
 
DrCabbageCommented:
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
 
shaunwinginAuthor Commented:
Its the correct one, tx.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Steve BinkCommented:
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
 
Steve BinkCommented:
>>> 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
 
Steve BinkCommented:
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
 
shaunwinginAuthor Commented:
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
 
Steve BinkCommented:
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
 
shaunwinginAuthor Commented:
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
 
Steve BinkCommented:
Have you tried explicitly naming the user in pg_hba?
0
 
shaunwinginAuthor Commented:
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
 
lesouefCommented:
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
 
shaunwinginAuthor Commented:
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
 
lesouefCommented:
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
 
shaunwinginAuthor Commented:
The port isn't busy. Can someone using postgre please help.
0
 
lesouefCommented:
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
 
shaunwinginAuthor Commented:
Teh operating system is Linux CentOS 5.1
0
 
lesouefCommented:
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
 
shaunwinginAuthor Commented:
thanks will look
0
 
lesouefCommented:
any log file??
0
 
shaunwinginAuthor Commented:
Nothing being logged by postgre... can't see anything in firewall that may block it.
0
 
DrCabbageCommented:
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
 
shaunwinginAuthor Commented:
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
 
DrCabbageCommented:
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
 
shaunwinginAuthor Commented:
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
 
shaunwinginAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.