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
Set the password hashing algorithm to scram-sha-256.
vi /var/lib/pgsql/data/postgresql.conf
FROM
#password_encryption = md5 # md5 or scram-sha-256
TO
password_encryption = scram-sha-256 # scram-sha-256 or md5
Change the following line for the IPv4 local connections
/var/lib/pgsql/data/pg_hba.conf
FROM
host all all 127.0.0.1/32 ident
TO
host all all 127.0.0.1/32 scram-sha-256
Restart the service
-
systemctl stop postgresql.service
systemctl start postgresql.service
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