PostgreSQL

PostgreSQL is a powerful open-source relational database management system known for its reliability, data integrity, and rich feature set. This article covers installing PostgreSQL on EL-based distributions, initializing the database, configuring authentication, user and database management, psql usage, backup and recovery, and remote access.

Versions Across Distributions

Live version data by pkgseek.com

Install PostgreSQL

Using the System Default Repository (EL 9)

EL 9 AppStream manages PostgreSQL versions through modularity. The usual default stream is 13, and other streams such as 15, 16, and 18 can be enabled as well (check dnf module list postgresql for what your repositories provide):

sudo dnf module list postgresql

sudo dnf install postgresql-server postgresql -y

Using the PostgreSQL Official Repository (Recommended, EL 9 & 10)

The official repository provides the latest stable version (currently PostgreSQL 18), and the URL adapts to your EL release automatically:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# EL 9 only — disable the built-in module first:
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql18-server postgresql18

sudo dnf install -y postgresql18-server postgresql18

Initialize the Database (initdb)

After installation, the database cluster must be initialized before the service can be started.

System Repository Version Initialization

sudo postgresql-setup --initdb

Official Repository Version Initialization

sudo /usr/pgsql-18/bin/postgresql-18-setup initdb

Start the Service

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

sudo systemctl start postgresql-18
sudo systemctl enable postgresql-18
sudo systemctl status postgresql-18

Data Directory Reference

Version	Data Directory	Configuration Directory
System repository	`/var/lib/pgsql/data/`	`/var/lib/pgsql/data/`
Official repository (18)	`/var/lib/pgsql/18/data/`	`/var/lib/pgsql/18/data/`

Configure pg_hba.conf

pg_hba.conf (Host-Based Authentication) controls client connection authentication and is the core of PostgreSQL security configuration.

sudo -u postgres psql -c "SHOW hba_file;"

Authentication Methods

Method	Description
`peer`	Matches the OS username to the database user (local Unix connections only)
`ident`	Similar to peer, verifies via ident server (TCP connections)
`md5`	MD5-encrypted password verification
`scram-sha-256`	SCRAM-SHA-256 encrypted verification (recommended)
`trust`	No password required, direct trust (test environments only)
`reject`	Reject connection

Edit pg_hba.conf

sudo vi /var/lib/pgsql/data/pg_hba.conf

Typical configuration example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local Unix socket connections
local   all             postgres                                peer
local   all             all                                     scram-sha-256

# Local IPv4 connections
host    all             all             127.0.0.1/32            scram-sha-256

# Local IPv6 connections
host    all             all             ::1/128                 scram-sha-256

# Allow remote connections from a specific subnet
host    all             all             192.168.1.0/24          scram-sha-256

# Allow a specific user to connect to a specific database
host    mydb            myuser          10.0.0.0/8              scram-sha-256

Reload the configuration after changes:

sudo systemctl reload postgresql

Create Users and Databases

Using Command-Line Tools

sudo -u postgres createuser --interactive --pwprompt myuser

sudo -u postgres createdb --owner=myuser mydb

Using SQL Statements

sudo -u postgres psql

-- Create a user
CREATE USER myuser WITH PASSWORD 'StrongPassword123!';

-- Create a database with a specified owner
CREATE DATABASE mydb OWNER myuser;

-- Set character encoding
CREATE DATABASE mydb_utf8
    OWNER myuser
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    TEMPLATE template0;

-- Grant database connection privilege
GRANT CONNECT ON DATABASE mydb TO myuser;

-- Grant schema privileges
\c mydb
GRANT USAGE ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

User Management

-- View all users
\du

-- Change user password
ALTER USER myuser WITH PASSWORD 'NewPassword456!';

-- Grant superuser privileges (use with caution)
ALTER USER myuser WITH SUPERUSER;

-- Revoke superuser privileges
ALTER USER myuser WITH NOSUPERUSER;

-- Drop a user (must first revoke owned objects)
DROP OWNED BY myuser;
DROP USER myuser;

psql Basic Operations

psql is the interactive terminal tool for PostgreSQL.

Connect to a Database

# Log in as the postgres system user
sudo -u postgres psql

# Specify user and database
psql -U myuser -d mydb

# Connect with a specified host
psql -U myuser -d mydb -h 127.0.0.1 -p 5432

Common psql Meta-Commands

\l          -- List all databases
\c dbname   -- Switch database
\dt         -- List all tables in the current database
\d tablename -- View table structure
\du         -- List all users/roles
\dn         -- List all schemas
\df         -- List all functions
\di         -- List all indexes
\dx         -- List installed extensions
\timing     -- Toggle query timing
\x          -- Toggle expanded display mode
\q          -- Quit psql
\?          -- Show help
\h SQL_CMD  -- Show SQL command help

Basic SQL Operations

-- Create a table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO articles (title, content, author) VALUES
    ('Getting Started with PostgreSQL', 'This is a beginner tutorial...', 'Zhang San'),
    ('Database Optimization', 'Optimization tips summary...', 'Li Si');

-- Query data
SELECT * FROM articles;
SELECT title, author FROM articles WHERE author = 'Zhang San';

-- Update data
UPDATE articles SET title = 'Advanced PostgreSQL' WHERE id = 1;

-- Delete data
DELETE FROM articles WHERE id = 2;

-- View table size
SELECT pg_size_pretty(pg_total_relation_size('articles'));

pg_dump Backup and Recovery

Back Up a Single Database

pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d_%H%M%S).sql

pg_dump -U postgres -Fc mydb > /backup/mydb_$(date +%Y%m%d_%H%M%S).dump

Back Up All Databases

pg_dumpall -U postgres > /backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

Back Up Schema Only

pg_dump -U postgres --schema-only mydb > /backup/mydb_schema.sql

Back Up Data Only

pg_dump -U postgres --data-only mydb > /backup/mydb_data.sql

Restore a Database

# Create the target database first
sudo -u postgres createdb mydb_restored

# Restore
psql -U postgres mydb_restored < /backup/mydb_20260324.sql

pg_restore -U postgres -d mydb_restored /backup/mydb_20260324.dump

pg_restore -U postgres -d mydb --clean --if-exists /backup/mydb_20260324.dump

Automated Backup Script

sudo tee /usr/local/bin/pg-backup.sh << 'SCRIPT'
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

# Back up all databases (custom compressed format)
for DB in $(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
    sudo -u postgres pg_dump -Fc "$DB" > "${BACKUP_DIR}/${DB}_${DATE}.dump"
done

# Back up global objects (roles, tablespaces)
sudo -u postgres pg_dumpall --globals-only > "${BACKUP_DIR}/globals_${DATE}.sql"

# Clean up old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "*.sql" -mtime +${RETENTION_DAYS} -delete

echo "PostgreSQL backup completed at ${DATE}"
SCRIPT

sudo chmod +x /usr/local/bin/pg-backup.sh

echo "0 3 * * * root /usr/local/bin/pg-backup.sh" | sudo tee /etc/cron.d/pg-backup

Remote Access Configuration

Modify postgresql.conf

Change the PostgreSQL listen address:

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /var/lib/pgsql/data/postgresql.conf

If you only need to listen on a specific IP:

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = 'localhost,192.168.1.10'/" /var/lib/pgsql/data/postgresql.conf

Modify pg_hba.conf

Add a remote access rule in pg_hba.conf:

echo "host    all    all    192.168.1.0/24    scram-sha-256" | sudo tee -a /var/lib/pgsql/data/pg_hba.conf

Restart the Service and Open the Firewall

sudo systemctl restart postgresql
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

Verify the Listening Port

ss -tlnp | grep 5432

Test the Remote Connection

psql -U myuser -d mydb -h server_IP_address -p 5432

Security Recommendations

Precisely specify allowed IP ranges in pg_hba.conf; avoid using 0.0.0.0/0
Use scram-sha-256 authentication instead of md5
Consider connecting via SSH tunnel to avoid directly exposing port 5432
Enable SSL connection encryption

# Create a tunnel locally
ssh -L 5432:127.0.0.1:5432 user@server_IP_address

# Connect through the tunnel
psql -U myuser -d mydb -h 127.0.0.1

Common Operations Quick Reference

# Start / Stop / Restart / Reload
sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql

# View version
psql --version

# View database sizes
sudo -u postgres psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"

# View active connections
sudo -u postgres psql -c "SELECT pid, usename, datname, client_addr, state, query FROM pg_stat_activity WHERE state = 'active';"

# View configuration file locations
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

# View current connection count
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

EL 10 Notes

EL 10 and EL 9 differ in how system repository versions are managed:

Version	EL 9 system repo	EL 10 system repo
PostgreSQL	13 (usual default stream); 15 / 16 / 18 streams can be enabled	16 (no modularity, install directly)

When using the PostgreSQL official repository (recommended), version selection is more flexible — the current latest stable release is PostgreSQL 18:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

The official repo URL uses %{rhel} and automatically adapts for EL 10.