How To Install PostgreSQL on AlmaLinux 10
PostgreSQL stands as one of the most powerful and reliable open-source relational database management systems available today. When combined with AlmaLinux 10, a robust enterprise-grade Linux distribution, this database solution provides exceptional performance, stability, and security for production environments. This comprehensive guide walks you through every step of installing PostgreSQL on AlmaLinux 10, from initial system preparation to advanced configuration and optimization.
Whether you’re a system administrator managing enterprise infrastructure, a developer building scalable applications, or a database professional seeking reliable solutions, this tutorial provides the expertise and detailed instructions needed for successful PostgreSQL deployment. The installation process covers multiple methods, security hardening, performance optimization, and troubleshooting common issues that may arise during implementation.
Understanding PostgreSQL and AlmaLinux 10 Foundation
What Makes PostgreSQL Outstanding
PostgreSQL has earned its reputation through more than three decades of active development and continuous improvement. This advanced database system offers full ACID compliance, ensuring data integrity and reliability in mission-critical applications. Its extensibility allows developers to create custom data types, functions, and operators tailored to specific requirements.
The database engine supports advanced features including JSON and JSONB data types, full-text search capabilities, and sophisticated indexing mechanisms. PostgreSQL’s standards compliance with SQL specifications ensures compatibility with existing applications while providing modern features for contemporary development needs.
AlmaLinux 10 as Enterprise Platform
AlmaLinux 10 represents the latest evolution in Red Hat Enterprise Linux-compatible distributions. This enterprise-ready operating system provides long-term stability, comprehensive security features, and extensive package management capabilities. Its binary compatibility with RHEL ensures seamless integration with existing enterprise infrastructure.
The distribution includes advanced security frameworks like SELinux, comprehensive firewall management, and robust package verification systems. These features make AlmaLinux 10 an ideal foundation for database servers requiring high availability and security.
Strategic Advantages of This Combination
Combining PostgreSQL with AlmaLinux 10 creates a powerful database platform suitable for demanding production environments. The operating system’s stability complements PostgreSQL’s reliability, while both systems provide extensive configuration options for performance optimization.
Prerequisites and System Requirements
Hardware Specifications for Optimal Performance
PostgreSQL performance depends heavily on adequate hardware resources. Minimum requirements include 2GB RAM, 20GB available disk space, and a dual-core processor. However, production environments typically require 8GB or more RAM, SSD storage for database files, and multi-core processors for concurrent transaction handling.
Memory allocation is crucial since PostgreSQL utilizes shared buffers and work memory for query processing. Plan for approximately 25% of system memory for shared buffers, with additional memory for operating system caches and concurrent connections.
Software Prerequisites and Dependencies
Before beginning installation, ensure your AlmaLinux 10 system meets these requirements:
- Fresh AlmaLinux 10 installation (64-bit architecture)
- Administrative privileges (sudo or root access)
- Active internet connection for package downloads
- Terminal or SSH access to the system
- Updated system packages and security patches
Network and Security Considerations
Evaluate network configuration requirements, including firewall settings, port availability, and remote access needs. PostgreSQL uses port 5432 by default, which must be accessible for client connections. Consider implementing VPN or secure tunneling for remote database access in production environments.
System Preparation and Updates
Comprehensive System Updates
Begin with a complete system update to ensure security patches and package compatibility. Execute the following commands to update your AlmaLinux 10 system:
sudo dnf clean all
sudo dnf update -y
sudo dnf upgrade -yThis process may take several minutes depending on available updates. Reboot the system if kernel updates are installed to ensure all changes take effect properly.
Package Manager Configuration
AlmaLinux 10 utilizes DNF (Dandified YUM) as its primary package manager. Understanding DNF commands is essential for PostgreSQL installation and maintenance:
# Check repository status
sudo dnf repolist
# Refresh package cache
sudo dnf makecache
# Search for PostgreSQL packages
sudo dnf search postgresqlSecurity Framework Verification
Verify SELinux status and firewall configuration before proceeding with installation:
# Check SELinux status
sestatus
# View firewall status
sudo firewall-cmd --state
# List active firewall zones
sudo firewall-cmd --get-active-zonesDocument current security settings for reference during configuration steps.
PostgreSQL Installation Methods Overview
Default Repository Installation Benefits
AlmaLinux 10 includes PostgreSQL packages in its default repositories, providing seamless integration with system package management. This method offers automatic security updates, dependency resolution, and compatibility with system libraries.
The default repository typically includes PostgreSQL 16, which provides excellent stability and performance for most applications. This installation method requires minimal configuration and follows standard AlmaLinux package management practices.
Official PostgreSQL Repository Advantages
The official PostgreSQL YUM repository provides access to the latest PostgreSQL versions, including PostgreSQL 17 and future releases. This repository offers extended version support, timely security updates, and access to additional PostgreSQL extensions.
Using the official repository ensures access to the most current features and performance improvements, making it ideal for applications requiring cutting-edge database capabilities.
Source Code Compilation Considerations
Compiling PostgreSQL from source code offers maximum customization but requires significant expertise and ongoing maintenance. This method is generally recommended only for specialized deployments requiring specific compile-time options or custom extensions.
Method 1: Installing from Default Repositories
Package Installation Process
Install PostgreSQL server and client packages from AlmaLinux 10 default repositories:
sudo dnf install postgresql-server postgresql-contrib postgresql -yThe installation includes:
- postgresql-server: Core database engine and server components
- postgresql-contrib: Additional utilities and extensions
- postgresql: Client tools and command-line interface
Database Initialization
Initialize the PostgreSQL database cluster using the setup utility:
sudo postgresql-setup --initdbThis command creates the initial database structure, configuration files, and system catalogs in /var/lib/pgsql/data/. The initialization process configures default authentication, creates system databases, and establishes initial user accounts.
Service Management Configuration
Configure PostgreSQL to start automatically at boot and start the service:
# Start PostgreSQL service
sudo systemctl start postgresql
# Enable automatic startup
sudo systemctl enable postgresql
# Verify service status
sudo systemctl status postgresqlInstallation Verification
Confirm successful installation by checking the PostgreSQL version:
# Check server version
sudo -u postgres psql -c "SELECT version();"
# Check client version
psql --versionThe output should display PostgreSQL 16.x information, confirming successful installation.
Method 2: Installing from Official PostgreSQL Repository
Adding PostgreSQL Official Repository
Install the official PostgreSQL repository configuration:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpmThis repository provides access to all supported PostgreSQL versions and maintains compatibility with AlmaLinux 10 systems.
Repository Configuration and Module Management
Update the package cache and verify repository installation:
# Update package cache
sudo dnf makecache
# List available PostgreSQL versions
sudo dnf list postgresql*-serverInstalling Latest PostgreSQL Version
Install PostgreSQL 17 from the official repository:
sudo dnf install postgresql17-server postgresql17-contrib postgresql17 -yDatabase Initialization for Latest Version
Initialize the database cluster for PostgreSQL 17:
sudo /usr/pgsql-17/bin/postgresql-17-setup initdbService Configuration for PostgreSQL 17
Configure and start the PostgreSQL 17 service:
# Start PostgreSQL 17 service
sudo systemctl start postgresql-17
# Enable automatic startup
sudo systemctl enable postgresql-17
# Check service status
sudo systemctl status postgresql-17Version Verification
Confirm PostgreSQL 17 installation:
# Check installed version
/usr/pgsql-17/bin/psql --version
# Connect and verify server version
sudo -u postgres /usr/pgsql-17/bin/psql -c "SELECT version();"Initial Database Configuration
Understanding PostgreSQL User System
PostgreSQL creates a dedicated system user named ‘postgres’ during installation. This user owns database files and processes, providing security isolation from other system users.
Switch to the postgres user for database administration:
sudo -i -u postgresSetting PostgreSQL User Password
Configure a secure password for the postgres user:
sudo passwd postgresChoose a strong password following security best practices: minimum 12 characters, mixing uppercase, lowercase, numbers, and special characters.
First Database Connection
Connect to PostgreSQL using the command-line interface:
# As postgres user
psql
# Or directly with sudo
sudo -u postgres psqlThe PostgreSQL prompt (postgres=#) indicates successful connection to the default database.
Essential PostgreSQL Commands
Learn fundamental commands for database management:
-- List all databases
\l
-- List database users
\du
-- Connect to specific database
\c database_name
-- Display tables in current database
\dt
-- Exit PostgreSQL shell
\qSecurity Configuration and Best Practices
Authentication Configuration
PostgreSQL authentication is controlled by the pg_hba.conf file located in the data directory. Edit this file to configure authentication methods:
sudo nano /var/lib/pgsql/data/pg_hba.confCommon authentication methods include:
- trust: No password required (development only)
- md5: Password authentication with MD5 hashing
- scram-sha-256: Modern password authentication (recommended)
User Management and Access Control
Create dedicated database users for applications:
-- Create new user with password
CREATE USER myapp_user WITH PASSWORD 'secure_password';
-- Grant database privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;
-- Create user with specific permissions
CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;Network Security Implementation
Configure firewall rules for PostgreSQL access:
# Allow PostgreSQL through firewall
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload
# Or allow specific port
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reloadFile System Security
Verify proper file permissions on database directories:
# Check data directory permissions
ls -la /var/lib/pgsql/data/
# Ensure correct ownership
sudo chown -R postgres:postgres /var/lib/pgsql/data/Remote Access Configuration
Enabling Remote Connections
Modify PostgreSQL configuration to accept remote connections:
sudo nano /var/lib/pgsql/data/postgresql.confUpdate the following settings:
# Enable listening on all interfaces
listen_addresses = '*'
# Set port (default is 5432)
port = 5432
# Increase maximum connections if needed
max_connections = 100Client Authentication Setup
Configure client authentication for remote access:
sudo nano /var/lib/pgsql/data/pg_hba.confAdd entries for remote connections:
# Allow connections from specific IP
host all all 192.168.1.0/24 md5
# Allow connections from any IP (less secure)
host all all 0.0.0.0/0 md5Firewall Configuration for Remote Access
Configure firewall rules for remote PostgreSQL access:
# Allow remote connections
sudo firewall-cmd --permanent --add-rich-rule="rule family='ipv4' source address='192.168.1.0/24' port protocol='tcp' port='5432' accept"
sudo firewall-cmd --reloadService Restart and Testing
Restart PostgreSQL to apply configuration changes:
sudo systemctl restart postgresql
# Test remote connection
psql -h your_server_ip -U postgres -d postgresTesting and Verification
Local Connection Testing
Verify local database connectivity:
# Connect as postgres user
sudo -u postgres psql
# Create test database
CREATE DATABASE testdb;
# Connect to test database
\c testdb;
# Create test table
CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR(50));
# Insert test data
INSERT INTO test_table (name) VALUES ('Test Entry');
# Query test data
SELECT * FROM test_table;Remote Connection Testing
Test remote connectivity from another system:
# Test connection from remote host
psql -h server_ip_address -U postgres -d postgres
# Test with specific database
psql -h server_ip_address -U postgres -d testdbPerformance and Status Monitoring
Monitor PostgreSQL performance and status:
-- Check active connections
SELECT * FROM pg_stat_activity;
-- View database statistics
SELECT * FROM pg_stat_database;
-- Monitor query performance
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;Performance Optimization and Best Practices
Memory Configuration Optimization
Optimize PostgreSQL memory settings in postgresql.conf:
# Shared buffers (25% of RAM)
shared_buffers = 2GB
# Work memory per connection
work_mem = 4MB
# Maintenance work memory
maintenance_work_mem = 256MB
# Effective cache size (75% of RAM)
effective_cache_size = 6GBConnection and Resource Management
Configure connection pooling and resource limits:
# Maximum connections
max_connections = 100
# Connection timeout
statement_timeout = 30s
# Lock timeout
lock_timeout = 10sIndex Management and Optimization
Implement effective indexing strategies:
-- Create index on frequently queried columns
CREATE INDEX idx_user_email ON users(email);
-- Create composite index for complex queries
CREATE INDEX idx_order_date_status ON orders(order_date, status);
-- Monitor index usage
SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC;Troubleshooting Common Issues
Installation Problems
Repository Access Issues:
# Clear DNF cache
sudo dnf clean all
# Rebuild cache
sudo dnf makecache
# Check repository status
sudo dnf repolistPackage Dependency Conflicts:
# Check for conflicting packages
sudo dnf check
# Resolve dependencies
sudo dnf install --best --allowerasing postgresql-serverConnection Issues
Service Startup Problems:
# Check service status
sudo systemctl status postgresql
# View detailed logs
sudo journalctl -u postgresql
# Check for port conflicts
sudo netstat -tlnp | grep 5432Authentication Failures:
# Reset postgres user password
sudo passwd postgres
# Check pg_hba.conf configuration
sudo cat /var/lib/pgsql/data/pg_hba.conf
# Reload configuration
sudo systemctl reload postgresqlPerformance Issues
Slow Query Identification:
-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_min_duration_statement = 1000;
-- Reload configuration
SELECT pg_reload_conf();
-- View slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE mean_time > 1000
ORDER BY mean_time DESC;Backup and Recovery Implementation
Automated Backup Strategy
Create automated backup scripts:
#!/bin/bash
# PostgreSQL backup script
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DATABASE="your_database"
# Create backup directory
mkdir -p $BACKUP_DIR
# Perform backup
sudo -u postgres pg_dump $DATABASE > $BACKUP_DIR/backup_$DATE.sql
# Compress backup
gzip $BACKUP_DIR/backup_$DATE.sql
# Remove old backups (keep 7 days)
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -deleteRecovery Procedures
Restore from backup:
# Stop PostgreSQL service
sudo systemctl stop postgresql
# Restore database
sudo -u postgres psql -d database_name < backup_file.sql
# Start PostgreSQL service
sudo systemctl start postgresqlMonitoring and Maintenance
Log Management
Configure PostgreSQL logging:
# Enable logging
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MBRegular Maintenance Tasks
Implement routine maintenance:
-- Analyze database statistics
ANALYZE;
-- Vacuum to reclaim space
VACUUM;
-- Reindex tables
REINDEX DATABASE your_database;Advanced Security Hardening
SSL/TLS Configuration
Enable SSL encryption:
# Generate SSL certificate
sudo openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=your-server-name"
# Set proper permissions
sudo chmod 600 server.key
sudo chown postgres:postgres server.key server.crtConfigure SSL in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'Advanced Authentication
Implement certificate-based authentication:
# In pg_hba.conf
hostssl all all 0.0.0.0/0 certCongratulations! You have successfully installed PostgreSQL. Thanks for using this tutorial for installing PostgreSQL 17 on your AlmaLinux OS 10 system. For additional help or useful information, we recommend you check the official PostgreSQL website.
