PostgreSQL 14 is an open-source relational database management system renowned for its robustness, scalability, and extensive feature set. One of the key features introduced in PostgreSQL 14 was the ability to handle incremental sorting, which significantly improved query performance for specific workloads. This release also enhanced parallel processing, vacuuming, and JSON processing, making it a strong choice for enterprise and development environments. Although it is a few versions behind the latest release, PostgreSQL 14 remains a reliable and widely supported option.
To install PostgreSQL 14 on Fedora 40 or 39 using the command-line terminal, you can import and install directly from the PostgreSQL RPM builds. This method ensures you have the latest version and makes future upgrades straightforward.
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 --refreshImport 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
Note: Remember to import the correct version for your Fedora distribution version:
For Fedora 40, use this command:
sudo dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/F-40-x86_64/pgdg-fedora-repo-latest.noarch.rpm
For Fedora 39, the command is slightly different:
sudo dnf install http://apt.postgresql.org/pub/repos/yum/reporpms/F-39-x86_64/pgdg-fedora-repo-latest.noarch.rpmDisable 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 postgresqlFinalize PostgreSQL 14 Installation
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, you can install some common-use additional modules, binaries, and libraries.
sudo dnf install postgresql14-libs postgresql14-odbc postgresql14-plperl postgresql14-plpython3 postgresql14-pltcl postgresql14-tcl postgresql14-contrib postgresql14-llvmjitInitialize PostgreSQL 14 Database
Once installed, you must run the following command to initialize the database; failing to do this will prevent PostgreSQL from functioning.
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
Enable PostgreSQL 14 Systemd Service
By default, PostgreSQL is not activated. Use the following command to start the service immediately and on system boot.
sudo systemctl enable postgresql-14 --nowVerify PostgreSQL 14 Installation
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
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 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-14Starting PostgreSQL 14 Server
To start the PostgreSQL service, especially after a stop or initial installation, use the following command:
sudo systemctl start postgresql-14Restarting PostgreSQL 14 Server
The restart command is useful if you need to apply new configurations or reset the PostgreSQL service. It stops and then starts the service in one action:
sudo systemctl restart postgresql-14Reloading PostgreSQL 14 Server
The reload command is ideal for applying configuration changes without stopping the database. It refreshes the service without interrupting the database operation:
sudo systemctl reload postgresql-14Checking 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-14Configure PostgreSQL 14
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 postgresEntering 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:
psqlAlternative 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 psqlThis command is efficient for quick interactions with the database as it bypasses additional terminal commands.
To exit, as with the first method, type exit.
exitCreate 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 psqlThen, 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.
exitConfigure Firewalld for PostgreSQL 14
Establishing Firewalld Rules for PostgreSQL 14
Securing PostgreSQL involves more than just installing and running the service; it’s crucial to configure network access controls effectively. This secures the database and 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=postgresThis 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.4Replace 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.5Repeat this command for each IP address.
Managing Port Access for PostgreSQL 14
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 --reloadThis 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=postgresThis command displays all the active rules in the ‘postgres’ zone, allowing for verification of the setup.exp
Remote Access Configuration for PostgreSQL 14
Setting Up Listening Interfaces for Remote Access
Modifying the PostgreSQL Configuration
To enable remote access to PostgreSQL, adjusting the interface settings in the postgresql is necessary.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-14Confirming Listening Ports
Use the ss utility to verify that PostgreSQL is listening on the specified ports:
ss -nlt | grep 5432If 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 particular 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-14Verify the effective settings in pg_hba.conf using:
cat /var/lib/pgsql/14/data/pg_hba.confConfigure SELinux for PostgreSQL 14
When configuring PostgreSQL 14 on Fedora, it’s essential to set up SELinux (Security-Enhanced Linux) properly. SELinux adds a 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 temporarily setting SELinux to ‘Permissive’ mode. This mode allows operations that would be blocked under ‘Enforcing’ mode but log them for review:
sudo setenforce 0Monitoring 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 1This 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.ppReplace 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
With PostgreSQL 14 successfully installed on your Fedora system, you can leverage its advanced features and robust performance for your database needs. Regularly check for updates and future upgrades from the PostgreSQL RPM builds to keep your system up-to-date.
For more information on using PostgreSQL, visit the official documentation.