The PostgreSQL database management system has been in active development for over 30 years now. Throughout its journey, it has earned a strong reputation for its reliability, robustness, and performance. PostgreSQL is a fully open source, production grade relational database management system build with love. At the time of updating this article, the latest release of PostgreSQL database is version 14.
In PostgreSQL 14 release, there are tons of improvements and new features. PostgreSQL is a database fit for use in designing and running of distributed workloads that needs logical replication, query parallelism, high-write workloads, connection concurrency, and many advanced features. Below is a highlight on PostgreSQL 14 release features and improvements as shared in the release notes.
- In PostgreSQL 14, stored procedures can return data via OUT parameters.
- Implementation of SQL-standard SEARCH and CYCLE options for common table expressions
- Extension of Range types by adding multiranges, allowing representation of noncontiguous data ranges.
- Updates on the B-tree index are managed more efficiently, reducing index bloat.
- Collection of extended statistics on expressions to allow better planning results for complex queries.
- Numerous performance improvements on parallelism of queries, running of heavily-concurrent workloads, partitioned tables, vacuuming, and logical replication.
- Among many other improvements and new features
Setup requirements
- CentOS 7 / RHEL 7 Linux system
- The machines need access to the internet – directly or via proxy
- Access to the server via ssh or console (as root or user account with sudo)
Step 1: Add PostgreSQL RPM Repository
The PostgreSQL team maintains an RPM repository for RHEL based Linux systems. This repository contains all binary builds of PostgreSQL database server in multiple versions. We’ll add the repository then use it for the installation.
Register RHEL 7 system
You need active RHEL subscription to perform updates on RHEL 7 Linux system. Registration to RHSM can be don with the following command:
$ sudo subscription-manager register --auto-attach
You are attempting to use a locale that is not installed.
Registering to: subscription.rhsm.redhat.com:443/subscription
Username: <INPUT-RHSM-USERNAME>
Password: <INPUT-RHSM-USER-PASSWORD>If the registration is successful, you should get output like below:
The system has been registered with ID: 1ea2349f-f4e1-4e09-8cbd-81de28bf8931
The registered system name is: rhel7-server.novalocal
Installed Product Current Status:
Product Name: Red Hat Enterprise Linux Server
Status: Subscribed
The version of PostgreSQL packages available on CentOS / RHEL 7 are older as seen in the output below:
$ sudo yum info postgresql-server
Failed to set locale, defaulting to C
Loaded plugins: product-id, search-disabled-repos, subscription-manager
Available Packages
Name : postgresql-server
Arch : x86_64
Version : 9.2.24
Release : 7.el7_9
Size : 3.8 M
Repo : rhel-7-server-rpms/7Server/x86_64
Summary : The programs needed to create and run a PostgreSQL server
URL : http://www.postgresql.org/
License : PostgreSQLFrom the output, we can confirm the version available is 9.x
Add PostgreSQL repository
Next we add PostgreSQL repository to our CentOS 7 / RHEL 7 Linux system.
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpmExample output of success in repository addition to our system
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-23 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
======================================================================================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-23 /pgdg-redhat-repo-latest.noarch 12 k
Transaction Summary
======================================================================================================================================================================================================
Install 1 Package
Total size: 12 k
Installed size: 12 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-redhat-repo-42.0-23.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-23.noarch 1/1
rhel-7-server-rpms/7Server/x86_64/productid | 2.1 kB 00:00:00
Installed:
pgdg-redhat-repo.noarch 0:42.0-23
Complete!
List available repositories on the system after adding it
$ sudo yum repolist -y
Failed to set locale, defaulting to C
Loaded plugins: product-id, search-disabled-repos, subscription-manager
repo id repo name status
pgdg-common/7Server/x86_64 PostgreSQL common RPMs for RHEL/CentOS 7Server - x86_64 353
pgdg10/7Server/x86_64 PostgreSQL 10 for RHEL/CentOS 7Server - x86_64 1004
pgdg11/7Server/x86_64 PostgreSQL 11 for RHEL/CentOS 7Server - x86_64 1112
pgdg12/7Server/x86_64 PostgreSQL 12 for RHEL/CentOS 7Server - x86_64 697
pgdg13/7Server/x86_64 PostgreSQL 13 for RHEL/CentOS 7Server - x86_64 445
pgdg14/7Server/x86_64 PostgreSQL 14 for RHEL/CentOS 7Server - x86_64 183
pgdg96/7Server/x86_64 PostgreSQL 9.6 for RHEL/CentOS 7Server - x86_64 960
rhel-7-server-rpms/7Server/x86_64 Red Hat Enterprise Linux 7 Server (RPMs) 32535
repolist: 37289Step 2: Install PostgreSQL 14 CentOS 7 | RHEL 7
After repository has been added, update system and reboot
sudo yum -y update
sudo systemctl rebootWait for the system to come online then login and install PostgreSQL server and client packages
sudo yum install -y postgresql14-server postgresql14Check packages version upon successful installation of PostgreSQL 14 CentOS 7 | RHEL 7
$ rpm -qi postgresql14-server postgresql14
Name : postgresql14-server
Version : 14.8
Release : 1PGDG.rhel7
Architecture: x86_64
Install Date: Thu 25 May 2023 12:41:05 AM UTC
Group : Unspecified
Size : 23398268
License : PostgreSQL
Signature : DSA/SHA1, Wed 10 May 2023 08:33:57 PM UTC, Key ID 1f16d2e1442df0f8
Source RPM : postgresql14-14.8-1PGDG.rhel7.src.rpm
Build Date : Wed 10 May 2023 06:13:30 PM UTC
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://www.postgresql.org/
...Step 3: Initialize PostgreSQL 14 CentOS 7 | RHEL 7
Initialize the PostgreSQL 14 server before use by running the command:
$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OKStart and enable database service
$ sudo systemctl enable --now postgresql-14
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.Service should be in running state after starting it
$ systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2021-12-09 05:37:12 EST; 5s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 7676 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 7685 (postmaster)
CGroup: /system.slice/postgresql-14.service
├─7685 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─7688 postgres: logger
├─7690 postgres: checkpointer
├─7691 postgres: background writer
├─7692 postgres: walwriter
├─7693 postgres: autovacuum launcher
├─7694 postgres: stats collector
└─7695 postgres: logical replication launcher
Dec 09 05:37:12 rhel7-01.novalocal systemd[1]: Starting PostgreSQL 14 database server...
Dec 09 05:37:12 rhel7-01.novalocal postmaster[7685]: 2021-12-09 05:37:12.927 EST [7685] LOG: redirecting log output to logging collector process
Dec 09 05:37:12 rhel7-01.novalocal postmaster[7685]: 2021-12-09 05:37:12.927 EST [7685] HINT: Future log output will appear in directory "log".
Dec 09 05:37:12 rhel7-01.novalocal systemd[1]: Started PostgreSQL 14 database server.Step 4: Set strong password for Postgres user and login
Switch to postgres user account.
sudo su - postgresAccess psql shell:
-bash-4.2$ psql
psql (14.1)
Type "help" for help.
postgres=#From here we can set a strong password for Postgres user
postgres=# alter user postgres with password 'PostgresStr0ngPassw0rd#';
ALTER ROLELogout from the shell
postgres=# \q
-bash-4.2$ logoutTo this far, we’ve installed and configured PostgreSQL 14 database server on our CentOS 7 / RHEL 7 Linux system. We hope this guide helped you in one way or another and thank you for visiting our website. Cheers!.
Recommended books:
More guides relating to PostgreSQL:
