Skip to content

PostgreSQL Installation & Configuration Guide

Prerequisites

  • RHEL/CentOS/Fedora system with dnf or yum package manager
  • Root/sudo privileges
  • Basic knowledge of PostgreSQL user/database concepts

Installation Instructions

Step 1: Install PostgreSQL Packages

Install PostgreSQL server and contrib packages: 

sudo dnf install postgresql17 postgresql17-server postgresql17-contrib

Package Breakdown: - postgresql17: Core client tools and libraries - postgresql17-server: Server daemon and utilities - postgresql17-contrib: Additional tools and extensions

Step 2: Initialize the Database Cluster

Initialize a new PostgreSQL database cluster: 

sudo postgresql-setup --initdb --unit postgresql

This creates the default database cluster in /var/lib/pgsql/data/ and sets up permissions.

Step 3: Enable PostgreSQL Service

Enable PostgreSQL to start automatically on system boot: 

sudo systemctl enable postgresql

Step 4: Start PostgreSQL Service

Start the PostgreSQL service: 

sudo systemctl start postgresql

Step 5: Verify Installation

Check that PostgreSQL started successfully: 

sudo systemctl status postgresql

Step 6: Configure Network Connections

Edit the PostgreSQL main configuration file: 

sudo nano /var/lib/pgsql/data/postgresql.conf

Find the CONNECTIONS AND AUTHENTICATION section and set the listen_addresses parameter: 

# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '10.0.0.77'

Common listen_addresses values: - 'localhost' — Only local connections - '*' — Listen on all IPv4 addresses - '10.0.0.77' — Listen on specific IP address (internal network) - '0.0.0.0' — All IPv4 addresses (less restrictive)

Step 7: Configure Host-Based Authentication

Edit the authentication rules file: 

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

Add or modify the host authentication entry to allow network connections: 

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             10.0.0.0/24             md5

Field Explanations: - TYPE: Connection type (host = TCP/IP, local = Unix socket) - DATABASE: Which database(s) this rule applies to (all = all databases) - USER: Which user(s) this rule applies to (all = all users) - ADDRESS: Client IP address/network (CIDR notation) - METHOD: Authentication method (md5, password, trust, scram-sha-256)

Step 8: Restart PostgreSQL

Restart the service to apply configuration changes: 

sudo systemctl restart postgresql

Step 9: Connect as PostgreSQL Admin

Connect to PostgreSQL as the system postgres user: 

sudo -u postgres psql

This opens the PostgreSQL interactive terminal.

Step 10: Create Database Users and Databases

Inside the PostgreSQL prompt, create a new user with password: 

CREATE USER myuser WITH PASSWORD 'your_secure_password';

Create a database owned by that user: 

CREATE DATABASE mydb OWNER myuser;

Optionally grant database creation privileges: 

ALTER USER myuser CREATEDB;

List users and databases: 

\du        -- List users (roles)
\l         -- List databases
\q         -- Quit psql

Systemd Service Configuration

PostgreSQL is managed through systemd. The service unit is automatically created during installation.

Service Management

Command Purpose
sudo systemctl start postgresql Start the service
sudo systemctl stop postgresql Stop the service
sudo systemctl restart postgresql Restart the service
sudo systemctl status postgresql Check service status
sudo systemctl enable postgresql Enable on boot
sudo systemctl disable postgresql Disable on boot

Service Logs

View PostgreSQL logs:

# View system journal for PostgreSQL
sudo journalctl -u postgresql -n 50

# Follow logs in real-time
sudo journalctl -u postgresql -f

# View logs without paging
sudo journalctl -u postgresql --no-pager

PostgreSQL also maintains its own log file (if configured): 

sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log

Configuration Files

Main Configuration: postgresql.conf

Location: /var/lib/pgsql/data/postgresql.conf

Key sections and settings:

Connections and Authentication

# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '10.0.0.77'         # IP addresses to listen on
port = 5432                             # Port to listen on (default: 5432)
max_connections = 100                   # Maximum allowed connections

# - Authentication -

password_encryption = scram-sha-256     # Password hashing algorithm

Performance Tuning

# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

shared_buffers = 256MB                  # RAM for shared buffers (1/4 of system RAM)
effective_cache_size = 1GB              # Available RAM (helps query planner)
work_mem = 4MB                          # RAM per query operation
maintenance_work_mem = 64MB             # RAM for maintenance operations

Logging

# REPORTING AND LOGGING
#------------------------------------------------------------------------------

logging_collector = on                  # Enable logging to files
log_directory = 'log'                   # Log directory (relative to data dir)
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # Log file naming
log_statement = 'all'                   # Log all SQL statements (development)
log_duration = off                      # Log query duration
log_min_duration_statement = 1000       # Log slow queries (> 1000ms)

Authentication Configuration: pg_hba.conf

Location: /var/lib/pgsql/data/pg_hba.conf

This file controls how clients authenticate to PostgreSQL.

Sample Configuration

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                trust
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    all             all             10.0.0.0/24             md5
host    replication     all             10.0.0.0/24             md5

Authentication Methods

Method Description Security
trust No password required Low - development only
password Plain-text password over connection Low - avoid
md5 MD5-hashed password Medium - legacy
scram-sha-256 SCRAM SHA-256 hashing High - recommended
ident Uses system user identity Medium - local only
peer Uses OS user mapping Medium - local only

Configuration Fields

Field Values Purpose
TYPE local, host, hostssl, hostnossl Connection type
DATABASE all, database_name, @filename Target database(s)
USER all, username, +groupname, @filename Target user(s)
ADDRESS CIDR notation (e.g., 10.0.0.0/24) Client IP range
METHOD trust, password, md5, scram-sha-256, ident, peer Authentication type

Database and User Management

Creating Users (Roles)

Connect to PostgreSQL as the postgres user: 

sudo -u postgres psql

Create a standard user: 

CREATE USER myuser WITH PASSWORD 'your_secure_password';

Create a superuser: 

CREATE USER admin WITH PASSWORD 'admin_password' SUPERUSER;

Create a user with database creation privileges: 

CREATE USER dbadmin WITH PASSWORD 'dbadmin_password' CREATEDB;

Creating Databases

Create a database owned by a specific user: 

CREATE DATABASE mydb OWNER myuser;

Create a database with specific encoding: 

CREATE DATABASE mydb OWNER myuser ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

Granting Privileges

Grant all privileges on a database: 

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Grant specific privileges: 

GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Useful psql Commands

\du                 -- List all users (roles)
\l                  -- List all databases
\c dbname           -- Connect to database
\dt                 -- List tables in current database
\d tablename        -- Describe table structure
\df                 -- List functions
\dn                 -- List schemas
\dU                 -- List user mappings
\q                  -- Quit psql

Network Access Configuration

Allow Remote Connections

To allow clients from another machine to connect:

  1. Edit postgresql.conf: 
    sudo nano /var/lib/pgsql/data/postgresql.conf

Set listen_addresses to your network interface IP: 

listen_addresses = '10.0.0.77'  # Or '*' for all interfaces
port = 5432

  1. Edit pg_hba.conf: 
    sudo nano /var/lib/pgsql/data/pg_hba.conf

Add authentication rule for the client network: 

host    all             all             10.0.0.0/24             scram-sha-256

  1. Restart PostgreSQL: 

    sudo systemctl restart postgresql

  2. Test connectivity from remote host: 

    psql -h 10.0.0.77 -U myuser -d mydb

Backup and Restore

Backup a Database

Create a SQL dump of a database: 

sudo -u postgres pg_dump mydb > mydb_backup.sql

Create a binary (custom format) backup: 

sudo -u postgres pg_dump -Fc mydb > mydb_backup.dump

Backup all databases: 

sudo -u postgres pg_dumpall > all_databases.sql

Restore a Database

Restore from SQL dump: 

sudo -u postgres psql mydb < mydb_backup.sql

Restore from binary dump: 

sudo -u postgres pg_restore -d mydb mydb_backup.dump

Quick Reference Commands

# Installation and Service
sudo dnf install postgresql17 postgresql17-server postgresql17-contrib
sudo postgresql-setup --initdb --unit postgresql
sudo systemctl enable postgresql
sudo systemctl start postgresql

# Service Management
sudo systemctl status postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

# Access PostgreSQL
sudo -u postgres psql              # Connect as postgres user
psql -h 10.0.0.77 -U myuser -d mydb  # Connect as specific user (remote)

# Configuration
sudo nano /var/lib/pgsql/data/postgresql.conf
sudo nano /var/lib/pgsql/data/pg_hba.conf
sudo systemctl reload postgresql   # Reload config without full restart

# Logs
sudo journalctl -u postgresql -f
sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log

# User and Database Management (inside psql)
CREATE USER newuser WITH PASSWORD 'password';
CREATE DATABASE newdb OWNER newuser;
ALTER USER newuser CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

# Backup and Restore
sudo -u postgres pg_dump mydb > backup.sql
sudo -u postgres pg_dumpall > all_databases.sql
sudo -u postgres psql mydb < backup.sql

Troubleshooting

Service Fails to Start

Check the service status and logs: 

sudo systemctl status postgresql
sudo journalctl -u postgresql -n 50 --no-pager

Common issues: - Database cluster not initialized: Run sudo postgresql-setup --initdb --unit postgresql - Port already in use: Check sudo ss -ltnp | grep 5432 - Permission issues: Verify /var/lib/pgsql/data/ is owned by postgres:postgres

Cannot Connect Remotely

  1. Verify PostgreSQL is listening on correct address: 

    sudo ss -ltnp | grep postgres

  2. Check pg_hba.conf allows the client IP

  3. Verify firewall rules allow the connection
  4. Test with psql command from remote host

Connection Refused (127.0.0.1:5432)

PostgreSQL may be listening only on Unix socket. Check: 

sudo -u postgres psql  # This works if listening on Unix socket

To fix, ensure listen_addresses in postgresql.conf includes '127.0.0.1' or '*', then restart.

Authentication Failed

Verify the user and password: 

sudo -u postgres psql -c "SELECT usename FROM pg_user;"

Check pg_hba.conf for correct authentication method (use scram-sha-256 for password auth).

Out of Memory

Adjust shared_buffers and effective_cache_size in postgresql.conf based on available RAM, then restart: 

sudo systemctl restart postgresql