When trying to connect to a new PostgreSQL database, the following error occurred:
C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres psql: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "192.168.78.51" and accepting TCP/IP connections on port 5432?
Check postgresql server is up and running
$ systemctl status postgresql-9.6
● postgresql-9.6.service - PostgreSQL 9.6 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2017-07-16 18:08:47 AEST; 15min ago
Process: 1238 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1243 (postmaster)
CGroup: /system.slice/postgresql-9.6.service
├─1243 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
├─1246 postgres: logger process
├─1248 postgres: checkpointer process
├─1249 postgres: writer process
├─1250 postgres: wal writer process
├─1251 postgres: autovacuum launcher process
├─1252 postgres: stats collector process
└─1822 postgres: postgres postgres 192.168.78.1(56572) idle
From client telnet to server
C:\Users\postgres>telnet 192.168.78.51 5432 Connecting To 192.168.78.51...Could not open connection to the host, on port 5432: Connect failed
From server check listener port 5432 not opened on IP 192.168.78.1
$ netstat -ntl|grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN tcp6 0 0 ::1:5432 :::* LISTEN
Allow remote IP address to access PostgreSQL
$ su - postgres
$ vi /var/lib/pgsql/9.6/data/pg_hba.conf
...
..
.
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.78.51/24 md5
...
..
.
Edit /var/lib/pgsql/9.6/data/postgresql.conf
# – Connection Settings –
... .. . listen_addresses = '192.168.78.51,localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) ... .. .
Stop and Start PostgreSQL server
$systemctl status postgresql-9.6 $systemctl stop postgresql-9.6 $systemctl start postgresql-9.6
Connect to PostgreSQL database successfully
C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres -U postgres Password for user postgres: psql (9.6.3) WARNING: Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)
Make DBA Life Easy 