Can't connect to Postgresql

Question

There are exaclty similar questions but nothing works for me.

I have a postgres installed on a server running Ubuntu Server 16.04.

In the postgresql.conf I have set:

listen_addresses = '*'                                          
port = 5432

The status of postgres gives:

● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2019-06-19 09:30:25 UTC; 3min 24s ago Process: 23683 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 23683 (code=exited, status=0/SUCCESS)

Jun 19 09:30:25 ip-172-31-43-128 systemd[1]: Starting PostgreSQL RDBMS... Jun 19 09:30:25 ip-172-31-43-128 systemd[1]: Started PostgreSQL RDBMS.

I have setup postgresql in streaming replication mode with 3 slaves.

My pg_hba.conf file looks like this:

 # Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

host    all             all             172.31.43.128/24        md5
host    replication     all             172.31.32.119/32        trust   # the slave1
host    replication     all             172.31.40.107/32        trust   # the slave2
host    replication     all             172.31.44.8/32          trust   # the slave3

host    postgres        pgpool          172.31.33.203/32        trust   # the client
host    postgres        postgres        172.31.33.203/32        trust   # new
host    all             all             172.31.33.203/32        md5     # the client

I try to connect to master instance in python:

import psycopg2
import matplotlib.pyplot as plt
import sys
import time
import pprint

def connect_postgrs():
    try:
        conn = psycopg2.connect("dbname=my_pgpool_db user=postgres password=antonis host=172.31.43.128")  
        print "Connected"
    except psycopg2.Error as e:
        print str(e)

if __name__ == "__main__":
    connect_postgrs()

and I get the error:

could not connect to server: Connection timed out Is the server running on host "172.31.43.128" and accepting TCP/IP connections on port 5432?

command: netstat -nlt gives the following output:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 172.31.43.128:5432      0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN

Does anyone knows what am i doing wrong?

Lucas Lucas · Accepted Answer · 2019-06-19T11:08:08

Have you restarted Postgres after setting listen_addresses = '*'?

Usually, you would see

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

Instead of

tcp 0 0 172.31.43.128:5432 0.0.0.0:* LISTEN

Can't connect to Postgresql

2 Answers