Archives November 11, 2022

INSTALL POSTGRESQL IN LINUX

Check the linux distribution for the postgresql to be installed

[root@DBZX21 ~]# uname -a
Linux DBZX21 5.4.17-2136.304.4.1.el8uek.x86_64 #2 SMP Tue Feb 8 11:54:24 PST 2022 x86_64 x86_64 x86_64 GNU/Linux

[root@DBZX21 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.5 (Ootpa)

Based on the distribution type, use the package installation utility to install postgresql

[root@DBZX21 kish]# yum install postgresql* -y

Initialize the postgre libraries using postgresql-setup

[postgres@DBZX21 ~]$ /usr/bin/postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Enable the postgresql service and start the service using systemctl utility

[postgres@DBZX21 ~]$ systemctl enable postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-unit-files ====
Authentication is required to manage system service or unit files.
Authenticating as: kishan
Password:
==== AUTHENTICATION COMPLETE ====
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon ====
Authentication is required to reload the systemd state.
Authenticating as: kishan
Password:
==== AUTHENTICATION COMPLETE ====
[postgres@DBZX21 ~]$ systemctl start postgresql
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ====
Authentication is required to start 'postgresql.service'.
Authenticating as: kishan
Password:
==== AUTHENTICATION COMPLETE ====

[postgres@DBZX21 ~]$ 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 2022-11-08 21:24:36 IST; 35s ago
  Process: 70150 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
 Main PID: 70152 (postmaster)
    Tasks: 8 (limit: 35835)
   Memory: 15.9M
   CGroup: /system.slice/postgresql.service
           ├─70152 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─70154 postgres: logger process
           ├─70156 postgres: checkpointer process
           ├─70157 postgres: writer process
           ├─70158 postgres: wal writer process
           ├─70159 postgres: autovacuum launcher process
           ├─70160 postgres: stats collector process
           └─70161 postgres: bgworker: logical replication launcher

Check the process related to postgre which are up to validate the status

[postgres@DBZX21 ~]$ ps -ef|grep postgre|grep -Ev 'su|grep|ps|bash'
postgres   70063   70062  0 21:24 pts/0    00:00:00 /usr/libexec/pk-command-not-found [postgres@DBZX21 ~]$ /usr/bin/postgresql-setup --initdb
postgres   70152       1  0 21:24 ?        00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres   70154   70152  0 21:24 ?        00:00:00 postgres: logger process
postgres   70156   70152  0 21:24 ?        00:00:00 postgres: checkpointer process
postgres   70157   70152  0 21:24 ?        00:00:00 postgres: writer process
postgres   70158   70152  0 21:24 ?        00:00:00 postgres: wal writer process
postgres   70159   70152  0 21:24 ?        00:00:00 postgres: autovacuum launcher process
postgres   70160   70152  0 21:24 ?        00:00:00 postgres: stats collector process
postgres   70161   70152  0 21:24 ?        00:00:00 postgres: bgworker: logical replication launcher

Use psql to login to the command line

List all the current databases

[postgres@DBZX21 ~]$ psql
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------
-------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7319 kB | pg_default | default administrative connection da
tabase
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7185 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7185 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(3 rows)

Use help to print all the DDL commands that can be used

postgres=# \h
Available help:
  ABORT                            CLUSTER                          CREATE VIEW                      DROP USER MAPPING
  ALTER AGGREGATE                  COMMENT                          DEALLOCATE                       DROP VIEW
  ALTER COLLATION                  COMMIT                           DECLARE                          END
  ALTER CONVERSION                 COMMIT PREPARED                  DELETE

That is it, postgresql is installed successfully!