PostgreSQL Installation & Optimization on a Dedicated Server (Ubuntu 24.04)

What Is PostgreSQL and Why Run It on a Dedicated Server?

PostgreSQL is an open-source, ACID-compliant relational database trusted by startups and enterprises alike. It supports advanced data types, full-text search, JSON, and complex queries — making it ideal for SaaS platforms, analytics pipelines, and high-traffic web apps.

Running PostgreSQL on a dedicated server gives you consistent I/O latency, predictable query performance, and full control over memory allocation — none of which you get on shared or over-provisioned cloud instances. This guide walks you through installing PostgreSQL 17 on Ubuntu 24.04 and tuning it for production workloads on bare metal.

Step 1 — Install PostgreSQL 17 from the Official Repository

Ubuntu's default repositories often lag behind upstream versions. Always install directly from the PostgreSQL Global Development Group (PGDG) repository to get the latest stable release and timely security patches.

# Install dependencies
sudo apt install -y curl ca-certificates

# Add the PGDG signing key and repository
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# Install PostgreSQL 17
sudo apt update && sudo apt install -y postgresql-17

Verify that the service is running:

sudo systemctl status postgresql

Step 2 — Secure the Default postgres Superuser

PostgreSQL creates a default postgres OS user and database superuser. You should set a strong password for it immediately:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'YourStrongPassword';"

Never use the postgres superuser for everyday application connections. Create a dedicated role instead:

sudo -u postgres psql

CREATE USER appuser WITH PASSWORD 'SecurePass123';
CREATE DATABASE myappdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE myappdb TO appuser;
\q

Step 3 — Core Performance Tuning in postgresql.conf

The default PostgreSQL configuration is intentionally conservative to allow it to run on any hardware. On a dedicated server, you have full resources — you should use them. Open the configuration file:

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

Apply these settings based on your server's RAM (the following example assumes a 16 GB server):

# Memory
shared_buffers = 4GB              # 25% of total RAM
effective_cache_size = 12GB       # ~75% of total RAM
work_mem = 64MB                   # Per sort/hash operation
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX

# Storage (NVMe/SSD optimized)
random_page_cost = 1.1            # Lower for SSD/NVMe (default is 4.0)
effective_io_concurrency = 200    # Higher for NVMe drives

# Write Ahead Log (WAL)
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB

# Connections
max_connections = 100             # Tune to your app's connection pool

# Query Planner
default_statistics_target = 100

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Step 4 — Enable io_uring for Modern I/O (Ubuntu 24.04)

On Ubuntu 24.04 with PostgreSQL 17, you can enable io_uring for high-performance async I/O. This significantly reduces system call overhead on NVMe drives. This is especially effective on bare metal dedicated servers where you have direct NVMe access without hypervisor overhead.

Add or modify this line in your postgresql.conf:

io_method = io_uring

Step 5 — Configure pg_hba.conf for Secure Access

Next, configure client authentication parameters.

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

For local app connections, use scram-sha-256 (which is much stronger than the older md5 protocol):

# TYPE  DATABASE   USER        ADDRESS          METHOD
local   all        postgres                     peer
local   myappdb    appuser                      scram-sha-256
host    myappdb    appuser    127.0.0.1/32      scram-sha-256

Note: For remote connections, restrict access to known IPs only and always enforce SSL.

Reload the service to apply the authentication changes:

sudo systemctl reload postgresql

Step 6 — Enable Query Monitoring with pg_stat_statements

This extension tracks slow and expensive queries, which is essential for ongoing database optimization.

sudo -u postgres psql -c "CREATE EXTENSION pg_stat_statements;"

Then, ensure it's loaded by checking your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

You can find your slowest queries at any time by running this SQL command:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 7 — Set Up Automated Backups with pg_dump

Run a nightly backup with a simple cron job to protect your data:

sudo -u postgres crontab -e

Add the following line to back up the database at 2:00 AM daily:

0 2 * * * pg_dump myappdb | gzip > /var/backups/myappdb_$(date +\%F).sql.gz

Tip: Keep 7 days of backups locally and pair this setup with rsync to sync your archives to a remote server for true offsite redundancy.

PostgreSQL Performance Quick-Reference (Dedicated Server)

Common PostgreSQL Mistakes on Dedicated Servers

• Leaving shared_buffers at 128 MB: The out-of-box default wastes gigabytes of available RAM that could be used for caching.
• Using md5 auth: Always upgrade your authentication to scram-sha-256 in pg_hba.conf.
• Skipping VACUUM tuning: Ensure autovacuum is enabled and monitor bloat closely on write-heavy tables.
• No connection pooling: Use PgBouncer if your application opens hundreds of short-lived database connections.
• No monitoring: Always enable pg_stat_statements from day one to catch bad queries early.