Installing PostgreSQL 13 on CentOS 8

PostgreSQL – a free object-relational database management system. Updates for this branch will be released for five years until November 2025.

Installing PostgreSQL 13

Add PostgreSQL repository

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

Disable the PostgreSQL module in the default AppStream repository

                      
                        $ sudo dnf -qy module disable postgresql
                      
                    

Checking

                      
                        $ sudo dnf module list | grep postgresql
postgresql    9.6 [x]     client, server [d]   PostgreSQL server and client module
postgresql    10 [d][x]   client, server [d]   PostgreSQL server and client module
postgresql    12 [x]      client, server [d]   PostgreSQL server and client module
                      
                    

Install PostgreSQL 13

                      
                        $ sudo dnf -y install postgresql13 postgresql13-server
                      
                    

Initializing the base

                      
                        $ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database … OK
                      
                    

The main PostgreSQL config is located here: /var/lib/pgsql/13/data/postgresql.conf

                      
                        $ ls  /var/lib/pgsql/13/data/
base    pg_commit_ts  pg_ident.conf  pg_notify    pg_snapshots  pg_subtrans  PG_VERSION  postgresql.auto.conf
global  pg_dynshmem   pg_logical     pg_replslot  pg_stat       pg_tblspc    pg_wal      postgresql.conf
log     pg_hba.conf   pg_multixact   pg_serial    pg_stat_tmp   pg_twophase  pg_xact
                      
                    

Launch PostgreSQL and add the service to startup

                      
                        $ sudo systemctl enable --now postgresql-13
                      
                    

Checking the status

                      
                        $ systemctl status postgresql-13
                      
                    

Set a password for the postgres user

                      
                        $ sudo su - postgres 
$ psql -c "alter user postgres with password 'StrongDBPassword'"
ALTER ROLE
$ exit
                      
                    

Working with the base / users

Switching to postgres user

                      
                        $ sudo su - postgres
                      
                    

Create a database user

                      
                        $ createuser userdb
                      
                    

Switching to PostgreSQL shell

                      
                        $ psql
                      
                    

Set a password for the database user

                      
                        =# ALTER USER userdb WITH ENCRYPTED password 'aaayoupasswdaaa';
                      
                    

Create a base and set the owner of the base

                      
                        =# CREATE DATABASE mybase WITH ENCODING='UTF8' OWNER=userdb;
=# q
$ exit
                      
                    

PostgreSQL 13 setup

We configure the ability to connect to the database from another host. To do this, edit the configuration file /var/lib/pgsql/13/data/postgresql.conf and set the server ip-address as the Listen address parameter, or “*” – for all network interfaces

                      
                        $ sudo nano /var/lib/pgsql/13/data/postgresql.conf
[…]
listen_addresses="192.168.11.200"
[…]
                      
                    

Configuring authorization parameters

                      
                        $ sudo nano /var/lib/pgsql/13/data/pg_hba.conf
[…]
# Accept from trusted subnet
host all all 192.168.11.0/24 md5
[…]
                      
                    

Restarting PostgreSQL

                      
                        $ sudo systemctl restart postgresql-13
                      
                    

Testing the connection

                      
                        $ sudo su - postgres
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>
                      
                    

Firewall configuration

Opening port 5432

                      
                        $ sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
$ sudo firewall-cmd --reload
                      
                    

Related Posts