Operating System and Software Versions

  • Operating System: - Ubuntu 18.04 Bionic Beaver
  • Software: - PostgreSQL Server 10


Install PostreSQL Client on Ubuntu


In case that you only need to connect to a remote PostreSQL server you only need to install PostgreSQL client on your local Ubuntu host. To do so execute:

$ sudo apt install postgresql-client


Once the installation of PostreSQl client is completed you can use 
psql command to connect to your remote PostreSQL server. For example, the following Linux command will connect to PostgreSQL server via hostname postresql-ubuntu as user postgres:

$ psql -h postresql-ubuntu -U postgres
psql (10.2 (Ubuntu 10.2-1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

See below on how to configure the PostreSQL server to accept remote client connections.

Install PostreSQL Server on Ubuntu


In this section we will be installing PostgreSQL server on Ubuntu 18.04 Linux. The installation is as simple as:

$ sudo apt install postgresql


Once the PostreSQL installation is finished confirm that it is up and running as expected by checking for its listening socket on port number 
5432:

$ ss -nlt
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128       0.0.0.0:22                    0.0.0.0:*    
LISTEN      0      5       127.0.0.1:631                   0.0.0.0:*      
LISTEN      0      128     127.0.0.1:5432                  0.0.0.0:*   
LISTEN      0      128          [::]:22                       [::]:*   
LISTEN      0      5           [::1]:631                      [::]:*    
 

The PostgreSQL server will start after reboot. To manipulate this default behavior you can either disable or enable the PostreSQL start after reboot by:

$ sudo systemctl disable postgresql
OR
$ sudo systemctl enable postgresql


By default, the PostgreSQL server will listen only on a local loopback interface 
127.0.0.1. If you need to configure your PostreSQL server to listen on all networks you will need to configure its main configuration file /etc/postgresql/10/main/postgresql.conf:

$ sudo nano /etc/postgresql/10/main/postgresql.conf

and add the following line somewhere to the CONNECTIONS AND AUTHENTICATION section:

listen_addresses = '*'


Once the configuration is completed restart PostreSQL server:

$ sudo service postgresql restart


The PostreSQL server should be now listening on socket 
0.0.0.0:5432. You can confirm this by executing the ss command:

$ ss -nlt
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128       0.0.0.0:22                    0.0.0.0:*    
LISTEN      0      5       127.0.0.1:631                   0.0.0.0:*      
LISTEN      0      128     0.0.0.0:5432                    0.0.0.0:*   
LISTEN      0      128          [::]:22                       [::]:*   
LISTEN      0      5           [::1]:631                      [::]:*    
 

Next, to accept connections from a remote PostreSQL client to all databases and all users add the following line to /etc/postgresql/10/main/pg_hba.conf

host    all          all            0.0.0.0/0  trust


Failing to do so may result in the following error message:

psql: FATAL:  no pg_hba.conf entry for host "postresql-client", user "postgres", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "postresql-client", user "postgres", database "postgres", SSL off


Lastly, if you have UFW firewall enabled you can open the PostreSQL's port 
5432 to any TCP incoming traffic by executing the below command:

$ sudo ufw allow from any to any port 5432 proto tcp
Rule added
Rule added (v6)

Done !!


For any technical support, 
 hire our expert.



Was this answer helpful? 0 Users Found This Useful (0 Votes)