Unable to connect to server: 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?

When trying to connect to a new PostgreSQL database, the below 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?

1) 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

2) From client to telnet:

C:\Users\james huang>telnet 192.168.78.51 5432
Connecting To 192.168.78.51...Could not open connection to the host, on port 5432: Connect failed

3) From server to 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

4)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
...
..
.

5) 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)
...
..
.

6) Stop and Start PostgreSQL server again:

$systemctl status postgresql-9.6

$systemctl stop postgresql-9.6

$systemctl start postgresql-9.6

7) Connect to 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)
Advertisements
This entry was posted in Client and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s