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 