psql: could not connect to server: Connection refused

To avoid “Connection refused”, Configuration is required for client to access PostgreSQL database server.

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)

Install PostgreSQL 9.6 on RHEL 7

Use “yum install” to install PostgreSQL.

1)  Go to https://www.postgresql.org/download, and choose your platform accordingly.

PostgreSQL Download  URL Pre-built Binary Package
Pre-built Binary Package

2)  Action on the below steps as per instructions :

PostgreSQL YUM Repository
PostgreSQL YUM Repository
  • Install the repository RPM:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

  • Install the client packages:

yum install postgresql96

  • Optionally install the server packages:

yum install postgresql96-server

  • Optionally initialize the database and enable automatic start:

/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6

[root@racnode1 ~]# cd /tmp
[root@racnode1 tmp]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Loaded plugins: ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
pgdg-redhat96-9.6-3.noarch.rpm | 4.7 kB 00:00:00
Examining /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 pgdg-redhat96 noarch 9.6-3 /pgdg-redhat96-9.6-3.noarch 2.7 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : pgdg-redhat96-9.6-3.noarch 1/1
 Verifying : pgdg-redhat96-9.6-3.noarch 1/1

Installed:
 pgdg-redhat96.noarch 0:9.6-3

Complete!
[root@racnode1 tmp]# yum install postgresql96
Loaded plugins: ulninfo
ksplice-uptrack | 951 B 00:00:00
ol7_UEKR3 | 1.2 kB 00:00:00
ol7_addons | 1.2 kB 00:00:00
ol7_latest | 1.4 kB 00:00:00
pgdg96 | 4.1 kB 00:00:00
(1/2): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:01
(2/2): pgdg96/7Server/x86_64/primary_db | 151 kB 00:00:02
(1/7): ol7_addons/x86_64/primary | 73 kB 00:00:00
(2/7): ol7_addons/x86_64/updateinfo | 38 kB 00:00:00
(3/7): ksplice-uptrack/7Server/x86_64/primary | 2.0 kB 00:00:00
(4/7): ol7_UEKR3/x86_64/updateinfo | 79 kB 00:00:00
(5/7): ol7_latest/x86_64/updateinfo | 1.3 MB 00:00:01
(6/7): ol7_UEKR3/x86_64/primary | 27 MB 00:00:20
(7/7): ol7_latest/x86_64/primary | 26 MB 00:00:20
ksplice-uptrack 7/7
ol7_UEKR3 642/642
ol7_addons 238/238
ol7_latest 19326/19326
Resolving Dependencies
--> Running transaction check
---> Package postgresql96.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql96-libs(x86-64) = 9.6.3-1PGDG.rhel7 for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96 x86_64 9.6.3-1PGDG.rhel7 pgdg96 1.3 M
Installing for dependencies:
 postgresql96-libs x86_64 9.6.3-1PGDG.rhel7 pgdg96 310 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package (+1 Dependent package)

Total download size: 1.6 M
Installed size: 8.0 M

Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm | 310 kB 00:00:03
(2/2): postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm | 1.3 MB 00:00:04
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 415 kB/s | 1.6 MB 00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Installing : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2
 Verifying : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Verifying : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2

Installed:
 postgresql96.x86_64 0:9.6.3-1PGDG.rhel7

Dependency Installed:
 postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# yum install postgresql96-server
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96-server x86_64 9.6.3-1PGDG.rhel7 pgdg96 4.3 M

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total download size: 4.3 M
Installed size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm | 4.3 MB 00:00:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1
 Verifying : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1

Installed:
 postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK

[root@racnode1 tmp]# systemctl enable postgresql-9.6
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
--
-- Start PosgreSQL Server
--
[root@racnode1 tmp]# systemctl start postgresql-9.6
[root@racnode1 tmp]# ps -eaf | grep post
postfix 933 1885 0 15:40 ? 00:00:00 pickup -l -t unix -u
postgres 1259 1 0 15:41 ? 00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres 1262 1259 0 15:41 ? 00:00:00 postgres: logger process
postgres 1264 1259 0 15:41 ? 00:00:00 postgres: checkpointer process
postgres 1265 1259 0 15:41 ? 00:00:00 postgres: writer process
postgres 1266 1259 0 15:41 ? 00:00:00 postgres: wal writer process
postgres 1267 1259 0 15:41 ? 00:00:00 postgres: autovacuum launcher process
postgres 1268 1259 0 15:41 ? 00:00:00 postgres: stats collector process
root 1277 5694 0 15:41 pts/0 00:00:00 grep --color=auto post
root 1885 1 0 12:50 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 1893 1885 0 12:50 ? 00:00:00 qmgr -l -t unix -u
[root@racnode1 tmp]#

[root@racnode1 ~]# su - postgres
Last login: Thu Jul 13 16:06:15 AEST 2017 on pts/0
-bash-4.2$ psql
psql (9.6.3)
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)

postgres=#