Install PostgreSQL 14.1 on Rocky 8 / Red Hat 9

Modified on Tue, 25 Oct, 2022 at 3:11 PM

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. Actualisation 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

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

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/postgresql.conf. Locate password_encryption variable and set it to md5.:

password_encryption = md5

3.Restart service

4.Test connection superuser (postgres) over password

psql -h 127.0.0.1 -U postgres -W

Troubleshooting:
In case of failed installation of Portal

Try first change encryption to md5 in pg_hba.conf and then change password

Apply md5 encryption to all rows in pg_hba.conf