Install and Configure PostgreSQL KodeKloud Engineer Task Success

Question :  The Nautilus application development team has shared that they are planning to deploy one newly developed application on Nautilus infra in Stratos DC. The application uses PostgreSQL database, so as a pre-requisite we need to set up PostgreSQL database server as per requirements shared below:

a. Install and configure PostgreSQL database on Nautilus database server.

b. Create a database user kodekloud_top and set its password to TmPcZjtRQx.

c. Create a database kodekloud_db7 and grant full permissions to user kodekloud_top on this database.

d. Make appropriate settings to allow all local clients (local socket connections) to connect to the kodekloud_db7 database through kodekloud_top user using md5 method (Please do not try to encrypt password with md5sum).

e. At the end its good to test the db connection using these new credentials from root user or server's sudo user.

 Please Note :-  Perform the below commands based on your question server,  user name & other details that might differ. So please read the task carefully before executing it. All the Best 👍

              

Solution:  

1. At first login  to the database server & Switch to the root user 

thor@jump_host ~$ ssh peter@stdb01 The authenticity of host 'stdb01 (172.16.239.10)' can't be established. ECDSA key fingerprint is SHA256:zDA/CN3Vd9Skra7ULlCYNczXHbmKHmGPGugYdhg6yqs. ECDSA key fingerprint is MD5:63:2d:65:40:88:f2:71:67:b8:55:32:20:ab:4c:76:93. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'stdb01,172.16.239.10' (ECDSA) to the list of known hosts. peter@stdb01's password: [peter@stdb01 ~]$ sudo su - We trust you have received the usual lecture from the local System Administrator. It usually boils down to these three things:     #1) Respect the privacy of others.     #2) Think before you type.     #3) With great power comes great responsibility. [sudo] password for peter: [root@stdb01 ~]#

2. Install PostgreSQL 

[root@stdb01 ~]# yum -y install postgresql-server postgresql-contrib Loaded plugins: fastestmirror, ovl Determining fastest mirrors  * base: mirrors.advancedhosters.com  * extras: ftpmirror.your.org  * updates: mirror.rackspace.com base                                                                                                                                             | 3.6 kB  00:00:00     extras                                                                                                                                           | 2.9 kB  00:00:00     updates                                                                                                                                          | 2.9 kB  00:00:00     (1/4): base/7/x86_64/group_gz                                                                                                                    | 153 kB  00:00:00     (2/4): extras/7/x86_64/primary_db                                                                                                                | 242 kB  00:00:00     (3/4): base/7/x86_64/primary_db                                                                                                                  | 6.1 MB  00:00:01     (4/4): updates/7/x86_64/primary_db                                                                                                               | 9.6 MB  00:00:01     Resolving Dependencies --> Running transaction check ---> Package postgresql-contrib.x86_64 0:9.2.24-7.el7_9 will be installed --> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-7.el7_9 for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: postgresql(x86-64) = 9.2.24-7.el7_9 for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.22)(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18)(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11)(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 --> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.24-7.el7_9.x86_64 ---> Package postgresql-server.x86_64 0:9.2.24-7.el7_9 will be installed   Installed:   postgresql-contrib.x86_64 0:9.2.24-7.el7_9                                          postgresql-server.x86_64 0:9.2.24-7.el7_9                                           Dependency Updated:   systemd.x86_64 0:219-78.el7_9.3                                                  systemd-libs.x86_64 0:219-78.el7_9.3                                                    Complete! [root@stdb01 ~]#

 3. Post installation Initiate DB setup   

[root@stdb01 ~]#  postgresql-setup initdb Initializing database ... OK  [root@stdb01 ~]#

4. Enable, start & check the status of the service 

[root@stdb01 ~]# systemctl enable postgresql Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service. [root@stdb01 ~]# [root@stdb01 ~]# systemctl start postgresql [root@stdb01 ~]# [root@stdb01 ~]# systemctl status postgresql ● postgresql.service - PostgreSQL database server    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)    Active: active (running) since Tue 2021-08-10 11:10:13 UTC; 5s ago   Process: 1468 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)   Process: 1463 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)  Main PID: 1470 (postgres)    CGroup: /docker/373cc6d4839d10f6b357683f376515eb95ec4bc14f75141009a4c85f7a89cd60/system.slice/postgresql.service            ├─1470 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432            ├─1471 postgres: logger process              ├─1473 postgres: checkpointer process              ├─1474 postgres: writer process              ├─1475 postgres: wal writer process              ├─1476 postgres: autovacuum launcher process              └─1477 postgres: stats collector process     Aug 10 11:10:12 stdb01.stratos.xfusioncorp.com systemd[1]: Starting PostgreSQL database server... Aug 10 11:10:12 stdb01.stratos.xfusioncorp.com pg_ctl[1468]: LOG:  could not bind IPv6 socket: Cannot assign requested address Aug 10 11:10:12 stdb01.stratos.xfusioncorp.com pg_ctl[1468]: HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry. Aug 10 11:10:13 stdb01.stratos.xfusioncorp.com systemd[1]: Started PostgreSQL database server. [root@stdb01 ~]#

 5.  Now login to the Postgres  & Create user, database, and grant permission

    (refer below video for more details)

  CREATE USER <user_asper_your task> WITH PASSWORD '<password_asper_your task>';

 CREATE DATABASE <DB_asper_your task>;

  GRANT ALL PRIVILEGES ON DATABASE "<DB_asper_your task>" to <user_asper_your task>;  

[root@stdb01 ~]# sudo -u postgres psql could not change directory to "/root" psql (9.2.24) Type "help" for help.   postgres=# CREATE USER kodekloud_top WITH PASSWORD 'TmPcZjtRQx'; CREATE ROLE postgres=# CREATE DATABASE kodekloud_db7; CREATE DATABASE postgres=# GRANT ALL PRIVILEGES ON DATABASE "kodekloud_db7" to kodekloud_top; GRANT postgres=# \q [root@stdb01 ~]#

6.Edit postgresql.conf  &  do the below configuration changes uncomment 

[root@stdb01 ~]# vi /var/lib/pgsql/data/postgresql.conf [root@stdb01 ~]# cat /var/lib/pgsql/data/postgresql.conf |grep localhost listen_addresses = 'localhost'          # what IP address(es) to listen on;                                         # defaults to 'localhost'; use '*' for all [root@stdb01 ~]#

7. Edit another config file pg_hba.conf  &  configure the md5 at bottom of the config 

[root@stdb01 ~]# vi /var/lib/pgsql/data/pg_hba.conf [root@stdb01 ~]# cat /var/lib/pgsql/data/pg_hba.conf |grep md5 # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", # "password" sends passwords in clear text; "md5" is preferred since local   all             all                                             md5 host    all             all             127.0.0.1/32            md5 host    all             all             ::1/128                    md5 [root@stdb01 ~]#

 8. Restart SQL service and check the status

[root@stdb01 ~]# systemctl restart postgresql [root@stdb01 ~]# systemctl status postgresql ● postgresql.service - PostgreSQL database server    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)    Active: active (running) since Tue 2021-08-10 11:19:27 UTC; 5s ago   Process: 1516 ExecStop=/usr/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)   Process: 1522 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)   Process: 1517 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)  Main PID: 1470 (code=exited, status=0/SUCCESS)    CGroup: /docker/373cc6d4839d10f6b357683f376515eb95ec4bc14f75141009a4c85f7a89cd60/system.slice/postgresql.service            ├─1524 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432            ├─1525 postgres: logger process              ├─1527 postgres: checkpointer process              ├─1528 postgres: writer process              ├─1529 postgres: wal writer process              ├─1530 postgres: autovacuum launcher process              └─1531 postgres: stats collector process     Aug 10 11:19:26 stdb01.stratos.xfusioncorp.com systemd[1]: Starting PostgreSQL database server... Aug 10 11:19:26 stdb01.stratos.xfusioncorp.com pg_ctl[1522]: LOG:  could not bind IPv6 socket: Cannot assign requested address Aug 10 11:19:26 stdb01.stratos.xfusioncorp.com pg_ctl[1522]: HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry. Aug 10 11:19:27 stdb01.stratos.xfusioncorp.com systemd[1]: Started PostgreSQL database server. [root@stdb01 ~]#

9.  Validate the task by below commands

psql -U <user_asper_your _task> -d <DB_asper_your_task> -h 127.0.0.1 -W

psql -U <user_asper_your _task> -d <DB_asper_your_task> -h localhost -W

root@stdb01 ~]# psql -U kodekloud_top -d kodekloud_db7 -h 127.0.0.1 -W Password for user kodekloud_top: psql (9.2.24) Type "help" for help. kodekloud_db7=> \q [root@stdb01 ~]# [root@stdb01 ~]# psql -U kodekloud_top -d kodekloud_db7 -h localhost -W Password for user kodekloud_top: psql (9.2.24) Type "help" for help. kodekloud_db7=>

10.  Click on Finish & Confirm to complete the task successful

Happy Learning!!!!

Apart from this if you need more clarity,  I have made a  tutorial video on this, please go through and share your comments. Like and share the knowledge