Re: Postgres SQLSTATE[08006] [7] timeout expired

On 8/24/19 3:42 PM, Howard Wells wrote:
> I have three servers behind a load balancer and a fourth server solely 
> for Postgres 10 database that is not behind the load balancer.  All four 
> are behind the same firewall, with port 5432 open.
> 
> I have a simple browser-based html form to submit email addresses, 
> usernames and passwords; the form submits through a jQuery program that 
> calls a php program.
> 
> I can navigate the browser to the IP address of the server that hosts 
> the Postgres database and submit records to the database with no 
> problems.  When I navigate to the site's URL (which goes through the 
> load balancer), I always get "SQLSTATE[08006] [7] timeout expired" after 
> a short delay -- in other words, no connection.
> 
> The php program is very simple:
> 
> <?php
> echo 'Hello ' . htmlspecialchars($_POST["firstname"]) . '!' ;
> echo PHP_EOL;
> 
> $dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', [
>      'host' => 'xxx.xx.xx.xx',
>      'port' => '5432',
>      'dbname' => '[DBNAME]',
>      'user' => '[USERNAME]',
>      'password' => '[PASSWORD]',
> ]);
> 
> echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
> echo PHP_EOL;
> 
> echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
> echo PHP_EOL;
> 
> try{
> // create a PostgreSQL database connection
> 
>    echo "I'm here now";
>    echo PHP_EOL;
> 
> //ini_set("default_socket_timeout", 300);
> $pdo = new PDO($dsn);
> $pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
> 
> // display a message if connected to the PostgreSQL successfully
> if($pdo){
> echo "Connected to the database successfully!";
> echo PHP_EOL;
> }
> }catch (PDOException $e){
> // report error message
> echo $e->getMessage();
> exit();
> }
> 
> $pdo = null;
> 
> ?>
> 
> The echo messages are simply debugging progress messages for the 
> browser's dev console.
> 
> The pg_hba.conf has these lines enabled:
> 
> pg_hba.conf:
> host    all             [username]   0.0.0.0/0               trust
> host    all             all             0.0.0.0/0               md5
> host    all             all              ::/0                   md5
> host    all             all              all                    md5
> 
> The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always 
> comes back with the address of the load balancer.
> 
> I'm new to Postgres, so any help with this connection problem will be 
> very appreciated.  Thanks a lot.

In addition to what Rob suggested, what do you see or not in the 
Postgres logs when you try the connections(both direct to db and to load 
balancer).



-- 
Adrian Klaver
adrian.klaver@aklaver.com