Why use PostgreSQL on CentOS Stream 9?
I recently finished migrating a series of systems from CentOS 7 to CentOS Stream 9. Through that process, I realized that getting PostgreSQL to “just run” is easy, but making it “run well” in production is another story. The difference lies in how you handle SELinux, Firewalld, and access permissions.
PostgreSQL has always been a reliable “workhorse” for systems requiring high reliability. Combined with CentOS Stream 9, we get a modern platform with a new kernel and strict security. This article draws from 6 months of real-world experience managing database clusters for the Fintech sector—where a small mistake in a SELinux rule can bring the whole system to a halt.
Quick Start: Install PostgreSQL 16 in 5 Minutes
If you need to quickly spin up an instance for testing, use the commands below. I’ve chosen version 16 as it is currently the most stable release.
# 1. Add the official PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 2. Disable the default postgresql module to avoid conflicts
sudo dnf -qy module disable postgresql
# 3. Install PostgreSQL 16 server
sudo dnf install -y postgresql16-server
# 4. Initialize the database
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
# 5. Enable and start the service
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16Verify the status using: systemctl status postgresql-16. If you see active (running) in green, you’ve successfully completed the first step.
Real-World Configuration for Production
1. Securing the postgres Account
By default, PostgreSQL uses the peer authentication mechanism for local connections, which fully trusts the system user. For professional administration, you should set a separate password for the postgres database user.
sudo -u postgres psql
# Inside the psql interface, type:
ALTER USER postgres WITH PASSWORD 'your_super_hard_password';
\q2. Firewalld Configuration: Don’t disable it, open the right ports
Many people simply disable the firewall to avoid hassle. On CentOS Stream 9, this is a fatal mistake. Instead, only open port 5432 to ensure security.
# Add the postgresql service to the public zone
sudo firewall-cmd --permanent --add-service=postgresql
# Reload the configuration to apply changes
sudo firewall-cmd --reload3. Handling SELinux: The Sysadmin’s Nightmare
SELinux is often the reason a database fails to start or cannot write logs. This error most commonly occurs when you change the Data Directory to an external drive, such as /data/pgsql.
If you change the default path, run the following commands to update the SELinux context:
sudo semanage fcontext -a -t postgresql_db_t "/data/pgsql(/.*)?"
sudo restorecon -Rv /data/pgsqlMy hard-earned advice: Always keep SELinux in Enforcing mode. Don’t switch to Permissive just because you’re too lazy to fix errors. Complying with SELinux helps your server withstand automated vulnerability scans.
Enabling Remote Access
By default, PostgreSQL only listens for connections from localhost. To allow access from other application servers, you need to edit two configuration files.
Editing postgresql.conf
Open the file at: /var/lib/pgsql/16/data/postgresql.conf. Find the listen_addresses line and change it to:
listen_addresses = '*' Pro tip: Using * will listen on all network interfaces. If your server has a private IP for an internal network, you should specify that IP to increase security.
Editing pg_hba.conf
This file controls login permissions. Add this line to the end of /var/lib/pgsql/16/data/pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.1.0/24 scram-sha-256I prefer using scram-sha-256 as it is more secure than the old md5. Don’t forget to replace 192.168.1.0/24 with your internal IP range. Finally, restart the service:
sudo systemctl restart postgresql-164 Tips for Smoother Database Performance
Here are the tweaks that help me avoid those emergency midnight calls:
- Increase Shared Buffers: PostgreSQL’s default is very low (128MB). For a server with 8GB RAM, I usually push it to 2GB (approx. 25% of RAM). This change can significantly boost data read speeds.
- Hunt for Slow Queries: In
postgresql.conf, setlog_min_duration_statementto around500ms. Any statement slower than this will be logged, making it easier for you to optimize later. - Automated Backups: Never trust the durability of a hard drive. Use a cronjob to run
pg_dumpevery night, then push the backup file to another server or S3 storage. - Performance Monitoring: Install the
pg_stat_statementsextension. It is the most powerful tool for identifying which queries are consuming the most CPU.
Deploying PostgreSQL on CentOS Stream 9 isn’t difficult if you master the security rules. The key is to work with the operating system rather than trying to disable its security layers. Good luck building a stable database system!
