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(dot)klaver(at)aklaver(dot)com