Solved

a2billing cannot connect to postgre database

Posted on 2008-10-06
28
1,259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have a server on collocation with the super-fast CPU, that doesn't mean that you get it running at full power. Here is a preamble. When doing inventory of Linux servers, that I'm administering, I've found that some of them are running on l…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Suggested Courses

738 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