How to Install PostgreSQL 14 on AlmaLinux 9

PostgreSQL is a robust, stable, and reliable open-source database management system that has been in use for over 20 years. It’s backed by an active community of developers who contribute their time and expertise to its development, resulting in higher levels of resilience, integrity, and correctness. PostgreSQL powers many web applications, including those from Google Earth Engine Geospatial data. Its feature set, performance, and stability make it an ideal choice for mission-critical applications. PostgreSQL’s support for SQL standard compliance ensures compatibility with various software tools. In addition, its extensibility features allow developers to add custom functionality to meet the needs of specific applications. As a result, PostgreSQL is well-suited for a wide range of workloads.

PostgreSQL 14 release contains many new features and improvements, including better performance, greater scalability, and improved security. Among the most notable changes are:

  • Adding support for materialized views can provide a significant performance boost for read-heavy workloads.
  • Enhancements to partitioning allow for better performance and easier management of large data sets.
  • The introduction of hash indexes can improve performance for certain types of queries.
  • Updates to the query optimizer can provide a significant performance boost for some types of queries.

The following tutorial will teach you how to install PostgreSQL 14 on AlmaLinux 9 using the command line terminal and some basic configuration and setup tips using the command line terminal.

Update AlmaLinux

First, update your system with the following command to ensure all packages are up-to-date to avoid conflicts.

sudo dnf update --refresh

Import PostgreSQL Repository

The first task is to import the repository directly from PostgreSQL, ensuring you always have the latest up-to-date version.



sudo dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y

Install PostgreSQL

Now that you have imported the stable or testing repository of PostgreSQL, you need to update your repository sources list to reflect the new addition with the following command.

sudo dnf install postgresql14-server postgresql14-docs -y

Additionally, you can install the development package as follows.

sudo dnf install postgresql14-devel -y

Lastly, below are some common use additional modules, binaries, and libraries that you can install.

sudo dnf install postgresql14-libs postgresql14-plperl postgresql14-plpython3 postgresql14-pltcl postgresql14-tcl postgresql14-contrib postgresql14-llvmjit

Once installed, you must run the following command to initialize the database, failure to do this will result in PostgreSQL not functioning.

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

By default, PostgreSQL does not come activated. Use the following command to start the service immediately and on system boot.

sudo systemctl enable postgresql-14 --now

Next, verify the status to ensure the software is installed and activated without errors using the following command.



systemctl status postgresql-14

Example output:

How to Install PostgreSQL 14 on AlmaLinux 9

Service Commands for PostgreSQL

The PostgreSQL database server runs as a service under the name “PostgreSQL,” which can be managed with the systemd with the following command examples.

Stop PostgreSQL server

sudo systemctl stop postgresql-14

Start PostgreSQL server

sudo systemctl start postgresql-14

Restart PostgreSQL server

sudo systemctl restart postgresql-14

Reload PostgreSQL server



sudo systemctl reload postgresql-14

Check PostgreSQL status:

systemctl status postgresql-14

Configure the PostgreSQL Server

Switching to Postgres account

Before you continue, note that only superusers and roles with create role privilege can create new roles in Postgres. Postgres’s user account associated with the default Postgres role was created during the installation.

To engage with the account, you can do the following commands:

sudo -i -u postgres

By entering the above, you can immediately access the PostgreSQL prompt by typing “psql”.

psql

Next, you will notice that the terminal has changed with “postgres=#” now being in the terminal command line. This means you have successfully connected to the database.

To exit the Postgres database, you can do this by typing the following:



exit

Alternative to switching Postgres account

An alternative way to interact with the Postgres database without changing user accounts is to use a sudo command to connect directly. You can do this by typing:

sudo -u postgres psql

This is ideal to use more, as it saves time logging you directly without having to do extra terminal bash commands.

As the first option, you can exit by typing the following to exit the terminal.

exit

Create User & Database

Now you can create user roles if you wish. Only superusers and roles with “createrole” privilege can create new roles.

Create a new user; type the following.

sudo su - postgres -c "createuser <name>"

Now you can create a PostgreSQL database for the new user you created:



sudo su - postgres -c "createdb <namedb>"

To finish off, you need to switch to the superuser Postgres account to grant permission to the new database.

Connect to Postgres superuser account

sudo -u postgres psql

Grant access to <username database> to <username>

GRANT ALL PRIVILEGES ON DATABASE <usernamedb> TO <name>;

Example:

How to Install PostgreSQL 14 on AlmaLinux 9

Once done, to exit, type the following

exit

Configure FirewallD

By default, no rules are set up for PostgreSQL. This means you will need to create rules essential to stop attacks, failure to secure will lead to issues down the track, so do not skip this unless you have other means to protect your installation.



First, add a new dedicated zone firewalld policy.

sudo firewall-cmd --permanent --new-zone=postgres

Next, specify the allowed IP addresses permitted to access.

sudo firewall-cmd --permanent --zone=postgres --add-source=1.2.3.4

Replace 1.2.3.4 with the IP address that will be added to the allow list.

Once you have finished adding the IP addresses, open the port. By default, this is TCP port 5432.

sudo firewall-cmd --permanent --zone=postgres --add-port=5432/tcp

Note that you can change the default port in your configuration file if you change the firewall port open rule above to the new value.

After running those commands, reload the firewall to implement the new rules.



sudo firewall-cmd --reload

Remote Access to PostgreSQL

If you require default access to PostgreSQL, this can be quickly done by changing the default listening to the local interface of (127.0.0.1) to an IP, subnet, or all interfaces in the configuration file.

Remember to ensure that FirewallD has been set up correctly and does not block remote access as per the previous section.

The tutorial has one of the latest versions installed, which is 14. This opens up the “postgresql.conf” file using the nano text editor.

sudo nano /var/lib/pgsql/14/data/postgresql.conf

Now scroll down until you find “Connection Settings” and change the (listen_addresses = ‘localhost’) to the <IP> address. Most would opt to listen to all interfaces, especially when running in multiple servers requiring connecting to the PostgreSQL database by changing localhost to (*).

Example:

How to Install PostgreSQL 14 on AlmaLinux 9

Save the file (CTRL+O), exit (CTRL+X), and restart your PostgreSQL instance.



sudo systemctl restart postgresql-14

Users can do this with the ss command to check the changes to ports.

ss -nlt | grep 5432

Example output:

How to Install PostgreSQL 14 on AlmaLinux 9

For further customization and securing PostgreSQL, you can configure the server to accept remote connections by editing the “pg_hba.conf” file using the nano text editor.

sudo nano /var/lib/pgsql/14/data/pg_hba.conf

From this point, you can set up various options, such as changing “all” to a specific username and database along with the address and authentication methods.

How to Install PostgreSQL 14 on AlmaLinux 9

Comments and Conclusion

As a result of its stability, reliability, and resilience, PostgreSQL is gaining popularity as the go-to database management system for web applications. Additionally, its open-source nature allows businesses to use it at no cost. If you’re looking for an alternative to proprietary database software or are interested in using geospatial data in your web application, consider using PostgreSQL.

For more information on using PostgreSQL, visit the official documentation.




Not what you were looking for? Try searching for additional tutorials.

Leave a Comment