BLOG POSTS

MangoHost Blog / How to Install and Use PostgreSQL on latest CentOS

How to Install and Use PostgreSQL on latest CentOS

PostgreSQL is one of the most advanced open-source relational database management systems available today, offering excellent performance, ACID compliance, and extensive feature sets that make it ideal for enterprise applications, web development, and data analytics. This guide will walk you through the complete process of installing and configuring PostgreSQL on the latest CentOS distribution, covering everything from basic installation to advanced configuration, performance optimization, and troubleshooting common issues you’re likely to encounter in production environments.

How PostgreSQL Works on CentOS

PostgreSQL operates as a multi-process architecture where each client connection spawns a separate backend process. On CentOS, PostgreSQL integrates seamlessly with systemd for service management and uses the standard file system hierarchy for data storage, typically under /var/lib/pgsql/. The database server listens on port 5432 by default and uses a combination of host-based authentication (HBA) and role-based access control for security.

The PostgreSQL installation on CentOS includes several key components: the main server daemon (postgres), command-line utilities (psql, pg_dump, etc.), and client libraries. The service runs under a dedicated system user called ‘postgres’ for security isolation, and all database operations are performed through this user context.

Step-by-Step Installation Guide

Before starting the installation, ensure your CentOS system is up to date and you have sudo privileges. Here’s the complete installation process:

Update System and Install Prerequisites

sudo dnf update -y
sudo dnf install -y wget curl vim

Install PostgreSQL from Official Repository

The default CentOS repositories often contain older PostgreSQL versions. To get the latest version, we’ll use the official PostgreSQL repository:

# Install the PostgreSQL official repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL 15 (latest stable version)
sudo dnf install -y postgresql15-server postgresql15 postgresql15-contrib

# Initialize the database
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# Enable and start PostgreSQL service
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

# Check service status
sudo systemctl status postgresql-15

Configure PostgreSQL

After installation, you need to configure PostgreSQL for optimal performance and security:

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL prompt
psql

# Set password for postgres user
\password postgres

# Exit PostgreSQL prompt
\q

# Exit postgres user session
exit

Configure Authentication and Network Access

Edit the PostgreSQL configuration files to allow connections:

# Edit postgresql.conf
sudo vim /var/lib/pgsql/15/data/postgresql.conf

Find and modify these lines:

listen_addresses = 'localhost'  # Change to '*' for all interfaces
port = 5432
max_connections = 100
shared_buffers = 256MB  # Adjust based on available RAM

Configure client authentication in pg_hba.conf:

# Edit pg_hba.conf
sudo vim /var/lib/pgsql/15/data/pg_hba.conf

Add these lines for local and network access:

# Local connections
local   all             all                                     md5
# IPv4 local connections
host    all             all             127.0.0.1/32            md5
# Allow connections from your network (adjust IP range as needed)
host    all             all             10.0.0.0/8              md5

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql-15

Configure Firewall

Open PostgreSQL port in firewall:

sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Basic PostgreSQL Usage and Administration

Creating Databases and Users

# Connect as postgres user
sudo -u postgres psql

# Create a new database
CREATE DATABASE myapp_db;

# Create a new user
CREATE USER myapp_user WITH PASSWORD 'secure_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

# List databases
\l

# List users
\du

# Connect to specific database
\c myapp_db

# Exit
\q

Essential PostgreSQL Commands

Here are the most commonly used PostgreSQL commands for daily administration:

# Backup database
sudo -u postgres pg_dump myapp_db > backup.sql

# Restore database
sudo -u postgres psql myapp_db < backup.sql

# Check PostgreSQL version
sudo -u postgres psql -c "SELECT version();"

# Show running queries
sudo -u postgres psql -c "SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state = 'active';"

# Check database sizes
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

Real-World Use Cases and Examples

PostgreSQL excels in various scenarios. Here are some practical examples:

Web Application Backend

PostgreSQL is perfect for web applications requiring complex queries and data integrity:

# Example: E-commerce database schema
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    inventory_count INTEGER DEFAULT 0,
    category_id INTEGER
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Create indexes for better performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Analytics and Reporting

PostgreSQL's advanced features make it excellent for data analytics:

# Example: Sales analytics query
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as total_orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

PostgreSQL vs Alternative Databases

Here's a comprehensive comparison of PostgreSQL with other popular database systems:

Feature	PostgreSQL	MySQL	MongoDB	Redis
Data Model	Relational + JSON	Relational	Document	Key-Value
ACID Compliance	Full	Full	Limited	Limited
Performance (OLTP)	Excellent	Very Good	Good	Excellent
Complex Queries	Excellent	Good	Limited	Basic
Scalability	Vertical + Horizontal	Vertical + Horizontal	Horizontal	Horizontal
Memory Usage	Moderate	Low	High	High
Learning Curve	Moderate	Easy	Moderate	Easy

Performance Optimization and Best Practices

Memory and Connection Tuning

Optimize PostgreSQL performance by adjusting these key parameters in postgresql.conf:

# Memory settings (adjust based on available RAM)
shared_buffers = '256MB'          # 25% of total RAM
effective_cache_size = '1GB'      # 75% of total RAM
work_mem = '4MB'                  # Per-query memory
maintenance_work_mem = '64MB'     # Maintenance operations

# Connection settings
max_connections = 100             # Adjust based on application needs
superuser_reserved_connections = 3

# Checkpoint settings
checkpoint_completion_target = 0.7
wal_buffers = '16MB'
random_page_cost = 1.1            # For SSD storage

Query Optimization

Use these techniques to optimize query performance:

# Analyze query performance
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

# Update table statistics
ANALYZE orders;

# Vacuum to reclaim space
VACUUM ANALYZE orders;

# Create partial indexes for better performance
CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'active';

Security Best Practices

Always use strong passwords for database users
Limit network access using pg_hba.conf
Enable SSL/TLS for remote connections
Regularly update PostgreSQL to the latest version
Use connection pooling to limit resource usage
Implement proper backup and recovery procedures

Common Issues and Troubleshooting

Connection Issues

If you can't connect to PostgreSQL, check these common issues:

# Check if PostgreSQL is running
sudo systemctl status postgresql-15

# Check listening ports
sudo netstat -tlnp | grep 5432

# Test local connection
sudo -u postgres psql -c "SELECT 1;"

# Check logs for errors
sudo tail -f /var/lib/pgsql/15/data/log/postgresql-*.log

Performance Issues

For slow queries and performance problems:

# Enable query logging (in postgresql.conf)
log_statement = 'all'
log_duration = on
log_min_duration_statement = 1000  # Log queries taking > 1 second

# Check for blocking queries
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Disk Space Issues

# Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size 
FROM pg_database ORDER BY pg_database_size(datname) DESC;

# Check table sizes
SELECT schemaname,tablename,pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 10;

# Clean up with VACUUM FULL (use carefully in production)
VACUUM FULL ANALYZE;

Advanced Configuration and Monitoring

Setting Up Connection Pooling

Install and configure PgBouncer for connection pooling:

# Install PgBouncer
sudo dnf install -y pgbouncer

# Configure PgBouncer
sudo vim /etc/pgbouncer/pgbouncer.ini

[databases]
myapp_db = host=localhost port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Automated Backups

Create a backup script for automated database backups:

# Create backup script
sudo vim /usr/local/bin/pg_backup.sh

#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASES="myapp_db"

mkdir -p $BACKUP_DIR

for DB in $DATABASES; do
    sudo -u postgres pg_dump $DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz
done

# Keep only last 7 days of backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# Make script executable
sudo chmod +x /usr/local/bin/pg_backup.sh

# Add to crontab for daily backups
echo "0 2 * * * /usr/local/bin/pg_backup.sh" | sudo crontab -

PostgreSQL on CentOS provides a robust, scalable database solution suitable for applications ranging from simple web apps to complex enterprise systems. When properly configured and maintained, it delivers excellent performance and reliability. For production deployments requiring high availability and performance, consider using dedicated server infrastructure from MangoHost's dedicated servers or scalable VPS solutions that provide the computational resources needed for demanding database workloads.

For additional information and advanced configuration options, refer to the official PostgreSQL documentation and the PostgreSQL community wiki.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.