PostgreSQL Server Setup - LearnLinuxOnline

PostgreSQL Server Setup on Linux

PostgreSQL is a powerful, open-source object-relational database system. This tutorial covers installing and configuring PostgreSQL on Ubuntu and RHEL-based systems, including security best practices and performance optimization.

Prerequisites

Ubuntu 20.04/22.04 LTS or RHEL/Rocky Linux 8/9
Root or sudo privileges
Minimum 2GB RAM (4GB+ recommended for production)
At least 10GB of free disk space
Basic knowledge of Linux command line

Ubuntu/Debian

RHEL/CentOS

1 Install PostgreSQL

Update your package list and install PostgreSQL:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

The postgresql-contrib package adds additional utilities and functionality.

2 Check PostgreSQL Service Status

Verify that PostgreSQL is running:

sudo systemctl status postgresql

If it's not running, start and enable it:

sudo systemctl start postgresql
sudo systemctl enable postgresql

3 Access PostgreSQL Prompt

Switch to the postgres user and access the PostgreSQL prompt:

sudo -u postgres psql

This will connect you to the PostgreSQL command-line interface.

4 Change PostgreSQL User Password

While in the PostgreSQL prompt, change the postgres user password:

\password postgres

Enter a strong password when prompted.

5 Create a New Database and User

From the PostgreSQL prompt, create a new database and user:

CREATE DATABASE exampledb;
CREATE USER exampleuser WITH ENCRYPTED PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE exampledb TO exampleuser;
\q

This creates a database, user, and grants all privileges to that user.

6 Configure PostgreSQL Authentication

Edit the PostgreSQL authentication configuration file:

sudo nano /etc/postgresql/*/main/pg_hba.conf

Modify the authentication methods. For example, to use md5 authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Reload PostgreSQL to apply changes:

sudo systemctl reload postgresql

7 Configure Remote Access (Optional)

Edit the main PostgreSQL configuration file:

sudo nano /etc/postgresql/*/main/postgresql.conf

Find and modify the listen_addresses line:

listen_addresses = '*'

Update pg_hba.conf to allow remote connections:

sudo nano /etc/postgresql/*/main/pg_hba.conf

Add a line for remote access (replace with your network):

host    all             all             192.168.1.0/24          md5

Security Note: Only allow remote connections from trusted networks and consider using SSL encryption for remote connections.

8 Configure Firewall

Allow PostgreSQL through the firewall (default port 5432):

sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

9 Test the Connection

Test connecting to PostgreSQL with the new user:

psql -h localhost -U exampleuser -d exampledb

Create a simple test table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (name, email) VALUES ('John Doe', 'john@example.com');
SELECT * FROM employees;

10 Basic PostgreSQL Administration

Common administrative commands:

# List all databases
sudo -u postgres psql -c "\l"

# List all users/roles
sudo -u postgres psql -c "\du"

# Backup a database
sudo -u postgres pg_dump exampledb > exampledb_backup.sql

# Restore a database
sudo -u postgres psql exampledb < exampledb_backup.sql

# Check database size
sudo -u postgres psql -c "\l+"

1 Install PostgreSQL on RHEL-based Systems

Enable the PostgreSQL repository and install:

# For RHEL 8/9, Rocky Linux, AlmaLinux
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf clean all
sudo dnf module disable postgresql
sudo dnf install postgresql15-server postgresql15-contrib -y

For CentOS 7:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql12-server postgresql12-contrib -y

2 Initialize PostgreSQL Database

Initialize the PostgreSQL database:

# For RHEL 8/9, Rocky Linux, AlmaLinux
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# For CentOS 7
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

3 Start and Enable PostgreSQL Service

Start the PostgreSQL service and enable it to start on boot:

sudo systemctl start postgresql-15  # Adjust version number as needed
sudo systemctl enable postgresql-15
sudo systemctl status postgresql-15

4 Access PostgreSQL Prompt

Switch to the postgres user and access the PostgreSQL prompt:

sudo -u postgres psql

5 Change PostgreSQL User Password

While in the PostgreSQL prompt, change the postgres user password:

\password postgres

Enter a strong password when prompted.

6 Configure PostgreSQL Authentication

Edit the PostgreSQL authentication configuration file:

sudo nano /var/lib/pgsql/15/data/pg_hba.conf  # Adjust version number as needed

Modify the authentication methods. For example, to use md5 authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Reload PostgreSQL to apply changes:

sudo systemctl reload postgresql-15

7 Configure Remote Access (Optional)

Edit the main PostgreSQL configuration file:

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

Find and modify the listen_addresses line:

listen_addresses = '*'

Update pg_hba.conf to allow remote connections:

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

Add a line for remote access (replace with your network):

host    all             all             192.168.1.0/24          md5

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql-15

8 Configure SELinux and Firewall

If SELinux is enabled, configure it for PostgreSQL:

# Check SELinux status
sestatus

# If enforcing, allow PostgreSQL to network connections
sudo setsebool -P httpd_can_network_connect_db 1

# Or adjust SELinux policy as needed
sudo semanage port -a -t postgresql_port_t -p tcp 5432

Configure the firewall to allow PostgreSQL connections:

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

9 Performance Tuning

Edit the PostgreSQL configuration for better performance:

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

Adjust these settings based on your system resources:

# Memory settings
shared_buffers = 1GB                  # 25% of total RAM
work_mem = 16MB                       # 1/20 of shared_buffers
maintenance_work_mem = 256MB          # For maintenance operations
effective_cache_size = 3GB            # 75% of total RAM

# Checkpoint settings
checkpoint_completion_target = 0.9    # Smooth out checkpoints
max_wal_size = 2GB
min_wal_size = 1GB

# Parallel processing
max_worker_processes = 8              # Number of CPU cores
max_parallel_workers_per_gather = 4   # 50% of max_worker_processes

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql-15

10 Backup and Maintenance

Create a basic backup script:

sudo nano /usr/local/bin/pg-backup.sh

Add the following content (adjust paths and credentials):

#!/bin/bash
# PostgreSQL backup script
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_DIR="/backup/postgres"
PGUSER="postgres"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Get list of databases
DATABASES=$(psql -U $PGUSER -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d' | grep -v template)

# Backup each database
for DB in $DATABASES; do
    pg_dump -U $PGUSER -Fc $DB > "$BACKUP_DIR/$DB-$DATE.dump"
done

# Delete backups older than 30 days
find $BACKUP_DIR -type f -name "*.dump" -mtime +30 -delete

Make the script executable:

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

Previous: MySQL/MariaDB Server Next: Cacti Monitoring