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

On 25-08-2019 02:54, Adrian Klaver wrote:
> 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).

You can use strace or tcpdump to see what's happened. First check to see
did the balncer is up and port is opened. Next if the balancer is bind
on port 5432 and pgsql server is started on same ip without port to be
changed this can't be happened unless the pgsql server is started on
socket without inet connection. If the balancer is up and they are not
in the same server then it's seems the problem is between balancer and
sql server. The balancer is not connected to pg server with some reason
/firewall, permissions, users and so on, you need to check them -
balancer logs, firewall rules /.

Regards,
HS