CI, Php 5, Postgre: DB connection issues

[eluser]Vadtec[/eluser]
Debian: Linux charon 2.6.26-1-amd64 #1 SMP Fri Mar 13 17:46:45 UTC 2009 x86_64 GNU/Linux
CI version: 1.7.2

PHP Version 5.2.12-0.dotdeb.1

PDO drivers: mysql, pgsql, sqlite, sqlite2
PDO Driver for PostgreSQL: enabled
PostgreSQL(libpq) Version: 8.3.9
Module version: 1.0.2
Revision: $Id: pdo_pgsql.c 289287 2009-10-07 17:40:16Z mbeccati $

PostgreSQL Support: enabled
PostgreSQL(libpq) Version: 8.3.9
Multibyte character support: enabled
SSL support: enabled
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

I had a website that was running as of Feb this year. However, when I went back to start working on it again, I kept getting:

"A Database Error Occurred

Unable to connect to your database server using the provided settings."

After trying everything under the sun, I uploaded a fresh copy of CI all on its own to the web server. I was able to get the default welcome page to display nicely. However, as soon as I plugged in the database information and auto loaded the database module, CI errored out again. I then tried manually loading the database module in the welcome.php controller, and received the same results.

Wondering if the DB driver was even trying to connect to postgre (which I can verify is working via both CLI and phppgadmin), I fired up tcpdump. Much to my surprise, neither the web server nor the database server are seeing any packets that would be bound for postgre. I know it's not a fire walling issue on either box, and I am capturing all packets for the assigned IP(s), not just certain ports. I have tried capturing on the ports as well, and still see no packets.

For the sake of security, I have removed sensetive sections of the following config:

$db['default']['hostname'] = "<I use the IP here>";
$db['default']['port'] = 5432;
$db['default']['username'] = "someuser";
$db['default']['password'] = "thatpass";
$db['default']['database'] = "thisdatabase";
$db['default']['dbdriver'] = "postgre";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

As I said, I can verify that the log in information for the database *is* correct and works properly. I have tried setting pconnect to both true and false.

No matter what I have tried, I see *zero* packets even attempt to be sent. I have verified that apache is working, as is php. The drivers are loaded and accessible (as you can see from the paste above).

I am at a loss. I suspect that when the box was upgraded something broke, but CI does little to actually help figure out what is broken. Any help is greatly appreciated.

- Vadtec

[eluser]kea13[/eluser]
Hi Vadtec,

a couple of pointers:
- did you verify that PostgreSQL is actually running? ("service postgresql status")
- if PostgreSQL is running, is it configured to listen on a tcp-socket? ("netstat -ntap|grep 5432")
- if PostgreSQL is running and configured to listen on a tcp-socket, do you have access to the database with the credentials given? ("cat /var/lib/pgsql/data/pg_hba.conf")

Regards,
Roman

[eluser]Vadtec[/eluser]
[quote author="Vadtec" date="1270865984"]
...snip...

Wondering if the DB driver was even trying to connect to postgre (which I can verify is working via both CLI and phppgadmin), I fired up tcpdump. Much to my surprise, neither the web server nor the database server are seeing any packets that would be bound for postgre. I know it's not a fire walling issue on either box, and I am capturing all packets for the assigned IP(s), not just certain ports. I have tried capturing on the ports as well, and still see no packets.

...snip...

No matter what I have tried, I see *zero* packets even attempt to be sent. I have verified that apache is working, as is php. The drivers are loaded and accessible (as you can see from the paste above).

...snip

- Vadtec[/quote]

[quote author="kea13" date="1271005951"]Hi Vadtec,

a couple of pointers:
- did you verify that PostgreSQL is actually running? ("service postgresql status")
- if PostgreSQL is running, is it configured to listen on a tcp-socket? ("netstat -ntap|grep 5432")
- if PostgreSQL is running and configured to listen on a tcp-socket, do you have access to the database with the credentials given? ("cat /var/lib/pgsql/data/pg_hba.conf")

Regards,
Roman[/quote]

I think I made it very clear above that postgre is running and functioning as expected. I also thought it was clear that I was seeing zero packets coming from the web server to the postgre port (which, yes i know, is 5432). In fact, here...

Code:

[***@<DB server> pg_log]# pwd
/var/lib/pgsql/data/pg_log
[***@<DB server> pg_log]# service postgre status
postmaster (pid 30796 30795 30794 30792 30790 6238 6226 4988 4633 4632 4631) is running...
[***@<DB server> pg_log]# note the file name, and the date it gives for when i shutdown/restarted the db server
[***@<DB server> pg_log]# cat postgresql-Fri.log
LOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating connection due to administrator command
LOG:  shutting down
LOG:  database system is shut down
LOG:  logger shutting down
LOG:  database system was shut down at 2010-04-09 13:38:14 EDT
LOG:  checkpoint record is at 0/B98DB90
LOG:  redo record is at 0/B98DB90; undo record is at 0/0; shutdown TRUE

!!!This is where the database is restarted.!!!

LOG:  next transaction ID: 492679; next OID: 18379
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484148, limited by database "***"
[***@<DB server> pg_log]# netstat -ntap|grep 5432
tcp        0      0 <IP removed>:5432          0.0.0.0:*                   LISTEN      30790/postmaster    
tcp        0      0 <IP removed>:5432          <IP removed>:59948         ESTABLISHED 4633/postgres: <DB user name removed>
tcp        0      0 <IP removed>:5432          <IP removed>:59947         ESTABLISHED 4632/postgres: <DB user name removed>
tcp        0      0 <IP removed>:5432          <IP removed>:59946         ESTABLISHED 4631/postgres: <DB user name removed>
tcp        0      0 <IP removed>:5432          <IP removed>:43445         ESTABLISHED 6226/postgres: <DB user name removed>
tcp        0      0 <IP removed>:5432          <IP removed>:34799         ESTABLISHED 6238/postgres: <DB user name removed>
tcp        0      0 <IP removed>:5432          <IP removed>:57802         ESTABLISHED 4988/postgres: <DB user name removed>

As you can clearly see, the database server is running just fine. The DB user name that was removed is the exact same user name that cannot connect via CI. If I were to run tcpdump, we would see packets between the two IPs. If I were to run tcpdump on the web server, we would see zero packets coming from the web server to the DB server. I suppose if you don't believe me I could record my screen and show you, but I really do think that is pointless.

I really do appreciate you trying to help, but if you cannot read the information I have given you and deduce that I have more than enough experience running web and database servers to know how to set them up and access them, please don't respond. I need answers, and no amount of code delving into CI has turned up any results. Yes, I delved clear down into the DB driver code for CI, just to verify it wasn't a bug.

So, as I asked before, does anyone have any information as to what is going on? After some speculation with co-workers and associates, it most likely is some incompatibility with CI and the underlying postgre drivers in PHP. That is one thing I have no time to delve into...PHP postgre drivers that is. Any help or info on the issue is greatly appreciated, but please, do try to keep it productive.

- Vadtec

[eluser]Vadtec[/eluser]
The root cause of this issue has been identified and fixed. It was a combination of firewall issues that was ultimately to blame. That and the fact that we were getting no indication of where the failure actually was. There were other contributing factors as well, though they wouldn't have impacted CI in any way.

Thanks for your time.

- Vadtec

[eluser]swmcl[/eluser]
Mr Vadtec,

You made it quite clear that you were a proficient database administrator and that you wanted only productive responses to your question. That's nice.

But now I've got a very similar issue and you haven't actually passed on the information related to the solution.

Were you using the forum and not contributing quite as much as you could've?

I'm sorry but newbies stay as newbies for a longer time than needed because some so-called experts don't help out as much as they could sometimes.