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 : PostgreSQL
From 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.rpm
Example 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: 37289
Step 2: Install PostgreSQL 14 CentOS 7 | RHEL 7
After repository has been added, update system and reboot
sudo yum -y update sudo systemctl reboot
Wait for the system to come online then login and install PostgreSQL server and client packages
sudo yum install -y postgresql14-server postgresql14
Check 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 ... OK
Start 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 - postgres
Access 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 ROLE
Logout from the shell
postgres=# \q -bash-4.2$ logout
To 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!.