PostgreSQL Database Server on Ubuntu

Trademark Notice

PostgreSQL® is a registered trademark of the PostgreSQL Global Development Group. All trademarks are used for representation purposes only. This content is for educational purposes only.

Complete guide to installing and configuring PostgreSQL on Ubuntu Server.

Installation

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install -y postgresql postgresql-contrib

# Check status
sudo systemctl status postgresql

# Verify version
psql --version

Initial Configuration

Access PostgreSQL

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL prompt
psql

# Or in one command
sudo -u postgres psql

Create Database and User

-- Create database
CREATE DATABASE myapp_db;

-- Create user with password
CREATE USER myapp_user WITH PASSWORD 'strong_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Grant schema privileges (PostgreSQL 15+)
\c myapp_db
GRANT ALL ON SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myapp_user;

-- Set default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myapp_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO myapp_user;

-- List databases
\l

-- List users
\du

-- Exit
\q

Configuration Files

/etc/postgresql/14/main/
├── postgresql.conf       # Main configuration
├── pg_hba.conf          # Client authentication
└── pg_ident.conf        # User mapping

Main Configuration

# Edit main config
sudo nano /etc/postgresql/14/main/postgresql.conf

# Connection Settings
listen_addresses = '*'              # Listen on all interfaces
port = 5432
max_connections = 200

# Memory Settings
shared_buffers = 256MB              # 25% of RAM
effective_cache_size = 1GB          # 50-75% of RAM
maintenance_work_mem = 64MB
work_mem = 4MB

# Write Ahead Log (WAL)
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
wal_buffers = 16MB

# Query Planning
random_page_cost = 1.1              # For SSD
effective_io_concurrency = 200      # For SSD

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_min_duration_statement = 1000   # Log queries slower than 1s

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3

Client Authentication

# Edit authentication config
sudo nano /etc/postgresql/14/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5

# Remote connections
host    all             all             0.0.0.0/0               md5
host    all             all             192.168.1.0/24          md5

# IPv6 connections
host    all             all             ::1/128                 md5

# SSL connections
hostssl all             all             0.0.0.0/0               md5

# Restart PostgreSQL
sudo systemctl restart postgresql

# Open firewall
sudo ufw allow 5432/tcp

User and Role Management

-- Create user
CREATE USER username WITH PASSWORD 'password';

-- Create user with attributes
CREATE USER admin_user WITH 
    PASSWORD 'secure_password'
    SUPERUSER
    CREATEDB
    CREATEROLE
    LOGIN;

-- Alter user
ALTER USER username WITH PASSWORD 'new_password';
ALTER USER username WITH SUPERUSER;
ALTER USER username VALID UNTIL '2025-12-31';

-- Create role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Assign role to user
GRANT readonly TO username;

-- Remove user
DROP USER username;

-- List users and roles
\du
SELECT * FROM pg_user;
SELECT * FROM pg_roles;

Database Operations

-- Create database
CREATE DATABASE dbname 
    WITH OWNER = dbuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- List databases
\l
SELECT datname FROM pg_database;

-- Connect to database
\c dbname

-- Rename database
ALTER DATABASE oldname RENAME TO newname;

-- Drop database
DROP DATABASE dbname;

-- Show tables
\dt
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Describe table
\d tablename
\d+ tablename

-- Show database size
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;

-- Show table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Backup and Restore

Using pg_dump

# Backup single database
sudo -u postgres pg_dump dbname > backup_$(date +%Y%m%d).sql

# Backup with compression
sudo -u postgres pg_dump dbname | gzip > backup_$(date +%Y%m%d).sql.gz

# Backup in custom format (faster restore)
sudo -u postgres pg_dump -Fc dbname > backup_$(date +%Y%m%d).dump

# Backup specific tables
sudo -u postgres pg_dump -t table1 -t table2 dbname > tables_backup.sql

# Backup schema only
sudo -u postgres pg_dump -s dbname > schema_$(date +%Y%m%d).sql

# Backup data only
sudo -u postgres pg_dump -a dbname > data_$(date +%Y%m%d).sql

# Backup all databases
sudo -u postgres pg_dumpall > all_databases_$(date +%Y%m%d).sql

# Backup only global objects (roles, tablespaces)
sudo -u postgres pg_dumpall --globals-only > globals_$(date +%Y%m%d).sql

Restore Database

# Restore from SQL dump
sudo -u postgres psql dbname < backup.sql

# Restore from compressed backup
gunzip -c backup.sql.gz | sudo -u postgres psql dbname

# Restore from custom format
sudo -u postgres pg_restore -d dbname backup.dump

# Restore with parallel jobs (faster)
sudo -u postgres pg_restore -j 4 -d dbname backup.dump

# Restore specific table
sudo -u postgres pg_restore -t table_name -d dbname backup.dump

# Restore all databases
sudo -u postgres psql -f all_databases.sql

# Create database and restore
sudo -u postgres createdb newdb
sudo -u postgres psql newdb < backup.sql

Automated Backup Script

# Create backup script
sudo nano /usr/local/bin/postgresql-backup.sh

#!/bin/bash

BACKUP_DIR="/backup/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

mkdir -p $BACKUP_DIR

# Backup all databases
sudo -u postgres pg_dumpall | gzip > $BACKUP_DIR/all_databases_$TIMESTAMP.sql.gz

# Backup individual databases
for DB in $(sudo -u postgres psql -t -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 old backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $TIMESTAMP"

# Make executable
sudo chmod +x /usr/local/bin/postgresql-backup.sh

# Add to cron
sudo crontab -e

0 2 * * * /usr/local/bin/postgresql-backup.sh >> /var/log/postgresql-backup.log 2>&1

Replication Setup

Primary Server Configuration

# Edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

# Replication settings
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

-- Create replication user
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'repl_password';

# Edit pg_hba.conf
sudo nano /etc/postgresql/14/main/pg_hba.conf

# Replication connection
host    replication     replicator      192.168.1.0/24          md5

# Restart PostgreSQL
sudo systemctl restart postgresql

Replica Server Setup

# Stop PostgreSQL on replica
sudo systemctl stop postgresql

# Remove old data directory
sudo rm -rf /var/lib/postgresql/14/main/*

# Clone primary server
sudo -u postgres pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/14/main -U replicator -P -v -R -X stream -C -S replica1

# Start replica
sudo systemctl start postgresql

# Verify replication status on primary
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

Performance Tuning

Connection Pooling with PgBouncer

# Install PgBouncer
sudo apt install -y pgbouncer

# Configure PgBouncer
sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5
max_db_connections = 100

# Create user list
echo '"myapp_user" "md5<password_hash>"' | sudo tee /etc/pgbouncer/userlist.txt

# Start PgBouncer
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Index Optimization

-- Find missing indexes
SELECT
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Find unused indexes
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created_at);

-- Reindex
REINDEX TABLE tablename;
REINDEX DATABASE dbname;

Vacuum and Analyze

-- Manual vacuum
VACUUM VERBOSE tablename;
VACUUM FULL tablename;

-- Analyze for query planning
ANALYZE tablename;
ANALYZE;

-- Vacuum and analyze
VACUUM ANALYZE tablename;

-- Show dead tuples
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Monitoring

Query Performance

-- Enable pg_stat_statements extension
CREATE EXTENSION pg_stat_statements;

-- View slow queries
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

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

-- Kill query
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);

-- Connection statistics
SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    round((blks_hit::numeric / NULLIF(blks_hit + blks_read, 0)) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database;

System Views

-- Database size
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

-- Table bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Replication lag
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

Security

SSL/TLS Configuration

# Generate SSL certificates
sudo openssl req -new -x509 -days 365 -nodes -text \
    -out /etc/postgresql/14/main/server.crt \
    -keyout /etc/postgresql/14/main/server.key \
    -subj "/CN=postgresql.example.com"

sudo chown postgres:postgres /etc/postgresql/14/main/server.{crt,key}
sudo chmod 600 /etc/postgresql/14/main/server.key

# Enable SSL in postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/postgresql/14/main/server.crt'
ssl_key_file = '/etc/postgresql/14/main/server.key'

# Require SSL in pg_hba.conf
sudo nano /etc/postgresql/14/main/pg_hba.conf

hostssl all all 0.0.0.0/0 md5

Password Policy

-- Install passwordcheck extension
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- Set password encryption
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

Troubleshooting

# Check PostgreSQL logs
sudo tail -f /var/log/postgresql/postgresql-14-main.log

# Check status
sudo systemctl status postgresql

# Check connections
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

# Check listening ports
sudo ss -tlnp | grep postgres

# Restart PostgreSQL
sudo systemctl restart postgresql

# Check configuration
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

Common Issues

Connection refused:

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check listen_addresses
sudo -u postgres psql -c "SHOW listen_addresses;"

# Check pg_hba.conf
sudo cat /etc/postgresql/14/main/pg_hba.conf

Out of shared memory:

# Increase shared_buffers
sudo nano /etc/postgresql/14/main/postgresql.conf
# shared_buffers = 512MB

sudo systemctl restart postgresql

Best Practices

1. Regular Backups: Automate daily backups with retention policy
2. Monitoring: Monitor query performance and slow queries
3. Indexes: Create appropriate indexes for common queries
4. Vacuum: Ensure autovacuum is running
5. Connection Pooling: Use PgBouncer for high-traffic applications
6. Replication: Set up streaming replication for high availability
7. Security: Use SSL/TLS, strong passwords, limit network access
8. Updates: Keep PostgreSQL updated with security patches
9. Resource Limits: Configure shared_buffers and work_mem appropriately
10. Documentation: Document schema changes and procedures

Resources

• Official Documentation: https://www.postgresql.org/docs/
• Ubuntu PostgreSQL Guide: https://ubuntu.com/server/docs/databases-postgresql
• Performance: https://pgtune.leopard.in.ua/
• Tools: pgAdmin, DBeaver, psql