How to Install PostgreSQL 14 on Fedora 40/39/38 Linux

This guide will demonstrate how to install PostgreSQL 14 on Fedora Linux using the command-line terminal and the PostgreSQL RPM Repository, ensuring access to the latest version and facilitating easy upgrades in the future.

PostgreSQL 14, released on September 30, 2021, marks a significant step in the evolution of open-source relational database systems. Primarily known for its robustness, scalability, and performance, PostgreSQL caters to a wide array of applications across various industries. As the world increasingly relies on complex data management, PostgreSQL 14 emerges as a pivotal tool for database administrators, developers, and users alike. The slated end-of-life for this version is November 12, 2026, ensuring long-term support and stability.

Key Features of PostgreSQL 14:

• Enhanced Performance: Improvements in query parallelism and vacuuming processes, boosting database efficiency.
• Robust Security: Advanced authentication methods and enhanced encryption options safeguarding data integrity.
• Improved Indexing: Introduction of additional indexing techniques, facilitating faster data retrieval.
• Extended SQL Conformance: Broader compatibility with SQL standards, easing the integration with various tools and technologies.
• Richer Data Types Support: Enhanced support for JSON, XML, and other complex data types, catering to modern data handling needs.
• Sophisticated Partitioning: More granular and flexible table partitioning capabilities, optimizing data organization and access.

As PostgreSQL continues to evolve, version 14 offers a comprehensive package that addresses both current needs and future trends in data management. The installation process on Fedora Linux, which we are about to delve into, is straightforward, yet it unlocks a world of possibilities for managing and utilizing data effectively. Let’s explore the technical aspects of getting PostgreSQL 14 up and running on your Fedora system.

Import PostgreSQL 14 RPM on Fedora

Update Fedora Before PostgreSQL 14 Installation

To begin, update your Fedora system to ensure all packages are current. This step helps prevent potential conflicts during the PostgreSQL installation. Execute the command below:

sudo dnf update --refresh

Import PostgreSQL RPM GPG Key Repository

Start by importing the PostgreSQL repository. This action guarantees access to the most recent PostgreSQL versions. Select and import the repository corresponding to your version of Fedora Linux.

Import PostgreSQL 14 for Fedora 39

For Fedora 39, use this command:

sudo dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/F-39-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Import PostgreSQL 14 for Fedora 38

For Fedora 38, the command is slightly different:

sudo dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/F-38-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Disable Default PostgreSQL Module (If Applicable)

Fedora’s default repositories often include PostgreSQL as a module. To prioritize installation from the PostgreSQL repository, it’s advisable to disable this default module. Use the following command to do so:

sudo dnf -qy module disable postgresql

Install PostgreSQL 14 via DNF Command on Fedora

Proceed with installing PostgreSQL 14

.Use this command to install the PostgreSQL 14 server and its documentation:

sudo dnf install postgresql14-server postgresql14-docs -y

Additionally, you can install the development package as follows.

sudo dnf install postgresql14-devel

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

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

Initialize PostgreSQL 14 Database on Fedora

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

Enable PostgreSQL 14 Systemd Service on Fedora

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

Verify PostgreSQL 14 on Fedora

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

systemctl status postgresql-14

Systemd Service Commands for PostgreSQL 14 on Fedora

Managing PostgreSQL 14 Service

The PostgreSQL database server operates as a systemd service named “postgresql-14” on Fedora. System administrators can manage this service using a set of systemd commands, which are vital for routine maintenance and troubleshooting.

Stopping PostgreSQL 14 Server

To stop the PostgreSQL service, perhaps for maintenance or configuration changes, use this command:

sudo systemctl stop postgresql-14

Starting PostgreSQL 14 Server

To start the PostgreSQL service, especially after a stop or initial installation, use the following command:

sudo systemctl start postgresql-14

Restarting PostgreSQL 14 Server

If you need to apply new configurations or reset the PostgreSQL service, the restart command is useful. It stops and then starts the service in one action:

sudo systemctl restart postgresql-14

Reloading PostgreSQL 14 Server

For applying configuration changes without stopping the database, the reload command is ideal. It refreshes the service without interrupting the database operation:

sudo systemctl reload postgresql-14

Checking PostgreSQL 14 Service Status

To verify the operational status of the PostgreSQL service, use this command. It provides information about the service’s state, including whether it’s active, idle, or experiencing issues:

systemctl status postgresql-14

Configure PostgreSQL 14 on Fedora Linux

Switching to the Postgres 14 Account

Accessing the Postgres 14 Account

During the installation of PostgreSQL, a user account named ‘postgres’ is automatically created. This account is associated with the default Postgres role, which possesses superuser privileges. To access the PostgreSQL database, switch to the ‘postgres’ account using the command:

sudo -i -u postgres

Entering the PostgreSQL 14 Prompt

Once switched to the ‘postgres’ user, access the PostgreSQL prompt directly by typing psql. Upon successful connection, the terminal prompt changes to postgres=#, indicating an active connection to the database.

To exit the PostgreSQL database, simply type:

psql

Alternative Method for Accessing PostgreSQL 14

Using Sudo for Direct Access

Alternatively, interact with the PostgreSQL database without switching accounts by using:

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 command is efficient for quick interactions with the database as it bypasses additional terminal commands.

To exit, as with the first method, type exit.

exit

Create User & Database with PostgreSQL 14

Creating a New User Role

Only superusers and roles with the createrole privilege can create new roles. To create a user, use the command:

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

Replace <name> with the desired username.

Creating a New Database

Next, create a PostgreSQL database for the newly created user:

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

Replace <namedb> with the desired database name.

Granting Permissions

To grant permissions to the new user on the new database, first connect to the PostgreSQL database as the superuser:

sudo -u postgres psql

Then, grant all privileges to the new user:

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

Replace <usernamedb> with the database name and <name> with the username. To exit, type exit.

exit

Configure Firewalld for PostgreSQL 14 on Fedora

Establishing Robust Firewalld Rules for PostgreSQL 14

Introduction to Firewalld Zone Configuration

Securing PostgreSQL involves more than just installing and running the service; it’s crucial to configure network access controls effectively. This not only secures the database but also ensures that only legitimate traffic reaches it. We’ll go through setting up firewalld, a dynamic firewall manager in Fedora, to safeguard PostgreSQL.

Creating a Firewalld Zone for PostgreSQL

First, create a dedicated zone in firewalld for PostgreSQL. This approach allows for more granular control and clarity in managing rules specific to PostgreSQL:

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

This command establishes a ‘postgres’ zone, isolating PostgreSQL-related firewall rules for easier management.

Restricting Access to Known IP Addresses

Allowing Access from a Single IP Address

For scenarios where only one client or server should access PostgreSQL:

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

Replace 1.2.3.4 with the specific IP address requiring access to the database.

Permitting a Subnet

In environments like corporate networks, allowing an entire subnet might be necessary:

sudo firewall-cmd --permanent --zone=postgres --add-source=192.168.1.0/24

Here, 192.168.1.0/24 represents the subnet. Adjust this value to match the desired network range.

Granting Access to Multiple Specific IPs

For scenarios with several known IPs requiring access:

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

Repeat this command for each individual IP address.

Managing Port Access for PostgreSQL

Configuring the Default PostgreSQL Port

For standard installations using the default port:

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

Customizing the Port Configuration

If PostgreSQL operates on a non-standard port (for example, 5433):

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

Alter the port number according to your specific PostgreSQL configuration.

Implementing and Verifying the New Firewall Rules

Applying the Changes

To activate the new rules, reload firewalld:

sudo firewall-cmd --reload

This step ensures that the new configurations take effect immediately.

Checking the Configurations

Post-configuration, it’s prudent to review the rules set for the ‘postgres’ zone:

sudo firewall-cmd --list-all --zone=postgres

This command displays all the active rules in the ‘postgres’ zone, allowing for verification of the setup.exp

Remote Access Configuration for PostgreSQL 14 on Fedora

Setting Up Listening Interfaces for Remote Access

Modifying the PostgreSQL Configuration

For enabling remote access to PostgreSQL, it’s necessary to adjust the interface settings in the postgresql.conf file. This process allows PostgreSQL to accept connections from various sources.

Ensure FirewallD settings are in place to permit remote access, as outlined in the preceding sections.

Accessing the Configuration File

To modify PostgreSQL 14’s configuration, use the nano text editor:

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

Editing the Listening Address

In the “Connection Settings” section, change listen_addresses from 'localhost' to your requirements:

• Listen on All Interfaces: To accept connections from any source, set listen_addresses to '*'.

listen_addresses = '*'

• Listen on a Specific Interface: Specify an IP address to restrict connections to a particular interface.

listen_addresses = '192.168.1.100'

After editing, save the file (Ctrl + O, then Enter) and exit (Ctrl + X).

Restarting PostgreSQL 14 Service

Apply the changes by restarting the PostgreSQL service:

sudo systemctl restart postgresql-14

Confirming Listening Ports

Use the ss utility to verify that PostgreSQL is listening on the specified ports:

ss -nlt | grep 5432

If successful you should see the ports in your terminal port.

Advanced Remote Connection Settings in pg_hba.conf

Tailoring Access in the pg_hba.conf File

For fine-grained control over remote connections, the pg_hba.conf file offers various customization options.

Editing pg_hba.conf

Open the pg_hba.conf file:

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

Configuring Remote Access Rules

Allowing Specific Users and Databases

To limit access to a specific user and database from a certain IP address:

host    mydatabase    myuser    192.168.1.100/32    md5

Allowing a Subnet

For broader access, such as an entire subnet:

host    all    all    192.168.1.0/24    md5

Using Different Authentication Methods

Choose an authentication method suitable for your environment. For password-based authentication:

host    all    all    0.0.0.0/0    md5

For trust authentication (note the security risks):

host    all    all    0.0.0.0/0    trust

After configuring the desired rules, save and exit the editor.

Applying and Verifying Changes

Restart PostgreSQL to implement the new configurations:

sudo systemctl restart postgresql-14

Verify the effective settings in pg_hba.conf using:

cat /var/lib/pgsql/14/data/pg_hba.conf

Best Practices for Configuring Remote Access in PostgreSQL 14

When configuring remote access in PostgreSQL 14, adhering to best practices is crucial for maintaining a secure and efficient database environment. The configuration involves precise edits to the postgresql.conf and pg_hba.conf files.

Here are key guidelines to follow:

• Limit Listening Interfaces: In postgresql.conf, restrict listen_addresses to specific interfaces or subnets where possible. Listening on all interfaces ('*') increases exposure and potential security risks.
• Specify Allowed IPs: In pg_hba.conf, explicitly define which IP addresses or ranges are permitted to connect. This minimizes the chance of unauthorized access.
• User and Database Specific Rules: Rather than allowing all users and databases (all), specify which users can access which databases. This granular approach enhances security.
• Choose Secure Authentication Methods: Opt for secure authentication methods like MD5 or SCRAM-SHA-256, especially when allowing access from external networks. Avoid using ‘trust’ authentication for remote connections.
• Regularly Review and Update Configurations: Security threats evolve, and so should your database configurations. Regularly review and update your settings to ensure they align with current best practices.
• Use SSL for Encrypted Connections: If possible, configure PostgreSQL to use SSL, adding an additional layer of security for data in transit.
• Monitor and Log Access: Keep an eye on access logs to spot any unusual activity or attempted breaches. Prompt detection can prevent potential security incidents.

Configure SELinux for PostgreSQL 14 on Fedora

Configure SELinux for PostgreSQL 14 on Fedora

If SELinux is disabled as you do not plan to use it on your Fedora system, this section can be skipped.

Understanding SELinux Configuration for PostgreSQL

When configuring PostgreSQL 14 on Fedora, it’s essential to properly set up SELinux (Security-Enhanced Linux). SELinux adds an additional layer of security by enforcing access control policies. Misconfiguration can lead to common issues like access denials or service disruptions.

Setting SELinux to Permissive Mode for Troubleshooting

Temporarily Adjusting SELinux Mode

If you encounter issues with PostgreSQL starting or functioning correctly, consider setting SELinux to ‘Permissive’ mode temporarily. This mode allows operations that would be blocked under ‘Enforcing’ mode, but logs them for review:

sudo setenforce 0

Monitoring Logs for AVC Denials

Check the SELinux logs for AVC (Access Vector Cache) denials:

sudo restorecon -Rv /var/lib/pgsql/14/data/

This command helps identify SELinux policies preventing PostgreSQL from functioning correctly.

Configuring SELinux Policies for PostgreSQL 14

Restoring Default SELinux Context

To ensure files have the correct SELinux context, use the restorecon command. Incorrect contexts on PostgreSQL directories or files can cause access issues:

sudo setsebool -P postgresql_can_rnetwork 1

This command enables the postgresql_can_rnetwork boolean, allowing network connections.

Advanced SELinux Configuration

Customizing SELinux Policies

In more complex setups, such as when PostgreSQL interacts with other services or custom ports, creating custom SELinux policies may be necessary. Utilize the audit2allow tool to generate custom policy modules based on specific needs.

Applying Custom Policies

After creating custom policies, apply them using:

sudo semodule -i my_postgresql.pp

Replace my_postgresql.pp with the name of your policy file.

Verifying SELinux Settings

Checking SELinux Status

Confirm the SELinux status to ensure it is set correctly for your PostgreSQL installation:

sestatus

Validating File Contexts

Verify that files and directories related to PostgreSQL have the appropriate SELinux contexts:

ls -Z /var/lib/pgsql/14/data/

Conclusion

Alright, that wraps up our journey through setting up PostgreSQL 14 on Fedora. We’ve covered the essentials, from initial installation to configuring firewalld and SELinux for top-notch security. Remember, the key is keeping things updated and double-checking your settings, especially when it comes to security with firewalld and SELinux. And hey, don’t forget to back up regularly – it’s a lifesaver. With these tips and tricks in your toolkit, you’re all set to run a smooth, secure PostgreSQL operation.

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