Installing PostgreSQL 14.1 on CentOS 8 / Red Hat 8

Install Postgres 14

1. Fix repository in the mirror list

cd /etc/yum.repos.d/
sudo sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-*
sudo sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-*

2. Actualization of packages

sudo yum update -y

3. Apply again step 1 to put back the fix.

4. Add the PostgreSQL 14 repository.

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

5. The default built-in PostgreSQL module might lead to unwanted conflicts, so make sure it is disabled.

sudo dnf -qy module disable postgresql

6. Install and init database.

sudo dnf install -y postgresql14-server

7. Check packages version upon successful installation.

RHEL$ rpm -qi postgresql14-server postgresql14
Name        : postgresql14-server
Version     : 14.1

8. Initialize DB

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

9. Start and enable database service.

sudo systemctl enable --now postgresql-14
sudo systemctl start postgresql-14

10. Check status.

systemctl status postgresql-14

Allow connection from localhost to database over password

1. Edit file /var/lib/pgsql/14.1/data/pg_hba.conf and change:

host    all             all             127.0.0.1/32            ident

To:

host    all             all             127.0.0.1/32            md5

2. Edit file /var/lib/pgsql/14.1/data/postgres.conf. Locate password_encryption variable and set it to md5.:

password_encryption = md5

3. Restart service.

sudo systemctl restart postgresql-14

Set password for Postgres user and login

1. Switch to postgres user account.

sudo su - postgres

2. Access psql shell.

-bash-4.2$ psql
psql (14.1)
Type "help" for help.

postgres=#

3. Set a password for a Postgres user. By default, is in Newired installer password newired.

postgres=# alter user postgres with password 'newired';
ALTER ROLE

4. Logout from the shell.

postgres=# \q
-bash-4.2$ logout

5. Test connection superuser (postgres) over password.

psql -h 127.0.0.1 -U postgres -W