Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8
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 14, 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 14 on Rocky Linux 8 | AlmaLinux 8 | CentOS 8 Linux system.
Features of PostgreSQL 14
The following are the cool features shifted with PostgreSQL 14:
- Parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming all increase performance.
- B-tree index updates are handled more effectively, resulting in less index bloat.
- Pipelined queries were supported using libpq, which increased throughput dramatically over high-latency connections.
- Enhancements to security.
- The jsonb and hstore types now provide subscripting operators.
- Multiranges and JSON conveniences enable the representation of non-contiguous data ranges.
- Data from stored procedures can now be returned using OUT parameters.
Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8
The steps below will guide you on how to install PostgreSQL 14 on PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8.
Step 1: System Update
Before you begin installing PostgreSQL 14 on Rocky Linux 8 | AlmaLinux 8 | CentOS 8, make the following updates to your system:
sudo dnf updateReboot your system after the update:
sudo rebootStep 2: Add PostgreSQL Repository on Rocky Linux 8|AlmaLinux 8|CentOS 8
PostgreSQL server 9.6, 10, 12 and 13 are included in the AppStream components.
$ dnf module list postgresql
Last metadata expiration check: 1 day, 2:59:37 ago on Tue 26 Oct 2021 08:31:04 AM EDT.
Rocky Linux 8 - AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
postgresql 13 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 14 on Rocky Linux 8 | AlmaLinux 8:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpmInstallation output sample:
....
pgdg-redhat-repo-latest.noarch.rpm 3.1 kB/s | 12 kB 00:03
Dependencies resolved.
===============================================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-20 @commandline 12 k
Transaction Summary
===============================================================================================================================================================================================
Install 1 Package
Total size: 12 k
Installed size: 12 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-20.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-20.noarch 1/1
Installed products updated.
Installed:
pgdg-redhat-repo-42.0-20.noarch
Complete!Step 3: Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8
Disable the default PostgreSQL module once the repository has been added:
sudo dnf -qy module disable postgresqlThen install PostgreSQL 14 Server and Client:
$ sudo dnf install -y postgresql14-server
Last metadata expiration check: 0:05:42 ago on Wed 27 Oct 2021 11:39:42 AM EDT.
Dependencies resolved.
===============================================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================================
Installing:
postgresql14-server x86_64 14.0-1PGDG.rhel8 pgdg14 5.7 M
Installing dependencies:
postgresql14 x86_64 14.0-1PGDG.rhel8 pgdg14 1.5 M
postgresql14-libs x86_64 14.0-1PGDG.rhel8 pgdg14 274 k
Transaction Summary
===============================================================================================================================================================================================
Install 3 Packages
Total download size: 7.5 M
Installed size: 31 MStep 4: Initialize and Start PostgreSQL 14 Database Service
Before running the service, you must first initialize the database instance after installing PostgreSQL 14:
$ sudo /usr/pgsql-14/bin/postgresql-14-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/14/data/postgresql.conf.
The PostgreSQL 14 database service is enabled and started as follows:
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14Verify that the PostgreSQL 14 service is up and running:
$ 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 Wed 2021-10-27 13:06:30 EDT; 1min 11s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 7547 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir $PGDATA (code=exited, status=0/SUCCESS)
Main PID: 7552 (postmaster)
Tasks: 8 (limit: 23547)
Memory: 16.7M
CGroup: /system.slice/postgresql-14.service
├─7552 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─7554 postgres: logger
├─7556 postgres: checkpointer
├─7557 postgres: background writer
├─7558 postgres: walwriter
├─7559 postgres: autovacuum launcher
├─7560 postgres: stats collector
└─7561 postgres: logical replication launcher
Oct 27 13:06:30 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
Oct 27 13:06:30 localhost.localdomain postmaster[7552]: 2021-10-27 13:06:30.638 EDT [7552] LOG: redirecting log output to logging collector process
Oct 27 13:06:30 localhost.localdomain postmaster[7552]: 2021-10-27 13:06:30.638 EDT [7552] HINT: Future log output will appear in directory "log".
Oct 27 13:06:30 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.Step 5: Connect to PostgreSQL 14 Database Locally
There are two methods to connect to a PostgreSQL 14 instance.
Method 1
Using sudo to run the Postgresql command directly.
$ sudo -u postgres psql
psql (14.0)
Type "help" for help.
postgres=#Method 2
To use this method, you must first switch to the Postgresql user created after installing PostgreSQL 14.
$ sudo -i -u postgres
[postgres@localhost ~]$Connect to the instance while you’re here using psql command.
$ psql
psql (14.0)
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:
$ psql -c "alter user postgres with password 'StrongPassword'"
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 (
postgres(# user_id serial PRIMARY KEY,
postgres(# username VARCHAR ( 50 ) UNIQUE NOT NULL,
postgres(# password VARCHAR ( 50 ) NOT NULL,
postgres(# email VARCHAR ( 255 ) UNIQUE NOT NULL,
postgres(# created_on TIMESTAMP NOT NULL,
postgres(# last_login TIMESTAMP
postgres(# );
CREATE TABLEList 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: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:5432 0.0.0.0:* LISTEN 7552/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:5432 :::* LISTEN 7552/postmasterFrom the above output is running on port 5432.
By modifying the postgresql.conf file in the /var/lib/psql/14/data/ directory, we may change PostgreSQL’s default service port, which is 5432.
sudo vim /var/lib/pgsql/14/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-14confirm 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/14/data/postgresql.conf
listen_addresses = '192.168.156.53'PostgreSQL should also be configured to accept remote connections:
$ sudo vim /var/lib/pgsql/14/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.156.0/24 md5You must restart database service after performing the changes:
sudo systemctl restart postgresql-14If 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 -h -p 5432Step 8: Install PgAdmin 4 on Rocky Linux 8|AlmaLinux 8|CentOS 8
PgAdmin 4 tool enables you to administer PostgreSQL database server from a web interface. You can install it by following our guide in the link below:
Conclusion
Our article on how to install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8|CentOS 8 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.
https://www.computingpost.com/install-postgresql-14-on-rocky-linux-8almalinux-8centos-8-2/?feed_id=1738&_unique_id=632d555486506
