How to Install PostgreSQL on CentOS Stream 9 Server Tutorial
Install PostgreSQL on CentOS Stream 9 Server. This article will discuss what PostgreSQL is with its benefits and I will move into installation steps.
Even though NoSQL popularity is on the rise the relational databases are still preferred for many applications due to querying abilities and robustness.
Open source relational databases like MySQL vs PostgreSQL provide a cost effective alternative as a stable production database.
What is PostgreSQL?
PostgreSQL is an enterprise level open source database management system that can assist you in efficiently storing your most valuable data. For SQL compliance and extensibility, it supports both JSON and SQL for non relational and relational queries. It has the advanced features to compete with the big players in the industry like Sybase, Oracle, and IBM. You can use its capability to run sophisticated and data driven applications as PostgreSQL server is a professionally developed and maintained software.
It inherits the performance optimization and advanced data types features of expensive commercial databases like SQL Server and Oracle that can help in better management of your data.
PostgreSQL features
Let’s mention some of the features of using PostgreSQL server
- Developers can efficiently operate for building applications.
- Inheritance.
- Managing geospatial Data.
- Non Atomic Columns.
- Window functions
- It protects data integrity in a fault tolerant environment built on it.
- Highly compatible with different languages and middleware.
- Has a sophisticated locking mechanism.
- It can support multi version concurrency control.
- It offers excellent server side programming functionality.
- It is compliant with the ANSI SQL standard.
- You get quality support for client server network architecture.
- High availability and standby server.
- It can help you link with other data stores like NoSQL as it supports JSON and Querying JSON data.
- Data integrity as it is ACID compliant (Atomicity, Consistency, Isolation and Durability) compliant.
- Error Handling (Postgresql returns error message when error happens).
- Sharding or horizontal partitioning of a database. PostgreSQL offers support for partitioning.
These features can be used to drive significant benefits to your organization’s database management and processing.
Advantages of PostgreSQL
PostgreSQL provides multiple benefits, but we have filtered down the top advantages that showcase why it can challenge the big players in the database system industry.
Open source
PostgreSQL is an open source database system so that you can use the source code for free under an open source license. You have the freedom to use, implement and modify the code based on your business requirements.
There are no restrictions on usability and you can improve your business operations and database management with ease.
Minimize cost
PostgreSQL doesn’t cost anything as it is a genuine open source product. You don’t need to pay a hefty license fee and worry about monthly or yearly subscriptions. It also helps you escape the hassles of vendor lock in that can disturb your business workflow.
You don’t need to indulge in the hassle of contract issues common with a high budget commercial database system. You can use PostgreSQL wherever you want and in any way you want.
It helps you save the total cost of ownership because of the above savings.
Community support
PostgreSQL has strong community support as multiple individuals and companies contribute to the project and drive innovation to the ecosystem. Over the past 25 years, it has been running strong and community support is helping the new adaptors gel with the PostgreSQL ecosystem quickly.
You can get your queries and problems resolved quickly, and it can improve your existing database management process. It is supported by many extensions and NoSQL, SQL data models.
High security
PostgreSQL offers a wide range of features because of its extensibility that you can use to strengthen the security of your database. You need to use the right security features like data masking TDE, among others, to ensure your data is not compromised.
It offers multi layer protection from the external interface and gives the admins more control over the database.
Great scalability
PostgreSQL is a great database system that can handle your business operations at scale. It has multiple technical options that can handle the complexity of the large database network and help you streamline the process.
You can grow your PostgreSQL database based on your requirements with no hassles. It can help your business to reach the next level by offering data warehouse analytics, cloud services, high performing OLTP, Weka machines learning, IoT and data science.
The easy scalability option is one of the reasons why the industry is quickly adapting to the PostgreSQL database system.
Easy to understand
If you want to use postgres to the maximum potential, then you need to master the functionality and usability of the database system. The training of the tool is simple which makes it easier to use compared to other complex database systems.
With a robust community support network you can gain in depth knowledge about the database from experts from different industries and with different experiences.
Follow the below section of how to install PostgreSQL server on CentOS Stream 9.
Install PostgreSQL on CentOS Stream 9 Server
Add the PostgreSQL Repository
By default, the latest version of PostgreSQL is not included in the CentOS Stream 9 default repository. So you will need to add the PostgreSQL repository to your server.
First, list all available PostgreSQL server version using the following command:
dnf module list postgresql
You will get a list of all PostgreSQL versions in the following output:
CentOS 9 - AppStream 6.9 MB/s | 9.7 MB 00:01
CentOS 9 - BaseOS 8.5 MB/s | 6.7 MB 00:00
CentOS 9 - Extras 30 kB/s | 12 kB 00:00
CentOS 9 - 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]nstalled
As you can see, the latest PostgreSQL version 14 is not included in the above output. So you will need to add the PostgreSQL repository to your server.
You can install it using the following command:
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Once the repository is installed, you will need to disable the default PostgreSQL repository.
dnf -qy module disable postgresql
Install PostgreSQL on CentOS Stream 9
Next step about how to install PostgreSQL server on CentOS Stream 9 is to run the following command to install the latest PostgreSQL server:
dnf install postgresql14-server -y
Once the PostgreSQL is installed, initialize the PostgreSQL database using the following command:
/usr/pgsql-14/bin/postgresql-14-setup initdb
Please start the PostgreSQL service and enable it to start after the system reboot:
systemctl start postgresql-14
systemctl enable postgresql-14
Check the status of the PostgreSQL service using the following command:
systemctl status postgresql-14
You will get the following output:
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-03-17 13:34:07 UTC; 5s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 1950 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1955 (postmaster)
Tasks: 8 (limit: 11412)
Memory: 16.8M
CGroup: /system.slice/postgresql-14.service
├─1955 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─1957 postgres: logger
├─1959 postgres: checkpointer
├─1960 postgres: background writer
├─1961 postgres: walwriter
├─1962 postgres: autovacuum launcher
├─1963 postgres: stats collector
└─1964 postgres: logical replication launcher
Mar 17 13:34:07 linux systemd[1]: Starting PostgreSQL 14 database server...
Mar 17 13:34:07 linux postmaster[1955]: 2022-03-17 13:34:07.269 UTC [1955] LOG: redirecting log output to logging collector process
Mar 17 13:34:07 linux postmaster[1955]: 2022-03-17 13:34:07.269 UTC [1955] HINT: Future log output will appear in directory "log".
Mar 17 13:34:07 linux systemd[1]: Started PostgreSQL 14 database server.
Connect to PostgreSQL Server
By default, PostgreSQL listens on port 5432. You can check it with the following command:
ss -antpl | grep -i postmaster
You will get the following output:
LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* users:(("postmaster",pid=1955,fd=7))
LISTEN 0 128 [::1]:5432 [::]:* users:(("postmaster",pid=1955,fd=6))
Now, connect to the PostgreSQL localy using the following command:
sudo -u postgres psql
The following output will appear:
psql (14.0)
Type "help" for help.
postgres=#
To create a database, run the following command:
CREATE DATABASE testdb;
To list all PostgreSQL databases, run the following command:
\l
You should see all databases in the following output:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
To switch the database to testdb, run the following command:
\c testdb
To create a table named students, run the following command:
CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR);
Now, list all tables using the following command:
\dt
You should see the following output:
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | postgres
(1 row)
To exit from the PostgreSQL shell, run the following command:
exit
You have successfully installed PostgreSQL server on CentOS Stream 9.
Install PostgreSQL on CentOS Stream 9 Server Conclusion
In this guide, you learned what PostgreSQL is with it’s main pros and benefits and you know how to install PostgreSQL server on CentOS Stream 9 server. In addition you also learned how to create a database and user in PostgreSQL. You can now use PostgreSQL as a database backend in any web based applications.
