PostgreSQL is a relational database management system that is well-known for its dependability and resilience. You can count on reliability and issue fixes in your database lifecycle thanks to over 30 years of open source development from a global developer community. PostgreSQL is used by thousands of enterprises to support financial transactions, large amounts of website traffic, and e-commerce platforms, among other things. It’s also possible to add custom functions written in programming languages like Java, Python, C/C++, and others.
PostgreSQL just announced the release of PostgreSQL 17, a new version of the database. This new version has numerous speed enhancements to make it easier for administrators and developers to construct data-driven applications. Furthermore, it maintains PostgreSQL’s reputation by enhancing logical replication, query parallelism, high-write workloads, and connection concurrency, all of which result in improved performance. In this guide we will help you install PostgreSQL 17 on Rocky Linux 9 | AlmaLinux 9 | CentOS 9 Linux system.
Features of PostgreSQL 17
The following are the cool features shifted with PostgreSQL 17:
- New memory management system for
VACUUM, which reduces memory consumption and can improve overall vacuuming performance. - New SQL/JSON capabilities, including constructors, identity functions, and the
JSON_TABLE()function, which converts JSON data into a table representation. - Various query performance improvements, including for sequential reads using streaming I/O, write throughput under high concurrency, and searches over multiple values in a btree index.
- Logical replication enhancements, including:
- Failover control
- pg_createsubscriber, a utility that creates logical replicas from physical standbys
- pg_upgrade now preserves logical replication slots on publishers and full subscription state on subscribers. This will allow upgrades to future major versions to continue logical replication without requiring copy to resynchronize.
- New client-side connection option,
sslnegotiation=direct, that performs a direct TLS handshake to avoid a round-trip negotiation. - pg_basebackup now supports incremental backup.
COPYadds a new option,ON_ERROR ignore, that allows a copy operation to continue in the event of an error.
Install PostgreSQL 17 on Rocky 9 / AlmaLinux 9 / CentOS 9
The steps below will guide you on how to install PostgreSQL 17 on PostgreSQL 17 on Rocky Linux 9|AlmaLinux 9|CentOS 9.
Step 1: System Update
Before you begin installing PostgreSQL 17 on Rocky Linux 9 | AlmaLinux 9 | CentOS 9, make the following updates to your system:
sudo dnf update -yReboot your system after the update:
sudo reboot nowStep 2: Add PostgreSQL Repository
PostgreSQL server 15 and 16 are included in the AppStream components.
$ dnf module list postgresql
Rocky Linux 9 - AppStream
Name Stream Profiles Summary
postgresql 15 client, server [d] PostgreSQL server and client module
postgresql 16 client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalledWe’ll need to add the following official repositories to install PostgreSQL 17 on Rocky Linux 9 | AlmaLinux 9:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpmInstallation output sample:
Last metadata expiration check: 0:03:11 ago on Tue 04 Feb 2025 05:59:48 PM EAT.
pgdg-redhat-repo-latest.noarch.rpm 14 kB/s | 12 kB 00:00
Dependencies resolved.
=====================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-46PGDG @commandline 12 k
Transaction Summary
=====================================================================================================================================================
Install 1 Package
Total size: 12 k
Installed size: 17 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-46PGDG.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-46PGDG.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-46PGDG.noarch
Complete!Step 3: Install PostgreSQL 17
Disable the default PostgreSQL module once the repository has been added:
sudo dnf -qy module disable postgresqlThen install PostgreSQL 17 Server and Client:
$ sudo dnf install -y postgresql17-server
Last metadata expiration check: 0:00:31 ago on Tue 04 Feb 2025 06:04:32 PM EAT.
Dependencies resolved.
=====================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================
Installing:
postgresql17-server x86_64 17.2-1PGDG.rhel9 pgdg17 7.0 M
Installing dependencies:
libicu x86_64 67.1-9.el9 baseos 9.6 M
postgresql17 x86_64 17.2-1PGDG.rhel9 pgdg17 1.9 M
postgresql17-libs x86_64 17.2-1PGDG.rhel9 pgdg17 339 k
Transaction Summary
=====================================================================================================================================================
Install 4 Packages
Total download size: 19 M
Installed size: 74 M
Downloading Packages:
(1/4): postgresql17-libs-17.2-1PGDG.rhel9.x86_64.rpm 266 kB/s | 339 kB 00:01
(2/4): libicu-67.1-9.el9.x86_64.rpm 16 MB/s | 9.6 MB 00:00
(3/4): postgresql17-17.2-1PGDG.rhel9.x86_64.rpm 1.0 MB/s | 1.9 MB 00:01
(4/4): postgresql17-server-17.2-1PGDG.rhel9.x86_64.rpm 2.8 MB/s | 7.0 MB 00:02
-----------------------------------------------------------------------------------------------------------------------------------------------------
Total 5.2 MB/s | 19 MB 00:03
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 9 - x86_64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0x08B40D20:
Userid : "PostgreSQL RPM Repository <[email protected]>"
Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Key imported successfully...Step 4: Initialize and Start PostgreSQL 17
Before running the service, you must first initialize the database instance after installing PostgreSQL 17:
$ sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
Initializing database ... OKThis will create the initial data as well as the main configuration file, which will be written to /var/lib/pgsql/17/data/postgresql.conf.
The PostgreSQL 17 database service is enabled and started as follows:
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17Verify that the PostgreSQL 14 service is up and running:
$ systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
Active: active (running) since Tue 2025-02-04 18:08:34 EAT; 10s ago
Docs: https://www.postgresql.org/docs/17/static/
Process: 5621 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 5626 (postgres)
Tasks: 7 (limit: 36017)
Memory: 17.5M
CPU: 42ms
CGroup: /system.slice/postgresql-17.service
├─5626 /usr/pgsql-17/bin/postgres -D /var/lib/pgsql/17/data/
├─5627 "postgres: logger "
├─5628 "postgres: checkpointer "
├─5629 "postgres: background writer "
├─5631 "postgres: walwriter "
├─5632 "postgres: autovacuum launcher "
└─5633 "postgres: logical replication launcher "
Feb 04 18:08:34 rocky9.cloudspinx.com systemd[1]: Starting PostgreSQL 17 database server...
Feb 04 18:08:34 rocky9.cloudspinx.com postgres[5626]: 2025-02-04 18:08:34.132 EAT [5626] LOG: redirecting log output to logging collector process
Feb 04 18:08:34 rocky9.cloudspinx.com postgres[5626]: 2025-02-04 18:08:34.132 EAT [5626] HINT: Future log output will appear in directory "log".
Feb 04 18:08:34 rocky9.cloudspinx.com systemd[1]: Started PostgreSQL 17 database server.
Step 5: Connect to PostgreSQL 17 Locally
There are two methods to connect to a PostgreSQL 17 instance.
Method 1
Using sudo to run the Postgresql command directly.
$ sudo -u postgres psql
psql (17.2)
Type "help" for help.
postgres=#Method 2
To use this method, you must first switch to the Postgresql user created after installing PostgreSQL 17.
$ sudo -i -u postgres
[postgres@localhost ~]$ Connect to the instance while you’re here using psql command.
$ psql
psql (17.2)
Type "help" for help.
postgres=#Secure Postgres User
Now that you’re logged in as the postgres user, execute the following command to set a strong password for the default postgres user:
# ALTER USER postgres WITH PASSWORD 'StrongPassw0rd';
ALTER ROLECreate Database in PostgreSQL
Connect to PostgreSQL to create a database. When PostgreSQL is installed, a default user named ‘postgres‘ is created. Make a connection with this user first. Create database called testdb.
# CREATE DATABASE testdb;
CREATE DATABASECreate Tables in PostgreSQL
We’ll make a new table called Employees with the columns listed below:
- user_id – primary key
- username – unique and not null
- password – not null
- email – unique and not null
- created_on – not null
- last_login – null
# CREATE TABLE Employees (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);List PostgreSQL Tables:
# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres
(1 row)
Delete PostgreSQL Tables:
# DROP TABLE employees;
DROP TABLEStep 6: Changing PostgreSQL Service Port
Check the default port on which PostgreSQL listens:
$ sudo netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 753/sshd: /usr/sbin
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 5626/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 5626/postgres
tcp6 0 0 :::22 :::* LISTEN 753/sshd: /usr/sbin From the above output is running on port 5432.
By modifying the postgresql.conf file in the /var/lib/psql/17/data/ directory, we may change PostgreSQL’s default service port, which is 5432.
sudo vim /var/lib/pgsql/17/data/postgresql.confIn the above file search for #port = 5432 and change to 5436:
# line 64 uncomment and change it to 5436
port = 5436 # (change requires restart)To make the changes take effect, restart PostgreSQL:
sudo systemctl restart postgresql-17confirm if PostgreSQL is listening on port 5436:
$ sudo netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1/systemd
tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 1592/dnsmasq
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1014/sshd
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1015/cupsd
tcp 0 0 127.0.0.1:5436 0.0.0.0:* LISTEN 11630/postmaster
tcp6 0 0 :::111 :::* LISTEN 1/systemd
tcp6 0 0 :::22 :::* LISTEN 1014/sshd
tcp6 0 0 ::1:631 :::* LISTEN 1015/cupsd
tcp6 0 0 ::1:5436 :::* LISTEN 11630/postmasterPostgreSQL is now listening on port 5436.
Step 7: Enable PostgreSQL Remote Access
You can update the configuration and set Listen address to your server IP address or “*” for all interfaces if you have applications that will connect to the server via the network.
$ sudo vim /var/lib/pgsql/17/data/postgresql.conf
listen_addresses = '192.168.156.53'PostgreSQL should also be configured to accept remote connections:
$ sudo vim /var/lib/pgsql/17/data/pg_hba.conf
# Accept from anywhere
#host all all 0.0.0.0/0 md5
# Accept from trusted subnet
host all all 192.168.1.0/24 md5You must restart database service after performing the changes:
sudo systemctl restart postgresql-17If you have an active firewalld service, allow port 5432/tcp:
sudo firewall-cmd –zone=public –add-port=5432/tcp –permanent
sudo firewall-cmd –reloadPass the IP address of your server to the psql command to test database remote connections:
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>PostgreSQL Learning materials:
Conclusion
Our article on how to install PostgreSQL 17 on Rocky Linux 9|AlmaLinux 9|CentOS Stream 9 is now complete. We hope you found this material useful; we will continue to provide you with new and valuable tutorials. Keep an eye out for updates.
Similar Guides:
