How To Install PostgreSQL on CentOS Stream 10
In this tutorial, we will show you how to install PostgreSQL on CentOS Stream 10. PostgreSQL is renowned for its reliability, feature robustness, and performance in handling various workloads. Version 17 continues this tradition with improvements in indexing, query optimization, and security features. Whether you’re a database administrator, developer, or system engineer, this guide will help you set up PostgreSQL 17 on your CentOS Stream 10 system with ease.
Prerequisites
Before we dive into the installation process, let’s ensure you have everything needed to successfully set up PostgreSQL 17:
System Requirements
- A CentOS Stream 10 system with at least 2GB RAM and 20GB of free disk space
- Root or sudo privileges on your system
- A stable internet connection for downloading packages
Preparation Steps
First, update your system to ensure you have the latest packages:
sudo dnf update -yNext, install the necessary development tools:
sudo dnf groupinstall "Development Tools" -yInstallation Process
Now that we’ve prepared our system, let’s proceed with the PostgreSQL 17 installation.
Adding PostgreSQL Repository
CentOS Stream 10 doesn’t include PostgreSQL 17 in its default repositories, so we need to add the official PostgreSQL repository:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpmAfter adding the repository, disable the default PostgreSQL module to avoid conflicts:
sudo dnf -qy module disable postgresqlInstalling PostgreSQL Server
With the repository set up, we can now install PostgreSQL 17:
sudo dnf install -y postgresql17-server postgresql17-contribThis command installs the PostgreSQL server and additional contributed modules that provide extra functionality.
Database Initialization
After installation, we need to initialize the database cluster:
sudo /usr/pgsql-17/bin/postgresql-17-setup initdbThis command creates a new PostgreSQL database cluster, which is a collection of databases managed by a single server instance.
Configuration Steps
With PostgreSQL installed and initialized, let’s configure it for optimal performance and security.
Service Management
Start the PostgreSQL service and enable it to launch on system boot:
sudo systemctl start postgresql-17
sudo systemctl enable postgresql-17Verify that the service is running:
sudo systemctl status postgresql-17Basic Security Setup
By default, PostgreSQL uses “peer” authentication for local connections. For improved security, let’s modify this to use password authentication:
sudo nano /var/lib/pgsql/17/data/pg_hba.confFind the lines that look like this:
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 ident
host all all ::1/128 ident
Change “peer” and “ident” to “md5”:
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
Save the file and exit the editor. Then, restart PostgreSQL to apply the changes:
sudo systemctl restart postgresql-17Post-Installation Tasks
Now that PostgreSQL 17 is installed and configured, let’s perform some essential post-installation tasks.
Accessing PostgreSQL
To access the PostgreSQL prompt, switch to the postgres user and start the psql command-line tool:
sudo -i -u postgres
psqlYou should now see the PostgreSQL prompt:
postgres=#Creating Initial Database
Let’s create a new database and user:
CREATE DATABASE mydb;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;Replace ‘mydb‘, ‘myuser‘, and ‘mypassword‘ with your preferred names and a strong password.
Verification and Testing
To ensure everything is working correctly, let’s perform some basic tests.
Connection Testing
Exit the PostgreSQL prompt by typing \q and pressing Enter. Then, try connecting to the new database:
psql -d mydb -U myuserIf prompted for a password, enter the one you set earlier.
Basic SQL Commands
Once connected, try some basic SQL commands:
CREATE TABLE test (id serial PRIMARY KEY, name VARCHAR (50) NOT NULL);
INSERT INTO test (name) VALUES ('Test Entry');
SELECT * FROM test;If these commands execute without errors, your PostgreSQL 17 installation is working correctly.
Troubleshooting Guide
If you encounter issues during installation or configuration, here are some common problems and their solutions:
Service Won’t Start
If the PostgreSQL service fails to start, check the system logs:
sudo journalctl -u postgresql-17Look for any error messages that might indicate the problem.
Connection Refused
If you can’t connect to the database, ensure that PostgreSQL is listening on the correct interfaces. Check the postgresql.conf file:
sudo nano /var/lib/pgsql/17/data/postgresql.confEnsure the listen_addresses line is set to ‘*’ for all interfaces, or to a specific IP if you prefer:
listen_addresses = '*'Remember to restart PostgreSQL after making changes:
sudo systemctl restart postgresql-17Best Practices and Optimization
To get the most out of your PostgreSQL 17 installation, consider these best practices:
Regular Backups
Implement a robust backup strategy using pg_dump or pg_basebackup. For example:
pg_dump mydb > mydb_backup.sqlPerformance Tuning
Adjust PostgreSQL configuration parameters based on your system resources and workload. Key parameters to consider include:
- shared_buffers
- effective_cache_size
- work_mem
- maintenance_work_mem
Always test changes in a non-production environment before applying them to your live system.
Security Recommendations
- Use strong, unique passwords for all database users
- Implement SSL for encrypted connections
- Regularly update PostgreSQL to the latest minor version for security patches
Congratulations! You have successfully installing PostgreSQL. Thanks for using this tutorial to install PostgreSQL free and open-source relational database management system (RDBMS) on CentOS Stream 10 system. For additional help or useful information, we recommend you check the official PostgreSQL website.
