Practical Guide: PostgreSQL Installation and Performance Tuning on Oracle Linux 8

PostgreSQL Installation and Performance Tuning on Oracle Linux 8

In this lab, we will cover the step-by-step process of installing, configuring, and applying initial performance tuning to PostgreSQL running on Oracle Linux 8. This guide covers everything from initializing the data cluster to fine-tuning the parameters for a machine with 4GB of RAM and 2 CPUs.

1. Installation via AppStream

If you need a quick installation without depending on external repositories (which is great for restricted environments that do not require internet access), you can use the Oracle Linux AppStream.

First, list and enable the PostgreSQL module (version 15 is recommended here):

dnf module list postgresql
dnf module enable -y postgresql:15

Install the server and contrib packages, and verify the installed version:

dnf install -y postgresql-server postgresql-contrib
postgres --version

(Note: If you prefer the latest version via the official PGDG repository, you will need to disable the native module and install it via the official RPM).

2. Database Initialization and Service

With the binaries installed, the next step is to initialize the data cluster:

postgresql-setup --initdb
ls /var/lib/pgsql/data/

Then, start and enable the service so it boots automatically with the OS:

systemctl enable --now postgresql
systemctl status postgresql

3. Configuring Access and Security

By default, PostgreSQL access is highly restricted. Let’s set up the superuser password and allow remote access.

Set the postgres user password:

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'SuaSenhaForte123!';
\q

Adjust pg_hba.conf for local and remote connections: Edit the file vi /var/lib/pgsql/data/pg_hba.conf and change the METHOD column from ident to md5 (or scram-sha-256) on the following lines:

1. TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             [IP ADDRESS]/24         md5

Enable network listening: In the main configuration file (vi /var/lib/pgsql/data/postgresql.conf), adjust the listen parameter to accept external connections:

listen_addresses = '*'          # was 'localhost'
port = 5432

Restart the service and open the port in the Firewall (if firewalld is active, skip if disabled):

systemctl restart postgresql
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

4. Creating an Application Database and User

Avoid using the postgres user for your applications. Create a dedicated database and user:

sudo -u postgres psql

-- Create user:
CREATE USER appuser WITH PASSWORD 'AppSenha123!';
-- Create DB:
CREATE DATABASE appdb OWNER appuser;
-- Grant privileges:
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

Test the connection remotely :

psql -U appuser -h [Host name/address] -p 5432 -d appdb -c "SELECT now();"

5. Performance Tuning (4GB RAM / 2 CPUs)

PostgreSQL’s default settings are conservative to ensure broad compatibility. The following values are specifically calculated for our hardware setup (4GB RAM and 2 CPUs).

You can apply all these configurations at once by pasting the block below into your terminal. This will append the settings to the end of your postgresql.conf, overwriting the previous default values:

cat >> /var/lib/pgsql/data/postgresql.conf <<'EOF'
1. ---- TUNING [Host name] (4GB RAM / 2 CPU) ----

1. Memory
shared_buffers = 1GB                  # 25% of RAM
effective_cache_size = 3GB            # 75% of RAM
work_mem = 16MB                       # RAM / max_connections / 3
maintenance_work_mem = 256MB          # 5-10% of RAM

1. Connections
max_connections = 100                 # CPUs x 50 (max)

1. WAL (Write-Ahead Log)
wal_buffers = 16MB
max_wal_size = 1GB
min_wal_size = 256MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min

1. Parallelism (2 CPUs)
max_worker_processes = 2
max_parallel_workers = 2
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1

1. Query Planner and IO
random_page_cost = 1.1                # Adjusted for SSD (Use 4.0 for HDD)
effective_io_concurrency = 100
default_statistics_target = 100

1. Aggressive Autovacuum
autovacuum_max_workers = 2
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

1. Logging for Diagnostics
log_min_duration_statement = 1000     # Logs queries > 1 second
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
deadlock_timeout = 1s
EOF

To apply the changes and verify the active parameters:

systemctl restart postgresql
sudo -u postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','work_mem','max_connections','effective_cache_size');"

Pro Tip: If you are running the database on different hardware, pgtune.leopard.in.ua is an excellent tool to generate automatic initial tuning based on your storage, workload, RAM, and CPUs.

Admin Survival Cheatsheet

For your daily operations, here are the most vital commands and file paths for your new instance:

• Main Configuration: /var/lib/pgsql/data/postgresql.conf
• Authentication Config: /var/lib/pgsql/data/pg_hba.conf
• Data & Logs Directory: /var/lib/pgsql/data/ and /var/lib/pgsql/data/log/
• OS Logs: journalctl -u postgresql -f
• DB Logs: tail -f /var/lib/pgsql/data/log/postgresql-*.log
• View active connections: SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity;
• Database Sizes: SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

Until next time! If you have any questions, leave them in the comments or ping us in the terminal.