How to Install PostgreSQL, basic server setup &administration command

PostgreSQL -
PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. PostgreSQL runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. This tutorial will give you quick start with PostgreSQL and make you comfortable with PostgreSQL programming.

Prerequisites -

• One Linux machine with sudoers access

Step 1- Installation -

To install PostgreSQL server, run the following command.

$ sudo apt install postgresql

Once the installation is done, Check PostgreSQL service status.

$ sudo systemctl status postgresql

Step 2- Enable service to start at system boot.
To enable service to start at system boot run command below.

$ sudo systemctl enable postgresql
Synchronizing state of postgresql.service with SysV init with /lib/systemd/systemd-sysv-install...
Executing /lib/systemd/systemd-sysv-install enable postgresql

Now PostgreSQL service will start automatically at system boot.

Step 3- Basic server setup - set password for user postgres

To start off, we need to set the password of the PostgreSQL user (role) called "postgres"; we will not be able to access the server externally otherwise. As the local “postgres” Linux user, we are allowed to connect and manipulate the server using the psql command.

$ sudo -u postgres psql postgres

this connects as a role with same name as the local user, i.e. "postgres", to the database called "postgres" (1st argument to psql).
Set a password for the "postgres" database role using the command:

postgres=# \password postgres
Enter new password:  ******
Enter it again:      ******

and give your password when prompted. The password text will be hidden from the console for security purposes.
Type Control+D or \q to exit the posgreSQL prompt.

Step 4- Basic server setup - Allow postgres user access
To allow access for postgre user, we need to modify config file- follow the steps to make changes :

$ sudo vi /etc/postgresql/9.5/main/pg_hba.conf

Modify the following line. your config file should be like below.

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
#local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.

Save and Exit from the file.

Step 5- Restart PostgreSQL service and test postgres user access
To restart postgresql service-

$ sudo systemctl restart postgresql

To test postges user access

$ sudo psql -U postgres -W

Login working successfully with user PostgreSQL

Step 6- List all database
To display all current database details- Use the following command.

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN |
 template0 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

Step 7- Create New Database-

To Create new database use following command.

postgres=# create database MyDB;
CREATE DATABASE

Step 8- Create user & assign permission to a database
Use the following command to create a new user and grant him access to the specific database.
To Create User

postgres=# create  user amar;
CREATE ROLE

Set password for User Name amar

postgres=# alter user amar password 'mypassword';
ALTER ROLE

Grant permission for user amar to database MyDB

postgres=# grant ALL on DATABASE mydb to amar;
GRANT

Step 9- Test User access
In above example, I have created a user "amar"  and given access to "mydb" database.
Let's try to connect with the server

amar@Server1:~$ sudo psql -U amar -W -d mydb
Password for user amar: ******
psql (9.5.12)
Type "help" for help.

mydb=>

Login working successfully for user "amar"

Cheers!!!