We will use a CentOS server to install PostgreSQL. The below command installs the PostgreSQL server.

[root@SHIJU-TEST-PGSQL-01 ~]# dnf install postgresql15-server -y

[root@SHIJU-TEST-PGSQL-01 ~]# postgresql-setup initdb

[root@SHIJU-TEST-PGSQL-01 ~]# systemctl start postgresql

[root@SHIJU-TEST-PGSQL-01 ~]# systemctl status postgresql

[root@SHIJU-TEST-PGSQL-01 ~]# systemctl enable postgresql

The PostgreSQL listens on the localhost by default. If other servers such as application servers connect remotely to this DB server, we need to modify the below configuration file.

Add the below line at the end of the file “/var/lib/pgsql/data/pg_hba.conf” after keeping a copy of the original file elsewhere:

host all all 0.0.0.0/0 md5

[root@SHIJU-TEST-PGSQL-01 ~]# systemctl restart postgresql

Now a new user postgres will be automatically created

[root@SHIJU-TEST-PGSQL-01 ~]# cat /etc/passwd | grep postgres
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

Now, let us access the newly created BD service and get to the postgreSQL prompt:

[root@SHIJU-TEST-PGSQL-01 ~]# su – postgres
Last login: Sun Aug 25 09:24:00 UTC 2024 on pts/3

[postgres@SHIJU-TEST-PGSQL-01 ~]$ psql

====== ===
psql (15.8)
Type “help” for help.
postgres=#
====== ===

The command to list all existing databases in the server is “\l” as follows:

postgres=# \l
====== ====
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
———–+———-+———-+———+———+————+—————–+———————–
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)

postgres=#
====== ====

Now let us assign/reset DB root user password:

postgres=# \password
===== ===
Enter new password for user “postgres”: **********
Enter it again: **********
postgres=#
===== ===