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
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 <dbuser> -h <serverip> -p 5432 <dbname>Step 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:
