Learn how to install PostgreSQL on Rocky Linux 9 with our step-by-step guide. Set up this powerful, open-source relational database system to manage your data efficiently on Rocky Linux. #centlinux #linux #postgres
Table of Contents
What is PostgreSQL?
PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.
PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Windows, Linux, FreeBSD, and OpenBSD. (Source: Wikipedia)
Read Also:
How to install PostgreSQL on CentOS 8
How to install PostgreSQL on CentOS 7
MongoDB vs PostgreSQL
MongoDB and PostgreSQL are two popular database management systems, but they serve different needs and have distinct characteristics. Here’s a comparison to help you understand their differences and choose the best one for your requirements:
MongoDB
Type:
- NoSQL database (document-oriented)
Data Model:
- Uses BSON (Binary JSON) to store data in flexible, JSON-like documents.
- Schema-less, allowing for easy modification and scalability.
Use Cases:
- Ideal for applications that require high performance, scalability, and flexibility.
- Suitable for handling large volumes of unstructured or semi-structured data.
- Commonly used in web applications, content management systems, and big data applications.
Strengths:
- Scalability: Horizontally scalable using sharding, making it suitable for handling large datasets across multiple servers.
- Flexibility: Schema-less design allows for dynamic data structures, making it easy to adapt to changing requirements.
- Speed: Optimized for read-heavy and write-heavy workloads.
Weaknesses:
- Complex Transactions: Limited support for multi-document ACID transactions (although this has improved in recent versions).
- Consistency: Can favor eventual consistency over strong consistency, depending on configuration.
PostgreSQL
Type:
- Relational database (SQL-based)
Data Model:
- Uses tables with predefined schemas, supporting complex queries and transactions.
- Strong adherence to ACID properties (Atomicity, Consistency, Isolation, Durability).
Use Cases:
- Ideal for applications that require complex queries, data integrity, and transactional consistency.
- Suitable for traditional business applications, financial systems, and data warehousing.
Strengths:
- ACID Compliance: Ensures data integrity and reliability with robust transaction support.
- Advanced Features: Supports complex queries, full-text search, indexing, and extensions like PostGIS for geographic data.
- Data Integrity: Strong schema enforcement and data validation.
Weaknesses:
- Scalability: Primarily vertically scalable, although recent versions have improved horizontal scaling capabilities.
- Flexibility: Schema-based design can be less flexible than NoSQL databases for certain types of data.
Feature Comparison
| Feature | MongoDB | PostgreSQL |
|---|---|---|
| Type | NoSQL (Document-oriented) | SQL (Relational) |
| Schema | Schema-less | Schema-based |
| Transactions | Limited multi-document ACID | Full ACID compliance |
| Query Language | MongoDB Query Language (MQL) | SQL |
| Scalability | Horizontal (sharding) | Primarily vertical, some horizontal |
| Performance | High performance for large datasets | Efficient for complex queries and transactions |
| Flexibility | High, with dynamic schemas | Moderate, with fixed schemas |
| Use Cases | Web apps, big data, content management | Business apps, financial systems, data warehousing |
Conclusion
- Choose MongoDB if you need a flexible, scalable, and high-performance database for handling large volumes of unstructured or semi-structured data, especially in web applications and big data scenarios.
- Choose PostgreSQL if you need a reliable, robust, and ACID-compliant relational database for applications that require complex queries, data integrity, and strong transactional support, such as business applications and financial systems.
Both databases have their strengths and are suited to different types of projects. The best choice depends on your specific use case, data structure, and requirements for performance and scalability.
Recommended Online Training: PostgreSQL Database Administration on Windows/Linux
Environment Specification:
We are using a minimal Rocky Linux 9 virtual machine with following specifications.
- CPU – 3.4 Ghz (2 cores)
- Memory – 2 GB
- Storage – 20 GB
- Operating System – Rocky Linux release 9.0 (Blue Onyx)
- Hostname – postgres-01.centlinux.com
- IP Address – 192.168.116.131/24
Update your Rocky Linux Server
It is a best practice to update your Linux operating system before performing any server configurations.
By using a SSH Client, connect with postgres-01.centlinux.com as root user.
Execute dnf command to refresh the yum cache of your Linux server.
# dnf makecache Rocky Linux 9 - BaseOS 1.2 kB/s | 3.6 kB 00:02 Rocky Linux 9 - AppStream 1.5 kB/s | 3.6 kB 00:02 Rocky Linux 9 - Extras 1.1 kB/s | 2.9 kB 00:02 Metadata cache created.
Now you can update your Rocky Linux server with the help of following dnf command.
# dnf update -y
The above command may update your Linux Kernel. If it does than reboot your Linux machine once to load the Latest Linux Kernel.
# reboot
After reboot, login as root user and check Linux Kernel and operating system versions.
# uname -r 5.14.0-70.26.1.el9_0.x86_64 # cat /etc/rocky-release Rocky Linux release 9.0 (Blue Onyx)
Install PostgreSQL Server on Rocky Linux 9
In Rocky Linux 9, PostgreSQL 13 is available in standard yum repositories that you can easily install by throwing a dnf command.
You can verify the version of available PostgreSQL server as follows.
# dnf info postgresql-server
Last metadata expiration check: 0:49:27 ago on Fri 23 Sep 2022 09:48:01 AM CDT.
Available Packages
Name : postgresql-server
Version : 13.7
Release : 1.el9_0
Architecture : x86_64
Size : 5.7 M
Source : postgresql-13.7-1.el9_0.src.rpm
Repository : appstream
Summary : The programs needed to create and run a PostgreSQL server
URL : http://www.postgresql.org/
License : PostgreSQL
Description : PostgreSQL is an advanced Object-Relational database management
: system (DBMS). The postgresql-server package contains the
: programs needed to create and run a PostgreSQL server, which will
: in turn allow you to create and maintain PostgreSQL databases.But, in this configuration guide, we are installing the PostgreSQL 14 on Rocky Linux 9.
PostgreSQL 14 is the latest PostgreSQL version at the time of this writing and is not available in standard yum repositories.
To setup Postgres 14 database, you need to install their official yum repository as follows.
# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Now, you can install Postgres 14 by using dnf command.
# dnf install -y postgresql14-server
After successful installation, you need to initialize your PostgreSQL database instance.
# /usr/pgsql-14/bin/postgresql-14-setup initdb Initializing database ... OK
Enable and start PostgreSQL database service.
# systemctl enable --now postgresql-14 Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
Check the status of your database service.
# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; ve>
Active: active (running) since Fri 2022-09-23 10:43:23 CDT; 20s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 5148 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${P>
Main PID: 5153 (postmaster)
Tasks: 8 (limit: 7495)
Memory: 16.6M
CPU: 64ms
CGroup: /system.slice/postgresql-14.service
├─5153 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─5154 "postgres: logger "
├─5156 "postgres: checkpointer "
├─5157 "postgres: background writer "
├─5158 "postgres: walwriter "
├─5159 "postgres: autovacuum launcher "
├─5160 "postgres: stats collector "
└─5161 "postgres: logical replication launcher "
Sep 23 10:43:23 postgres-01.centlinux.com systemd[1]: Starting PostgreSQL 14>
Sep 23 10:43:23 postgres-01.centlinux.com postmaster[5153]: 2022-09-23 10:43>
Sep 23 10:43:23 postgres-01.centlinux.com postmaster[5153]: 2022-09-23 10:43>
Sep 23 10:43:23 postgres-01.centlinux.com systemd[1]: Started PostgreSQL 14 >
Verify the version of installed database software by using psql command.
# psql -V psql (PostgreSQL) 14.5
Set Database Admin User Password:
Switch to postgres user and set DBA password as follows.
# su - postgres $ psql psql (14.5) Type "help" for help. postgres=# ALTER USER postgres WITH PASSWORD '123'; ALTER ROLE postgres=# q $ exit logout
Configure Network Access for PostgreSQL Server:
By default the Postgres service listens on the localhost network interface.
You can verify it by executing ss command at Linux bash prompt.
# ss -tulpn | grep 5432
tcp LISTEN 0 244 127.0.0.1:5432 0.0.0.0:* users:(("postmaster",pid=5153,fd=7))
tcp LISTEN 0 244 [::1]:5432 [::]:* users:(("postmaster",pid=5153,fd=6))To enable network access to your database server, you need to perform following configurations.
Open postgresql.conf file in vim text editor.
# vi /var/lib/pgsql/14/data/postgresql.conf
Search for following directive therein.
# listen_addresses = 'localhost'
And replace it with the following directive.
listen_addresses = '*'
Allow network clients to access database service in pg_hba.conf file.
# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/14/data/pg_hba.conf
Restart database service to apply changes.
# systemctl restart postgresql-14.service
Now, check the status of service again by executing ss command.
# ss -tulpn | grep 5432
tcp LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* users:(("postmaster",pid=5539,fd=6))
tcp LISTEN 0 244 [::]:5432 [::]:* users:(("postmaster",pid=5539,fd=7))You can see that, now the database service is listening on all network interfaces.
One last thing is to allow the service port in Linux firewall.
There is a preconfigured firewall service, postgresql, that you can use to allow PostgreSQL default port i.e 5432/tcp in Linux firewall.
# firewall-cmd --permanent --add-service=postgresql success # firewall-cmd --reload success
Install pgAdmin 4: PostgreSQL GUI
Although, you have setup PostgreSQL server on your Linux operating system. But it doesn’t provides a GUI based database management environment and you have to perform DBA tasks straight from the psql command prompt.
There are many third party web user interfaces are available for Postgres, but pgAdmin 4 is the most popular among them due to it’s rich database management features.
You can install pgAdmin 4 as follows or you can obtain an alternate download link from their official download page, if you are installing on some other Linux distribution.
# dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
pgAdmin 4 also requires some software packages from EPEL (Extra Packages for Enterprise Linux) yum repository. Therefore, you should install it first before moving forward with server configurations.
# dnf install -y epel-release
Build your yum cache for the newly installed repositories.
# dnf makecache Extra Packages for Enterprise Linux 9 - x86_64 139 kB/s | 10 MB 01:16 pgadmin4 283 B/s | 833 B 00:02 PostgreSQL common RPMs for RHEL / Rocky 9 - x86 61 B/s | 195 B 00:03 PostgreSQL 14 for RHEL / Rocky 9 - x86_64 102 B/s | 195 B 00:01 PostgreSQL 13 for RHEL / Rocky 9 - x86_64 77 B/s | 195 B 00:02 PostgreSQL 12 for RHEL / Rocky 9 - x86_64 102 B/s | 195 B 00:01 PostgreSQL 11 for RHEL / Rocky 9 - x86_64 76 B/s | 195 B 00:02 PostgreSQL 10 for RHEL / Rocky 9 - x86_64 96 B/s | 195 B 00:02 Rocky Linux 9 - BaseOS 1.9 kB/s | 3.6 kB 00:01 Rocky Linux 9 - AppStream 1.8 kB/s | 3.6 kB 00:02 Rocky Linux 9 - Extras 1.1 kB/s | 2.9 kB 00:02 Metadata cache created.
Now, you can install pgAdmin 4 software by using dnf command.
# dnf install -y pgadmin4
pgAdmin 4 configuration script requires semanage command to configure SELinux context for the software files. Therefore, you need to install policycoreutils-python-utils package before installing the Postgres web user interface.
# dnf install -y policycoreutils-python-utils
Execute following script to setup pgAdmin 4 web user interface.
# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address: ahmer@postgres-01.centlinux.com
Password:
Retype password:
pgAdmin 4 - Application Initialization
======================================
Creating storage and log directories...
Configuring SELinux...
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
Allow the HTTP server in Linux firewall by using firewall-cmd command.
# firewall-cmd --permanent --add-service=http success # firewall-cmd --reload success
Open URL http://postgres-01.centlinux.com/pgadmin4/ in a web browser.
Login as user that you have created during pgAdmin setup.
After successful login, you will redirected to the pgAdmin dashboard.
Currently, there are no PostgreSQL servers in the list. Click on “Add New Server” to add your database server.
At the “General” Tab, provide a name for your database server.
At the “Connection” Tab, provide the database user and the password to create a connection.
Click on “Save”.
Your PostgreSQL database server has been added in pgAdmin 4 web user interface.
After setup PostgreSQL Server you may also need to develop skills in PostgreSQL database administration. We recommend that you should attend an online training like SQL PostgreSQL at Udemy or read PostgreSQL 14 Administration Cookbook (PAID LINK) by Simon Riggs & Gianni Ciolli.
Video: How to install PostgreSQL Server on Rocky Linux 9
Final Thoughts
Congratulations on successfully installing PostgreSQL on Rocky Linux 9! With PostgreSQL set up, you now have a powerful and reliable relational database management system at your disposal. Use PostgreSQL’s advanced features and robust performance to manage your data effectively. Whether you’re developing applications, managing data warehouses, or running complex queries, PostgreSQL will support your needs with efficiency and stability. Keep exploring its capabilities to maximize the potential of your database system. If you need further assistance or custom solutions, feel free to check out my services on Fiverr here. Happy database management!
Excelent arcticle