In this tutorial we are going to learn how to install PostgreSQL 14 on Rocky Linux. PostgreSQL is world’s most advanced open source relational database. It has experienced running up to 30 years that is why it has earned strong reputation for its reliability, robustness and its strong performance.
PostgreSQL 14 brings a variety of features that help developers and administrators deploy there data backed applications. PostgreSQL 14 continues to build on complex data types include access to convenient JSON files and support for non contiguous ranges of data. PostgreSQL 14 its main agenda was to provide high performance and distributed data workloads. It advances in data concurrency, high write workloads, query parallelism and logical replication.
New features contained in PostgreSQL 14
- The SQL standard search and cycle for options for common table expressions have been implemented. When computing a tree traversal using a recursive query, you might want to order the results in either depth-first or breadth-first order.
- B-tree index updates are managed more effectively, reducing index plot.
- libpq now has the ability to pipeline multiple queries, which can boost throughput over high latency connections. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
- Extended statistics can now be collected on expressions, allowing better planning for complex results.
- Improvement have been made for parallel queries, heavily concurrent workloads, partitioned tables, logical replication and vacuuming. VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.
- VACUUM has become more aggressive, it skips inessential cleanup if the database starts to approach a transaction ID wraparound condition.
Prerequisites
- Rocky Linux 8 server
- Internet connection
- Familiar to command line interface
Check the following for more open source databases
1. Install PostgreSQL 14
Run system update as a starting point with the following command:
$ sudo dnf update -yFirst check the default version of Apstream for PostgreSQL with the following command:
$ sudo dnf module list postgresql# sudo dnf module list postgresql
DigitalOcean Droplet Agent 39 kB/s | 3.3 kB 00:00
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]nstalledAs you can see the default version of PostgreSQL on Apstream is PostgreSQL 10 as shown by [d]. So to install PostgreSQL 14.
2. Add official repository
In order to install PostgreSQL on rocky Linux we need to add official YUM repository with the following command:
$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpmSample output
....
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:
pgdg-redhat-repo-42.0-20.noarch
Complete!3. Install PostgreSQL 14 on Rocky Linux 8 | Alma Linux 8
Disable the default PostgreSQL 10 from the system so that it can allow you install the newer version.
$ sudo dnf -qy module disable postgresqlSample output
# sudo dnf -qy module disable postgresql
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDGThen we can enable PostgreSQL 14 with the following command:
$ sudo dnf -y install postgresql14 postgresql14-server....
Preparing : 1/1
Installing : postgresql14-libs-14.0-1PGDG.rhel8.x86_64 1/5
Running scriptlet: postgresql14-libs-14.0-1PGDG.rhel8.x86_64 1/5
Installing : libicu-60.3-2.el8_1.x86_64 2/5
Running scriptlet: libicu-60.3-2.el8_1.x86_64 2/5
Installing : lz4-1.8.3-3.el8_4.x86_64 3/5
Installing : postgresql14-14.0-1PGDG.rhel8.x86_64 4/5
Running scriptlet: postgresql14-14.0-1PGDG.rhel8.x86_64 4/5
Running scriptlet: postgresql14-server-14.0-1PGDG.rhel8.x86_64 5/5
Installing : postgresql14-server-14.0-1PGDG.rhel8.x86_64 5/5
Running scriptlet: postgresql14-server-14.0-1PGDG.rhel8.x86_64 5/5
Verifying : libicu-60.3-2.el8_1.x86_64 1/5
Verifying : lz4-1.8.3-3.el8_4.x86_64 2/5
Verifying : postgresql14-14.0-1PGDG.rhel8.x86_64 3/5
Verifying : postgresql14-libs-14.0-1PGDG.rhel8.x86_64 4/5
Verifying : postgresql14-server-14.0-1PGDG.rhel8.x86_64 5/5
Installed:
libicu-60.3-2.el8_1.x86_64 lz4-1.8.3-3.el8_4.x86_64
postgresql14-14.0-1PGDG.rhel8.x86_64 postgresql14-libs-14.0-1PGDG.rhel8.x86_64
postgresql14-server-14.0-1PGDG.rhel8.x86_64
Complete!4. Enable PostgreSQL 14 service and initialize the database
We can initialize our database first with this command:
$ sudo /usr/pgsql-14/bin/postgresql-14-setup initdbYou will get the following output.
# sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OKWe can enable the service with the following command:
$ sudo systemctl enable --now postgresql-14Then you can start the service thereafter with the following command.
$ sudo systemctl start postgresql-14Confirm that the services is running first in order for you to continue.
$ sudo systemctl status postgresql-14If you get the following as output, you are on the right track
# sudo 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 Thu 2021-10-28 17:48:21 UTC; 8min ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 64082 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCC>
Main PID: 64087 (postmaster)
Tasks: 8 (limit: 11189)
Memory: 16.8M
CGroup: /system.slice/postgresql-14.service
├─64087 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─64089 postgres: logger
├─64091 postgres: checkpointer
├─64092 postgres: background writer
├─64093 postgres: walwriter
├─64094 postgres: autovacuum launcher
├─64095 postgres: stats collector
└─64096 postgres: logical replication launcher
Oct 28 17:48:21 rockylinux systemd[1]: Starting PostgreSQL 14 database server...
Oct 28 17:48:21 rockylinux postmaster[64087]: 2021-10-28 17:48:21.106 UTC [64087] LOG: redirecting log output t>
Oct 28 17:48:21 rockylinux postmaster[64087]: 2021-10-28 17:48:21.106 UTC [64087] HINT: Future log output will >
Oct 28 17:48:21 rockylinux systemd[1]: Started PostgreSQL 14 database server.Conclusion
Congratulations! You have installed PostgreSQL 14 on Rocky Linux 8, you have also learn why PostgreSQL is important to large data set. For more information always consult the PostgreSQL documentation.
