My Profile Photo


A Thinker, Innovator, Enthusiastic Software Developer, Amateur sketch maker, Recreational Singer, Thought leader and Sometimes clumsy. :-)

12 - Installing and configuring Postgres

To install postgres on linux machine,

sudo apt-get install postgresql postgresql-contrib

After successful installation, check for

netstat -na | grep ^tcp

This should include below line

tcp 0 0* LISTEN

if it is not showing, then fix it first. Most probably postgres is not installed.

You can login as postgres like this

sudo -i -u postgres

To open postgres console


To check the port it is using and other connection info

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

To quit


Now, lets say we have two machines

machine-S : (server) machine-C : (client)

In both the machine, postgres is installed, but machine-C wants to connect to machine-S, where postgres database is present.

If we try to connect server machine, from client using below command

psql -h -p 5432 -U postgres

It will throw error like this

psql: could not connect to server: Connection refused
        Is the server running on host "" and accepting
        TCP/IP connections on port 5432?

So, to set up the connection between two, we need to follow two steps:

  1. Modify pg_hba.conf to add Client Authentication Record

As we are using host based authentication, we need to change pg_hba.conf file like below

sudo vi /etc/postgresql/9.3/main/pg_hba.conf

append below line

host all all trust

where “” is the ipv4 address of client machine

change the line for localhost like below

host all all trust
  1. Change the Listen Address in postgresql.conf of server machine

a. open postgresql.conf

sudo vi /etc/postgresql/9.3/main/postgresql.conf

b. uncomment listen_address and change the value from ‘localhost’ to ‘*’

Now restart the server

sudo /etc/init.d/postgresql restart

Now client machine can connect to postgres database

root@ psql -h -p 5432 -U postgres
psql (9.3.14, server 9.3.13)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.


Cheers :)