How to Install PostgreSQL on Linux?
Introduction
Installing PostgreSQL
Initialize the Database and Enable Automatic Start
Connecting to PostGreSQL DB
Creating PostgreSQL users
Creating a PostgreSQL Database
Introduction
PostgreSQL is a robust and highly flexible open-source database server that uses the SQL language. It provides a powerful system for managing large amounts of data and supporting multiple users simultaneously. It can be used in clusters to handle even higher volumes of data.
With PostgreSQL, you can ensure the integrity of your data, create fault-tolerant environments, and develop applications with ease. It allows you to extend the functionality of the database by adding your own custom data types, functions, or code in various programming languages, without requiring any recompilation of the database software.
To learn about PostgreSQL 15's new features, follow this article
PostgreSQL 15's Must-Know Features for Database Enthusiasts
In this article, we will cover how to install and configure PostgreSQL on a RHEL (Red Hat Enterprise Linux 8.8) system.
Installing PostgreSQL
In RHEL 8.8, PostgreSQL 10,12, 13 and 15 are available as the initial versions within the Application Stream. Installing PostgreSQL 15 is straightforward using the RPM package.
If you haven't set up a Yum repository yet, please refer to the tutorial
How to configure a local Yum repository in RHEL Linux for guidance.
Execute the following command to check the available PostgreSQL packages:
[root@dbdocs ~]# sudo yum module list | grep postgresql
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
postgresql 15 client, server [d] PostgreSQL server and client module
In the previous step, we noticed that RHEL 8.8 came with five Application Streams for the PostgreSQL server. By default, PostgreSQL 10 will be installed if no specific version is specified during the installation process.
To install the latest version, PostgreSQL 15, please run the following command:
[root@dbdocs ~]# sudo yum install @postgresql:15
Last metadata expiration check: 2:57:18 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
========================================================================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================================================================
Installing group/module packages:
postgresql-server x86_64 15.2-1.module+el8.8.0+18155+992b6275 InstallMedia-AppStream 6.0 M
Installing dependencies:
postgresql x86_64 15.2-1.module+el8.8.0+18155+992b6275 InstallMedia-AppStream 1.7 M
postgresql-private-libs x86_64 15.2-1.module+el8.8.0+18155+992b6275 InstallMedia-AppStream 132 k
Installing module profiles:
postgresql/server
Enabling module streams:
postgresql 15
Transaction Summary
========================================================================================================================================================================
Install 3 Packages
Total size: 7.8 M
Installed size: 31 M
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64 1/3
Installing : postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64 2/3
Running scriptlet: postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64 3/3
Installing : postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64 3/3
Running scriptlet: postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64 3/3
Verifying : postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64 1/3
Verifying : postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64 2/3
Verifying : postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64 3/3
Installed products updated.
Installed:
postgresql-15.2-1.module+el8.8.0+18155+992b6275.x86_64 postgresql-private-libs-15.2-1.module+el8.8.0+18155+992b6275.x86_64
postgresql-server-15.2-1.module+el8.8.0+18155+992b6275.x86_64
Complete!
[root@dbdocs ~]#
If you encounter the following error during the installation of the PostgreSQL database, please reset the PostgreSQL module and attempt the installation again.
[root@dbdocs ~]# sudo yum install @postgresql:15
Last metadata expiration check: 2:54:56 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
The operation would result in switching of module 'postgresql' stream '10' to stream '15'
Error: It is not possible to switch enabled streams of a module unless explicitly enabled via configuration option module_stream_switch.
It is recommended to rather remove all installed content from the module, and reset the module using 'yum module reset "module_name" command. After you reset the module, you can install the other stream.
[root@dbdocs ~]# dnf module reset postgresql
Last metadata expiration check: 2:56:27 ago on Sun 25 Jun 2023 10:37:34 AM EDT.
Dependencies resolved.
========================================================================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================================================================
Resetting modules:
postgresql
Transaction Summary
========================================================================================================================================================================
Is this ok [y/N]: y
Complete!
Initialize the Database and Enable Automatic Start
To initialize the PostgreSQL database and add services to the server startup, follow these steps and execute the provided commands:
[root@dbdocs var]# postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@dbdocs var]#
Red Hat recommends storing the data in the default
/var/lib/pgsql/data directory.
[root@dbdocs var]# systemctl start postgresql.service
[root@dbdocs var]# systemctl enable postgresql.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@dbdocs var]#
To check the stauts of postgresql service
[root@dbdocs var]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2023-06-25 14:17:30 EDT; 4min 14s ago
Main PID: 55158 (postmaster)
Tasks: 7 (limit: 11003)
Memory: 23.4M
CGroup: /system.slice/postgresql.service
├─55158 /usr/bin/postmaster -D /var/lib/pgsql/data
├─55160 postgres: logger
├─55161 postgres: checkpointer
├─55162 postgres: background writer
├─55164 postgres: walwriter
├─55165 postgres: autovacuum launcher
└─55166 postgres: logical replication launcher
Jun 25 14:17:29 dbdocs systemd[1]: Starting PostgreSQL database server...
Jun 25 14:17:30 dbdocs postmaster[55158]: 2023-06-25 14:17:30.600 EDT [55158] LOG: redirecting log output to logging collector process
Jun 25 14:17:30 dbdocs postmaster[55158]: 2023-06-25 14:17:30.600 EDT [55158] HINT: Future log output will appear in directory "log".
Jun 25 14:17:30 dbdocs systemd[1]: Started PostgreSQL database server.
Connecting to PostGreSQL DB
[root@dbdocs var]# su - postgres
[postgres@dbdocs ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=#
--To check connnection information
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#
Creating PostgreSQL users
In PostgreSQL, there are different types of users with specific roles and permissions. Let's break them down:
The postgres Linux system user: This user is meant to be used exclusively for running the PostgreSQL server and client applications like
pg_dump. It is not recommended to perform interactive PostgreSQL administration tasks, such as creating databases or managing users, using this system user.
Database Superuser: The default superuser in PostgreSQL is called
postgres," but it is not directly associated with the
postgres system user. You can limit the access of the superuser in the
pg_hba.conf file. By default, there are no other limitations on the permissions of the superuser. However, you can create additional superusers with different names.
By running this SQL command, you will create a new user and assign it both the
CREATEROLE and
CREATEDB permissions.
-- Create a new user
postgres=# CREATE USER db_user;
CREATE ROLE
-- Set a password for the user
postgres=# ALTER USER db_user WITH PASSWORD 'db_user';
ALTER ROLE
-- Grant CREATEROLE and CREATEDB permissions to the user
postgres=# ALTER USER db_user WITH CREATEROLE CREATEDB;
ALTER ROLE
postgres=#
Creating a PostgreSQL Database
postgres=# create database dbdocs with owner db_user;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbdocs | db_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \q
Configure PostgreSQL Security and Remote Access
This section describes how to harden PostgreSQL authentication by enabling
SCRAM-SHA-256 password encryption and how to safely allow
local and remote client connections on RHEL-based systems.
1. Configure Password Encryption and Network Listening
PostgreSQL supports multiple password hashing algorithms. For improved security,
it is strongly recommended to use scram-sha-256, which provides
stronger protection than legacy md5.
Edit the PostgreSQL configuration file:
vi /var/lib/pgsql/data/postgresql.conf
Update the password encryption setting as shown below:
# FROM:
#password_encryption = md5 # md5 or scram-sha-256
# TO:
password_encryption = scram-sha-256 # scram-sha-256 or md5
Next, configure the network interfaces PostgreSQL will listen on.
To allow connections on all network interfaces (use with firewall restrictions):
listen_addresses = '*'
Recommended: Restrict PostgreSQL to a specific server IP where possible:
listen_addresses = '192.168.1.10'
2. Configure Client Authentication (pg_hba.conf)
The pg_hba.conf file controls which clients can connect to PostgreSQL,
which users are allowed, and what authentication method is required.
Edit the file:
vi /var/lib/pgsql/data/pg_hba.conf
Update Local IPv4 Authentication
Change local IPv4 connections to use scram-sha-256 instead of
ident authentication.
# FROM:
host all all 127.0.0.1/32 ident
# TO:
# This allows only localhost connections using SCRAM authentication
host all all 127.0.0.1/32 scram-sha-256
Allow a Specific Remote Host (Best Practice)
Grant access to a single trusted client system:
host all all 192.168.1.50/32 scram-sha-256
Allow an Entire Subnet (Use with Caution)
If multiple systems need access, you may allow a subnet:
host all all 192.168.1.0/24 scram-sha-256
Note: Always place more specific rules above broader ones, as
PostgreSQL processes this file from top to bottom.
3. Open the Firewall (RHEL)
Ensure the system firewall allows PostgreSQL traffic on port 5432.
Allow PostgreSQL Service (All Sources)
sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload
Restrict Firewall Access to a Specific IP (Recommended)
sudo firewall-cmd \
--add-rich-rule='rule family="ipv4" source address="192.168.1.50" service name="postgresql" accept' \
--permanent
sudo firewall-cmd --reload
4. Restart PostgreSQL Service
Apply the configuration changes by restarting the PostgreSQL service:
sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service
Alternatively, you may reload the configuration if only pg_hba.conf
was changed:
sudo systemctl reload postgresql.service
Result: PostgreSQL is now configured to use secure SCRAM-based
authentication and allows controlled local and remote access in a secure,
production-ready manner.
Connect to the new database
[postgres@dbdocs ~]$ psql -U db_user -h 127.0.0.1 -d dbdocs
Password for user db_user:
psql (15.2)
Type "help" for help.
dbdocs=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbdocs | db_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
dbdocs=> \conninfo
You are connected to database "dbdocs" as user "db_user" on host "127.0.0.1" at port "5432".
dbdocs=>
Related content