PostgreSQL is the most advanced open-source relational database system in production use today. Trusted by organizations ranging from startups to enterprises, it powers everything from small web applications to massive data warehousing systems processing billions of rows. This guide walks you through installing PostgreSQL on Ubuntu Server, configuring it for production use, setting up authentication and remote access, implementing backup strategies, and tuning performance parameters for optimal throughput.

Prerequisites

Before you begin, make sure you have:

  • Ubuntu Server 20.04, 22.04, or 24.04 LTS
  • Terminal access with sudo privileges
  • At least 1 GB of RAM (2 GB or more recommended for production)
  • Basic familiarity with SQL and Linux command-line operations
  • A firewall configured (see our UFW guide for setup)

What Is PostgreSQL?

PostgreSQL (often called “Postgres”) is an object-relational database management system (ORDBMS) that has been in active development for over 35 years. Originally developed at the University of California, Berkeley, it is now maintained by a global community of contributors.

Key characteristics of PostgreSQL:

  • ACID compliant — full support for atomicity, consistency, isolation, and durability
  • MVCC (Multi-Version Concurrency Control) — readers never block writers and writers never block readers
  • Extensible — custom data types, operators, functions, and index methods
  • Standards-compliant — closest implementation to the SQL standard among all databases
  • Advanced features — JSON/JSONB, full-text search, table partitioning, logical replication, CTEs, window functions
  • Robust security — row-level security, SSL/TLS, multiple authentication methods

Installing PostgreSQL on Ubuntu

Option 1: Install from Ubuntu Repositories

The simplest approach uses the default Ubuntu packages:

sudo apt update
sudo apt install postgresql postgresql-contrib

This installs the version bundled with your Ubuntu release (PostgreSQL 14 on 22.04, PostgreSQL 16 on 24.04).

Option 2: Install the Latest Version from the Official Repository

To get the latest PostgreSQL release (version 17 at the time of writing), add the official PostgreSQL APT repository:

sudo apt install -y gnupg2 wget
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-archive-keyring.gpg

Update the repository references in the sources list to use the signed keyring:

sudo sed -i 's|deb http://apt.postgresql.org|deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org|' /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-17 postgresql-contrib-17

Verify the Installation

After installation, PostgreSQL starts automatically. Verify it is running:

sudo systemctl status postgresql

Check the installed version:

psql --version

Expected output:

psql (PostgreSQL) 17.x

Initial Configuration

PostgreSQL stores its configuration in two primary files. On Ubuntu, these are located at /etc/postgresql/<version>/main/.

postgresql.conf — Server Configuration

This file controls the server behavior, performance settings, logging, and connection parameters:

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

Key settings to review immediately:

# Connection settings
listen_addresses = 'localhost'    # Change to '*' or specific IP for remote access
port = 5432                       # Default PostgreSQL port
max_connections = 100             # Maximum simultaneous connections

# Memory settings (tune based on your server RAM)
shared_buffers = 256MB            # Start with 25% of total RAM
work_mem = 4MB                    # Per-operation sort memory
maintenance_work_mem = 128MB      # For VACUUM, CREATE INDEX

# Write-Ahead Logging
wal_level = replica               # Enables replication and PITR
max_wal_size = 1GB
min_wal_size = 80MB

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'             # Log DDL statements
log_min_duration_statement = 1000 # Log queries taking longer than 1 second

pg_hba.conf — Client Authentication

This file controls who can connect, from where, and how they authenticate:

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

The default configuration typically looks like:

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
local   all       all                       peer
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

After making changes to either file, restart PostgreSQL:

sudo systemctl restart postgresql

Or reload without restarting (for most pg_hba.conf changes):

sudo systemctl reload postgresql

Authentication Methods

PostgreSQL supports multiple authentication methods. Understanding them is essential for securing your database.

peer Authentication

Maps the operating system username to a PostgreSQL role. If you are logged in as the postgres system user, you can connect to the postgres database role without a password:

sudo -u postgres psql

This is the default for local Unix socket connections and is secure because it relies on the OS authentication.

md5 Authentication

Uses MD5-hashed passwords. While still functional, it is considered legacy:

host    all    all    192.168.1.0/24    md5

The most secure password-based method, using the SCRAM-SHA-256 challenge-response mechanism:

host    all    all    192.168.1.0/24    scram-sha-256

To ensure new passwords use SCRAM-SHA-256, set this in postgresql.conf:

password_encryption = scram-sha-256

Important: If you switch from md5 to scram-sha-256, existing user passwords must be reset because the stored hash format is different.

Creating Databases and Users

Connect to PostgreSQL as the superuser:

sudo -u postgres psql

Create a New Role (User)

CREATE ROLE appuser WITH LOGIN PASSWORD 'StrongPassword123!';

Grant specific capabilities:

ALTER ROLE appuser CREATEDB;

Create a Database

CREATE DATABASE myappdb OWNER appuser;

Or from the command line:

sudo -u postgres createdb -O appuser myappdb

Grant Privileges

GRANT ALL PRIVILEGES ON DATABASE myappdb TO appuser;

For more granular control on schemas and tables:

\c myappdb
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

Connect as the New User

psql -U appuser -d myappdb -h localhost

Remote Access Configuration

By default, PostgreSQL only accepts connections from localhost. To enable remote access:

Step 1: Update listen_addresses

Edit postgresql.conf:

listen_addresses = '*'

Or restrict to a specific interface:

listen_addresses = '192.168.1.10'

Step 2: Add pg_hba.conf Entry

Allow a specific subnet:

host    myappdb    appuser    192.168.1.0/24    scram-sha-256

Allow a single host:

host    all    all    10.0.0.50/32    scram-sha-256

Step 3: Configure the Firewall

Allow PostgreSQL through UFW:

sudo ufw allow from 192.168.1.0/24 to any port 5432

Or allow from anywhere (not recommended for production):

sudo ufw allow 5432/tcp

Step 4: Restart and Test

sudo systemctl restart postgresql

Test from the remote machine:

psql -h 192.168.1.10 -U appuser -d myappdb

Essential psql Commands

The psql interactive terminal is the primary tool for working with PostgreSQL. Here are the commands you will use daily:

Connecting

# Connect as postgres superuser
sudo -u postgres psql

# Connect to a specific database
psql -U appuser -d myappdb -h localhost

# Connect with a connection string
psql "postgresql://appuser:password@localhost:5432/myappdb"
-- List all databases
\l

-- Connect to a different database
\c myappdb

-- List all tables in the current schema
\dt

-- Describe a table structure
\d tablename

-- List all schemas
\dn

-- List all roles/users
\du

-- Show current connection info
\conninfo

Query Execution

-- Run a query
SELECT * FROM users LIMIT 10;

-- Enable expanded display for wide tables
\x auto

-- Time query execution
\timing on

-- Execute a SQL file
\i /path/to/script.sql

-- Save query output to a file
\o /tmp/output.txt
SELECT * FROM users;
\o

Exiting

-- Quit psql
\q

Backup and Restore

A solid backup strategy is non-negotiable for production databases.

pg_dump — Logical Backups

Export a single database:

# SQL format (human-readable)
sudo -u postgres pg_dump myappdb > /backups/myappdb_$(date +%Y%m%d).sql

# Custom format (compressed, supports parallel restore)
sudo -u postgres pg_dump -Fc myappdb > /backups/myappdb_$(date +%Y%m%d).dump

# Only specific tables
sudo -u postgres pg_dump -t users -t orders myappdb > /backups/tables_$(date +%Y%m%d).sql

Export all databases:

sudo -u postgres pg_dumpall > /backups/all_databases_$(date +%Y%m%d).sql

pg_restore — Restoring Backups

Restore from custom format:

sudo -u postgres pg_restore -d myappdb /backups/myappdb_20260128.dump

Restore with parallel jobs for faster recovery:

sudo -u postgres pg_restore -j 4 -d myappdb /backups/myappdb_20260128.dump

Restore from SQL format:

sudo -u postgres psql myappdb < /backups/myappdb_20260128.sql

pg_basebackup — Physical Backups

For point-in-time recovery (PITR) and replication setup:

sudo -u postgres pg_basebackup -D /backups/base -Ft -Xs -P

Options explained:

  • -D — destination directory
  • -Ft — tar format
  • -Xs — stream WAL files during backup
  • -P — show progress

Automated Backup Script

Create a cron job for daily backups:

sudo nano /usr/local/bin/pg_backup.sh
#!/bin/bash
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

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

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $TIMESTAMP"
sudo chmod +x /usr/local/bin/pg_backup.sh
sudo crontab -e

Add the cron entry:

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Performance Tuning

PostgreSQL performance depends heavily on proper configuration. The default settings are conservative and designed to run on minimal hardware.

shared_buffers

The most important memory parameter. Sets how much memory PostgreSQL uses for caching data blocks:

# Recommended: 25% of total system RAM
# For a server with 16 GB RAM:
shared_buffers = 4GB

work_mem

Memory allocated for each sort operation, hash join, or similar operation. Be careful — this is per-operation, not per-connection:

# For OLTP workloads with many connections:
work_mem = 4MB

# For analytical queries with fewer connections:
work_mem = 64MB

effective_cache_size

Tells the query planner how much memory is available for caching (OS cache + shared_buffers). This does not allocate memory — it only influences query planning:

# Recommended: 50-75% of total system RAM
# For a server with 16 GB RAM:
effective_cache_size = 12GB

WAL Configuration

Write-Ahead Logging settings significantly impact write performance:

# Checkpoint settings
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# For write-heavy workloads
wal_buffers = 64MB
synchronous_commit = on          # Set to 'off' only if you can tolerate minimal data loss

Connection and Parallelism

# Maximum connections (each consumes ~10MB RAM)
max_connections = 200

# Parallel query execution
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_worker_processes = 8

After Tuning

Apply changes by restarting PostgreSQL:

sudo systemctl restart postgresql

Verify settings:

SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

Monitoring with pg_stat_activity

The pg_stat_activity view provides real-time information about all active connections and queries.

View Active Queries

SELECT pid, usename, datname, state, query, query_start,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Find Long-Running Queries

SELECT pid, usename, datname, query,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

Cancel or Terminate a Query

-- Graceful cancel (sends SIGINT)
SELECT pg_cancel_backend(12345);

-- Force terminate (sends SIGTERM)
SELECT pg_terminate_backend(12345);

Database Statistics

-- Database-level statistics
SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit,
       round(blks_hit::numeric / (blks_read + blks_hit) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = 'myappdb';

Table Statistics

-- Table access statistics
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Lock Monitoring

-- View current locks
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

psql Commands Reference Table

CommandDescription
\lList all databases
\c dbnameConnect to a database
\dtList tables in current schema
\dt+List tables with sizes
\d tablenameDescribe a table
\diList indexes
\dvList views
\dfList functions
\duList roles/users
\dnList schemas
\dpList table access privileges
\xToggle expanded display
\timingToggle query timing
\i file.sqlExecute a SQL file
\o file.txtSend output to file
\eOpen query in editor
\copyClient-side COPY command
\passwordChange a user password
\conninfoShow connection info
\qQuit psql

Troubleshooting

PostgreSQL Refuses to Start

Check the logs for errors:

sudo journalctl -u postgresql -n 50
sudo cat /var/log/postgresql/postgresql-17-main.log

Common causes:

  • Port 5432 already in use by another instance
  • Corrupted configuration files (syntax errors in postgresql.conf)
  • Insufficient shared memory — check shared_buffers value

Connection Refused

If you get psql: error: connection refused:

# Verify PostgreSQL is running
sudo systemctl status postgresql

# Check which address and port it is listening on
sudo ss -tlnp | grep 5432

# Verify pg_hba.conf allows your connection
sudo cat /etc/postgresql/17/main/pg_hba.conf

Authentication Failed

FATAL: password authentication failed for user "appuser"

Verify the password and authentication method:

# Check the auth method in pg_hba.conf
sudo grep -v '^#' /etc/postgresql/17/main/pg_hba.conf | grep -v '^$'

# Reset password via peer auth
sudo -u postgres psql -c "ALTER USER appuser PASSWORD 'NewPassword123!';"

Slow Queries

Enable logging of slow queries:

# In postgresql.conf
log_min_duration_statement = 500  # Log queries taking more than 500ms

Use EXPLAIN ANALYZE to diagnose:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Look for sequential scans on large tables and add indexes where needed:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Disk Space Issues

Check database sizes:

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Find the largest tables:

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Run VACUUM to reclaim space:

sudo -u postgres vacuumdb --all --analyze

Summary

PostgreSQL is a powerful, production-ready database that rewards proper configuration. In this guide, you learned how to install PostgreSQL from the official repository, configure authentication with scram-sha-256, create databases and roles, enable remote access safely, implement backup strategies with pg_dump and pg_basebackup, tune performance parameters for your hardware, and monitor database activity with pg_stat_activity.

For a complete server hardening approach, pair this setup with a properly configured firewall — see our UFW Firewall guide for detailed instructions. You should also review our Linux Server Security Checklist to ensure your entire server stack follows security best practices from the OS level up.

Next steps to consider:

  • Set up streaming replication for high availability
  • Implement connection pooling with PgBouncer for high-traffic applications
  • Configure SSL/TLS connections for encrypted client-server communication
  • Explore logical replication for zero-downtime migrations