PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a database system with robustness, extensibility and the one which complies to technical standards.
While we decided to try it out, at many points we faced small issues to get it up and running, mainly the issues were around misconfigurations being done during installation.
So, lets walk through the steps and configurations step by step, so that we can install Postgres and interact with it from an external application.
Step 1: Add Postgresql repository
To start with, first lets add the Postgres repository to the system using :
sudo add-apt-repository “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main”
wget -q -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
This will return an output : OK
Step 2: Install Postgresql 9.6
Use the following commands to install Postgres packages :
sudo apt-get update
sudo apt-get install postgresql-9.6 postgresql-contrib
Verification — Using psql shell (Optional)
Run the following command to verify if Postgres installation was successful.
sudo -u postgres psql postgres
This should take us to postgres interactive terminal :
Step 3: Create Database and User:
Run below commands through psql client to create the database and user
# CREATE DATABASE nodedb;
# CREATE USER adminuser WITH PASSWORD ‘adminpassword’;
Exit from psql client using \q, and then
Exit from postgres user using exit
Step 4: Configure PostgreSQL to allow remote connection:
i. Configuring postgresql.conf:
Uncomment listen_addresses and change its value from ‘localhost’ to ‘*’
Port number should be 5432. If not, then update go to Step 5: Restart Postgres
port = 5432 # (change requires restart)
ii. Configuring pg_hba.conf:
Update client authentication configuration
open pg_hba.conf and add following entry at the very end.
host all all 0.0.0.0/0 md5
host all all ::/0 md5
Adding wildcards , these should be replaced with tighter controls.
Step 5: Restart Postgres
Restart progress using :
sudo service postgresql restart
Verify the number of services which are running. Only one should be running
If there are two then remove postgresql-11 and postgresql-client-11
Now restart PostgreSQL. And repeat Step 3: Create Database and User
To check if configuration is done properly execute :
psql -h <ip> -U adminuser -d nodedb -p 5432
This completes Postrgres installation with which external applications, systems, virtual machine can connect .