by On this article we will discuss about PostgreSQL installation on Linux CentOS 8.
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 about PostgreSQL installation on Linux CentOS 8 operating system.
Before we are starting PostgreSQL installation, there are will be prerequisites to be fulfill, as mention below :
- CentOS 8 System with sufficient disk space
- non-root user with administrative privileges
- firewall configured with
firewalld
PostgreSQL installation on CentOS 8
PostgreSQL project provides a repository of packages of all supported versions for the most common Linux distributions. On this tutorial we will be using PostgreSQL Yum Repository for PostgreSQL version 13, the last stable version which was released on 24 September 2020.
The PostgreSQL installaton will be consist of several steps :
- Add PostgreSQL Repository To CentOS 8
- Install PostgreSQL 13 on CentOS 8
- Initialize and start database service
- Set PostgreSQL admin user’s password
- Enable remote access
Add PostgreSQL Repository To CentOS 8
The PostgreSQL Yum Repository will integrate with our normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.
[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
Install PostgreSQL 13 on CentOS 8
[ramans@diginetdb01 ~]$ sudo dnf -qy module disable postgresql
Than install the Postgresql 13 server, by submitting command line : sudo dnf install -y postgresql13-server as shown below :
[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. 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!
Initialize and start database service
After the installation is completed done, than we stat up its services.
[ramans@diginetdb01 ~]$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb Initializing database … OK
[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
Than query the PostgreSQL status by submitting the command line below :
[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>
We also able to verify its version by submitting command line below :
[ramans@diginetdb01 ~]$ sudo -u postgres psql -c "SELECT version();"
The output will be :
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)
If we have implemented the Firewall on database server. then remote clients should connect to our database server, we have to allow PostgreSQL service. The firewall service is running on our side, as firewalld status below :
[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 we enable if the PostgreSQL service is allowed.
[ramans@diginetdb01 ~]$ sudo firewall-cmd --add-service=postgresql --permanent success [ramans@diginetdb01 ~]$ sudo firewall-cmd --reload success [ramans@diginetdb01 ~]$
Set PostgreSQL admin user’s password
For administrative easiness, we have to set the admin user’s password. For this purpose, we have to login to database and setting the password.
ramans@diginetdb01 ~]$ sudo su - postgres [sudo] password for ramans: [postgres@diginetdb01 ~]$ psql psql (13.0) Type "help" for help. postgres=# alter user postgres with password 'Otodiginet2020'; ALTER ROLE postgres=#
Enable remote access
To enable remote access we have to edit the /var/lib/pgsql/13/data/postgresql.conf file.
Just update the listen_address with IP Address where the database is hosted.
CONNECTIONS AND AUTHENTICATION ------------------------------------------------------------------------------ - Connection Settings - listen_addresses = '192.168.19.133' # what IP address(es) to listen on;
And then setting if the PostgreSQL is also accepting remote connections, by editing /var/lib/pgsql/13/data/pg_hba.conf file.
Accept connection from anywhere host all all 0.0.0.0/0 md5
