Installing Postgres DB on Ubuntu (Ex: DB version 11.5)

First you need to add the PostgreSQL to your APT repository.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package list

sudo apt update

Installing PostgreSQL 11.5

sudo apt install postgresql-11

Verify the Status

sudo service postgresql status

Connect to the PSQL

sudo -u postgres psql

Sample Screen

Config the postgresql.conf

Located in: (Ubuntu)

sudo vim /etc/postgresql//main/postgresql.conf

Change the list_address = ‘*’

listen_addresses = '*'

Save the file

Update the pg_hba.conf for authentications

Location (Ubuntu):

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

host    all             all             127.0.0.1/32            md5

if you need, you can change above according to the requirement you have. example:

host    all             all             0.0.0.0/0            md5
Allow any network

host    all             all             10.0.0.0/0            md5
Allow networks only start from 10.x.x.x

Then re start the server

sudo service postgresql restart

Change the Password

Initially Postgresql password is set as blank. So, log into the system without a password

sudo -u postgres psql

Change the password

ALTER USER postgres WITH PASSWORD 'your_new_password';

Creating a New Database

sudo -u postgres createdb your_database_name

How to Check Open ports?

sudo netstat -tuln

    -t: Show TCP ports.
    -u: Show UDP ports.
    -l: Display listening ports.
    -n: Show numerical addresses instead of resolving hostnames (faster).

Installing Postgres Client to another Unix machine

sudo apt-get install postgresql-client

psql -h hostname_or_ip -U username -d database_name

How to restore a DB to Postgres from a backup

Change the pg_hba.conf file and update the values as per the following image

Now you can run the restore command as follows:

from the DB Server

pg_restore –dbname=[db name] –username=[user name] –verbose [dump name]

From a Remote server:

pg_restore -h [DB connection] -p 5432 -U [user name] -d [db name] -v [dump name]

Then system will ask for the password. Else, system gives you an error which is even difficult to understand.

Once you update the pg_hba.conf file, we can run the following command as well

psql -U postgres

Every time we did change to the conf file, it is better to restart the server by using the

sudo service postgresql restart

Check the available DB roles and Create a new Role

Check available Roles

SELECT rolname FROM pg_roles WHERE rolname = ‘dmsdb’;

Create a new Role

CREATE ROLE dmsdb;