On this short tutorial, we will learn how to install PostgreSQL 13 on CentOS 8 operating system.
Introduction
Beside MySQL and MariaDB, PostgreSQL (also known as Postgres) is a popular a relational database management system which is used by many popular projects, both large and small and found as a key component of many websites and applications. On this article, we will discuss how to install PostgreSQL on Linux CentOS 8 operating system.
PostgreSQL 13 Installation On CentOS 8
Before we are going to start PostgreSQL 13 installation on CentOS 8, there are several prerequisite to be fulfilled. These prerequisites are so important because it will affect the success or failure of the installation process.
Prerequisites
- CentOS 8 System with sufficient disk space
- non-root user with administrative privileges
- firewall configured with firewalld
The PostgreSQL installation will be consist of several steps :
- Add PostgreSQL Repository To CentOS 8
- Install PostgreSQL 13 on CentOS 8
- Initializing and starting database service
- Set PostgreSQL admin user’s password
- Enable remote access (Optional)
Each of the above items will be explained in more detail in the following sub-chapters.
1. Add PostgreSQL Repository To CentOS 8
The first step installing PostgreSQL 13 database engine on CentOS 8 is to add PostgreSQL repository to the system. By default CentOS 8 has already had PostgreSQL repo, but it will be the older version, than the one one available on the PosrgreSQL official website. To add PostgreSQL 13 repository we will do following command line :
$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Output :
[ramans@diginetdb01 ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm [sudo] password for ramans: Last metadata expiration check: 9:06:11 ago on Thu 05 Nov 2020 06:40:20 AM PST. pgdg-redhat-repo-latest.noarch.rpm 6.4 kB/s | 11 kB 00:01 Dependencies resolved. ============================================================================================ Package Architecture Version Repository Size ============================================================================================ Installing: pgdg-redhat-repo noarch 42.0-14 @commandline 11 k Transaction Summary ============================================================================================ Install 1 Package Total size: 11 k Installed size: 11 k Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : pgdg-redhat-repo-42.0-14.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-14.noarch 1/1 Installed products updated. Installed: pgdg-redhat-repo-42.0-14.noarch Complete!
The PostgreSQL 13 repo has been added on our CentOS 8.
2. Install PostgreSQL 13 on CentOS 8
The next step is to install PostgreSQL 13 on CentOS 8, on this step we need to disable the PostgreSQL AppStream repository on CentOS 8 to prevent other version of PostgreSQL. This task will be done by submitting command line :
$ sudo dnf -qy module disable postgresql
After all are set, then we will begin to install PostgreSQL 13 on CentOS 8 by submitting command line :
$ sudo dnf install -y postgresql13-server
Output :
[ramans@diginetdb01 ~]$ sudo dnf -qy module disable postgresql [ramans@diginetdb01 ~]$ sudo dnf install -y postgresql13-server Last metadata expiration check: 0:00:13 ago on Thu 05 Nov 2020 03:47:32 PM PST. Dependencies resolved. ============================================================================================In general, database users are users who have ip addresses that are outside our postgresql database system. and for security reasons it is sometimes on a very different IP address segment. For cases like this, we need to open access to the database from remote access. Package Architecture Version Repository Size ============================================================================================ Installing: postgresql13-server x86_64 13.0-1PGDG.rhel8 pgdg13 5.5 M Installing dependencies: postgresql13 x86_64 13.0-1PGDG.rhel8 pgdg13 1.4 M postgresql13-libs x86_64 13.0-1PGDG.rhel8 pgdg13 411 k Transaction Summary ============================================================================================ Install 3 Packages Total download size: 7.4 M Installed size: 31 M Downloading Packages: (1/3): postgresql13-libs-13.0-1PGDG.rhel8.x86_64.rpm 162 kB/s | 411 kB 00:02 (2/3): postgresql13-13.0-1PGDG.rhel8.x86_64.rpm 451 kB/s | 1.4 MB 00:03 (3/3): postgresql13-server-13.0-1PGDG.rhel8.x86_64.rpm 454 kB/s | 5.5 MB 00:12 -------------------------------------------------------------------------------------------- Total 601 kB/s | 7.4 MB 00:12 warning: /var/cache/dnf/pgdg13-e81daebfc8b779ec/packages/postgresql13-13.0-1PGDG.rhel8.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY PostgreSQL 13 for RHEL/CentOS 8 - x86_64 1.6 MB/s | 1.7 kB 00:00 Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8 From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Key imported successfully Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : postgresql13-libs-13.0-1PGDG.rhel8.x86_64 1/3 Running scriptlet: postgresql13-libs-13.0-1PGDG.rhel8.x86_64 1/3 Installing : postgresql13-13.0-1PGDG.rhel8.x86_64 2/3 Running scriptlet: postgresql13-13.0-1PGDG.rhel8.x86_64 2/3 Running scriptlet: postgresql13-server-13.0-1PGDG.rhel8.x86_64 3/3 Installing : postgresql13-server-13.0-1PGDG.rhel8.x86_64 3/3 Running scriptlet: postgresql13-server-13.0-1PGDG.rhel8.x86_64 3/3 Verifying : postgresql13-13.0-1PGDG.rhel8.x86_64 1/3 Verifying : postgresql13-libs-13.0-1PGDG.rhel8.x86_64 2/3 Verifying : postgresql13-server-13.0-1PGDG.rhel8.x86_64 3/3 Installed products updated. Installed: postgresql13-13.0-1PGDG.rhel8.x86_64 postgresql13-libs-13.0-1PGDG.rhel8.x86_64 postgresql13-server-13.0-1PGDG.rhel8.x86_64 Complete! [ramans@diginetdb01 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb Initializing database ... OK
3. Initializing and Starting Database Service
Until this step, we have completedly install PostgreSQL database engine on CentOS 8, then we will use this database engine for our requirement. Before it can be done, we have ti initialize PostgreSQL database before we use it. To initialize database, we will use the following command line :
$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Output :
[ramans@diginetdb01 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb Initializing database ... OK
Then we will enable and start PostgreSQL 13 service to server our need as well. To enable and start PostgreSQL 13 database service we will use the following commands line :
$ sudo systemctl enable postgresql-13 $ sudo systemctl start postgresql-13 $ sudo systemctl status postgresql-13
Output :
[ramans@diginetdb01 ~]$ sudo systemctl enable postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usr/lib/systemd/system/postgresql-13.service.
[ramans@diginetdb01 ~]$ sudo systemctl start postgresql-13
[ramans@diginetdb01 ~]$ sudo systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: d>
Active: active (running) since Thu 2020-11-05 15:48:52 PST; 8s ago
Docs: https://www.postgresql.org/docs/13/static/
Process: 3433 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=e>
Main PID: 3439 (postmaster)
Tasks: 8 (limit: 23800)
Memory: 18.1M
CGroup: /system.slice/postgresql-13.service
├─3439 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
├─3440 postgres: logger
├─3442 postgres: checkpointer
├─3443 postgres: background writer
├─3444 postgres: walwriter
├─3445 postgres: autovacuum launcher
├─3446 postgres: stats collector
└─3447 postgres: logical replication launcher
Nov 05 15:48:52 diginetdb01 systemd[1]: Starting PostgreSQL 13 database server...
Nov 05 15:48:52 diginetdb01 postmaster[3439]: 2020-11-05 15:48:52.320 PST [3439] LOG: redi>
Nov 05 15:48:52 diginetdb01 postmaster[3439]: 2020-11-05 15:48:52.320 PST [3439] HINT: Fut>
On this step, we will verify the PostgreSQL 13 by entering command line below :
$ sudo -u postgres psql -c "SELECT version();"
Output :
[ramans@diginetdb01 ~]$ sudo -u postgres psql -c "SELECT version();"
could not change directory to "/home/ramans": Permission denied
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)
4. Setting Up PostgreSQL admin user’s password
For the security reason and strengthen database security we are recommended to change our PostgreSQL admin user’s password. To do this we will enter the following command line from shell script and and enter to sql script and change the password from there.
ramans@diginetdb01 ~]$ sudo su - postgres
[sudo] password for ramans:
[postgres@diginetdb01 ~]$ psql
psql (13.0)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# alter user postgres with password 'Otodiginet2020';
ALTER ROLE
5. Enabling remote access (Optional)
If you have a running Firewall service and remote clients should connect to your database server, allow PostgreSQL service. Then we have to enable posrgresql service on firewalld. We will check if firewalld has been running on our system, by submitting command line :
$ sudo systemctl status firewalld
Output :
[ramans@diginetdb01 ~]$ sudo systemctl status firewalld
[sudo] password for ramans:
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabl>
Active: active (running) since Thu 2020-11-05 15:36:44 PST; 30min ago
Docs: man:firewalld(1)
Main PID: 1088 (firewalld)
Tasks: 2 (limit: 23800)
Memory: 33.6M
CGroup: /system.slice/firewalld.service
└─1088 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid
Nov 05 15:36:44 diginetdb01 systemd[1]: Starting firewalld - dynamic firewall daemon...
Nov 05 15:36:44 diginetdb01 systemd[1]: Started firewalld - dynamic firewall daemon.
Nov 05 15:36:45 diginetdb01 firewalld[1088]: WARNING: AllowZoneDrifting is enabled. This is>
Then weill add postgresql service permanently, by submitting command line :
$ sudo firewall-cmd --add-service=postgresql --permanent $ sudo firewall-cmd --reload
Ouput :
[ramans@diginetdb01 ~]$ sudo firewall-cmd --add-service=postgresql --permanent success [ramans@diginetdb01 ~]$ sudo firewall-cmd --reload success
In general, database users are users who have ip addresses that are outside our database system and for security reasons it is sometimes on a very different IP address segment. For this case, we need to enbale remote access to our database system. To enable remote access we have to modify a spescific configuration file. We have to edit the /var/lib/pgsql/13/data/postgresql.conf file and set listen address to our server IP address or “*” for all interfaces.
ramans@diginetdb01 ~]$ sudo vi /var/lib/pgsql/13/data/postgresql.conf
[sudo] password for ramans:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '192.168.19.133' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
The next step is to set PostgreSQL to accept remote connections, by updating /var/lib/pgsql/13/data/pg_hba.conf file.
[ramans@diginetdb01 ~]$ sudo vi /var/lib/pgsql/13/data/pg_hba.conf TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 # Accept connection from anywhere host all all 0.0.0.0/0 md5
After saving the file, we have to restart database service to take effect after updating configuration file.
Conclusion
So far, until this stage, we have already discussed how to install PostgreSQL 13 database server on CentOS 8 operating system properly. I hope this guide will be helpful for anyone who need it.
