Share This Article
Contents
PostgreSQL is one of the leading and widely used open source relational database management systems (RDBMS) that stores structured data in tables, just like MySQL. It is used by developers in their tech stacks and even by large enterprises and corporations to store website and application data. PostgreSQL uses SQL query language to store and manipulate data and also provides JSON support.
At the time of publishing this guide, PostgreSQL 16 is the latest PostgreSQL release. It was officially released on September 14, 2023, by the PostgreSQL Global Development Group and ships with major improvements and enhancements.
Let’s have a glance at some of the new enhancements and features in PostgreSQL 16:
Logical Replication
PostgreSQL 16 includes significant logical replication enhancements to its existing replication feature. The latest update allows developers to initiate logical replication from a standby instance. This implies that standbys can publish changes to other servers, offering added flexibility in replication setups and support for complex data architectures.
Enhanced Monitoring
To enhance the monitoring of various database metrics, PostgreSQL 16 provides pg_stat_io, a statistics view feature that offers deeper insights into the I/O activity of your database server. It performs a granular analysis of I/O access patterns, thus allowing better visibility and troubleshooting.
Access Control and Security
The latest release fine-tunes access control by refining user roles and privileges and improving the management of pg_hba.conf and pg_ident.conf files.
CPU Acceleration Using SIMD
PostgreSQL 16 introduces CPU acceleration using SIMD (Single Instruction, Multiple Data) on both x86/x86_64 and AArch64 architectures.
Bulk Data Loading
Bulk processing has been optimized in PostgreSQL 16. As the name implies, bulk loading is a process of loading enormous amounts of data into the database in the shortest time possible. This allows the insertion of many rows in a table in a relatively short period.
Additional Enhancements
Other improvements that ship with PostgreSQL include improved JSON support, security improvements, enhancement in parallel execution, and much more.
Check out the PostgreSQL 16 release notes for more information. Let’s now look at how to install PostgreSQL 16 on RHEL 9.
Step 1: Add the PostgreSQL Repository
To get started, log into your server and update the packages to their current versions. In our case, our server has already been updated.
$ sudo dnf update -y
To install PostgreSQL 16, which is the latest PostgreSQL version at the time of writing this guide, you need to add the PostgreSQL repository for RHEL 9 as shown.
$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
Next, disable the built-in PostgreSQL module.
$ sudo dnf -qy module disable postgresql
Step 2: Install PostgreSQL 16 on RHEL 9
Once the PostgreSQL repository has been added, proceed and install PostgreSQL by running the following DNF command:
$ sudo dnf install postgresql16-server -y
The command installs the PostgreSQL server alongside other packages and dependencies.
Once installed, be sure to initialize the database as shown.
$ sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
Step 3: How to Manage the PostgreSQL Service
So far, we have managed to install PostgreSQL 16 on RHEL 9. In this section, we explore how to manage the service.
To set the PostgreSQL service to auto-start every time upon booting, run the command:
$ sudo systemctl enable postgresql-16
By default, PostgreSQL does not start automatically. To start it, run the command:
$ sudo systemctl start postgresql-16
To verify that the service is running, execute the command:
$ sudo systemctl status postgresql-16
The following output confirms that PostgreSQL is up and running.
To restart the database server, run the command:
$ sudo systemctl restart postgresql-16
You can also reload the database server without stopping the service.
$ sudo systemctl reload postgresql-16
To check the version of PostgreSQL on your server, run the following command:
$ psql -V
The output confirms that we are running PostgreSQL 16.0.
Step 4: How to Enable Authentication in PostgreSQL
Note: Follow the principle of least privilege when configuring your system. It’s always best to assign privileges to an unprivileged role.
During installation, a UNIX user called Postgres is created. This user is also the super-user for the PostgreSQL database and has full administrative privileges.
However, the Postgres user does not have a password and you can access the database server without authentication. To demonstrate this, simply access the PostgreSQL shell as shown.
$ sudo -u postgres psql
To quit the shell, run the \q command:
# \q
As you can see, we were not prompted for a password. Accessing the database server without authentication could pose a huge security risk, and for this reason, we need to set a password for the Postgres user.
Therefore, access the psql prompt again.
$ sudo -u postgres psql
Next, run the following command to set a password for the Postgres user:
\password postgres
Finally, exit the psql prompt.
\q
Next, access the PostgreSQL access policy configuration file.
$ sudo vim /var/lib/pgsql/16/data/pg_hba.conf
Locate the line that starts with local and change the attribute peer to md5 to enable md5 authentication.
local all all md5
Save the changes and exit. Then reload PostgreSQL for the changes to come into effect.
$ sudo systemctl reload postgresql-16
When you access the psql shell, this time around, you will be required to provide a password. Type it in and hit ENTER.
To list all the database users, run the query:
# \du
To list all the existing databases on the server, run the query:
\l
Conclusion
In this guide, we have demonstrated how to install PostgreSQL 16 on RHEL 9. We have further demonstrated how to enable authentication and perform basic database operations.
It’s advisable to consult with a network security specialist to establish the appropriate firewall setup as well as network and transport-level security settings to harden your database. The database can be protected through the use of its built-in roles and features such as row-level security. It also has built-in auditing capabilities for auditing and visibility into events such as SQL statements being executed.
If you’d like to develop more RHEL 9 skills, check out the following tutorials:
