What is PostgreSQL?
PostgreSQL is an open source (BSD license), enterprise-grade, advanced object-relational database system that supports both relational (SQL) and non-relational (JSON) querying.
Answer to "Why PostgreSQL" question:
- It is free and open source.
- PostgreSQL is not affiliated with any company and is open to all developers. Anyone who needs it can customize and use PostgreSQL to meet their needs.
- PostgreSQL has an extensible structure.
- You can define your own data types without recompiling your database.
- You can create custom functions and even code from different programming languages.
- It has flexible full-text search feature. The full-text search feature can be used when searching for sequences that execute vector operations and sequence searches.
- It can be used in all server environments thanks to the SQL functions called Store Procedure.
- PostgreSQL conforms to at least 170 of the 179 mandatory attributes for SQL:2016 Core compatibility.
In this article, we will describe the installation of this popular database on Rocky Linux 9.
How to install Postgresql?
We will explain the installation in 2 different ways. We will first install it from the Rocky Linux repository, then from the official PostgreSQL repository.
Method-1: Install From Repository
In this step install the postgresql-server package from the repository:
[foc@rocky9 ~]$ sudo dnf install postgresql-server.x86_64 Rocky Linux 9 - BaseOS 3.8 kB/s | 3.6 kB 00:00 Rocky Linux 9 - BaseOS 962 kB/s | 1.7 MB 00:01 Rocky Linux 9 - AppStream 7.7 kB/s | 4.1 kB 00:00 Rocky Linux 9 - AppStream 2.3 MB/s | 6.4 MB 00:02 Rocky Linux 9 - Extras 5.6 kB/s | 2.9 kB 00:00 Rocky Linux 9 - Extras 10 kB/s | 7.7 kB 00:00 Dependencies resolved. ... Installed: libicu-67.1-9.el9.x86_64 postgresql-13.7-1.el9_0.x86_64 postgresql-private-libs-13.7-1.el9_0.x86_64 postgresql-server-13.7-1.el9_0.x86_64 Complete!
PostgreSQL server version available in the repository:
[foc@rocky9 ~]$ postgres --version
postgres (PostgreSQL) 13.7
Create a new PostgreSQL database cluster:
[foc@rocky9 ~]$ sudo /usr/bin/postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Note: If you get an error like the one below, it means you didn't follow the above step.
Directory "/var/lib/pgsql/data" is missing or empty
Now enable and start the service:
[foc@rocky9 ~]$ sudo systemctl enable --now postgresql
Status of the service:
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; ven>
Active: active (running) since Thu 2023-01-05 20:42:19 +03; 55s ago
Process: 11315 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgres>
Main PID: 11317 (postmaster)
Tasks: 8 (limit: 9122)
Memory: 16.8M
CPU: 44ms
CGroup: /system.slice/postgresql.service
...
Switch to postgres user:
[foc@rocky9 ~]$ sudo -u postgres -i
Finally, login to the postgresql server with psql:
[postgres@rocky9 ~]$ psql
psql (13.7)
Type "help" for help.
postgres=#
Installation completed successfully.
Method-2: Install from PostgreSQL Repository
Install the RPM package that will add the repository:
[foc@rocky9 ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm [sudo] password for foc: Rocky Linux 9 - BaseOS 1.1 MB/s | 1.7 MB 00:01 Rocky Linux 9 - AppStream 2.3 MB/s | 6.4 MB 00:02 Rocky Linux 9 - Extras 10 kB/s | 7.7 kB 00:00 pgdg-redhat-repo-latest.noarch.rpm 19 kB/s | 12 kB 00:00 Dependencies resolved. ======================================================================== Package Arch Version Repository Size ======================================================================== Installing: pgdg-redhat-repo noarch 42.0-28 @commandline 12 k Transaction Summary ======================================================================== Install 1 Package Total size: 12 k Installed size: 14 k ... Transaction test succeeded. Running transaction Preparing : 1/1 Installing : pgdg-redhat-repo-42.0-28.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-28.noarch 1/1 Installed: pgdg-redhat-repo-42.0-28.noarch Complete!
Then disable the built-in PostgreSQL module:
[foc@rocky9 ~]$ sudo dnf -qy module disable postgresql
Install PostgreSQL:
[foc@rocky9 ~]$ sudo dnf install -y postgresql15-server.x86_64
Create a new PostgreSQL database cluster:
[foc@rocky9 ~]$ sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
Initialize the database and enable automatic start:
[foc@rocky9 ~]$ sudo systemctl enable --now postgresql-15
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service.
postgresql-15 service status:
[foc@rocky9 ~]$ sudo systemctl status postgresql-15 ● postgresql-15.service - PostgreSQL 15 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; ena> Active: active (running) since Thu 2022-12-29 22:33:42 +03; 14s ago Docs: https://www.postgresql.org/docs/15/static/ Process: 2480 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db> Main PID: 2485 (postmaster) Tasks: 7 (limit: 9122) Memory: 17.5M CPU: 29ms CGroup: /system.slice/postgresql-15.service ├─2485 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/d> ├─2486 "postgres: logger " ...
After installation, executable postgresql files can be run from /usr/pgsql-15/bin/ directory. Installed postgresql version:
[foc@rocky9 ~]$ /usr/pgsql-15/bin/postgres --version
postgres (PostgreSQL) 15.1
Successfully installed version 15 from the PostgreSQL repository.
[foc@rocky9 ~]$ sudo -u postgres -i
Then login to posgtresql server:
[postgres@rocky9 ~]$ /usr/pgsql-15/bin/psql
psql (15.1)
Type "help" for help.
postgres=#
Summary
Different versions are available at the PostgreSQL official address. After adding the official repository to the system, you can install the version you want from v10 to v15.
PostgreSQL 16 is released in alpha, so installation is not recommended. When it is stable, it will be installed in the official repository.
We installed with 2 different versions. The choice is yours. If there is no reason, installing from the official Rocky repository is recommended.
References
www.postgresql.org - Linux downloads (Red Hat family)