PostgreSQL on Linux: Complete Administration Guide from Installation to Production

PostgreSQL is the world's most advanced open-source relational database, and Linux is its natural home. Whether you are building a small web application or managing enterprise data, PostgreSQL delivers reliability, performance, and features that rival commercial databases costing thousands of dollars.

This guide covers everything you need to know to install, configure, secure, and optimize PostgreSQL on Linux for production use.

Installing PostgreSQL on Linux

# Ubuntu/Debian — Install the latest version
sudo apt update
sudo apt install postgresql postgresql-contrib

# RHEL/AlmaLinux
sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

# Start and enable the service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify it is running
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT version();"

Understanding PostgreSQL Architecture

User and Database Management

# Switch to postgres system user
sudo -u postgres psql

-- Create a new database user
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';

-- Create a database owned by that user
CREATE DATABASE myapp OWNER appuser;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Create a read-only user for reporting
CREATE USER reporter WITH PASSWORD 'ReadOnly456!';
GRANT CONNECT ON DATABASE myapp TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

-- List all databases
\l

-- List all users/roles
\du

-- Exit psql
\q

Essential SQL for Daily Administration

-- Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Check table sizes in current database
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(tablename::text)) as total_size,
       pg_size_pretty(pg_relation_size(tablename::text)) as data_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::text) DESC;

-- Find slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- Active connections
SELECT pid, usename, datname, client_addr, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Kill a stuck query
SELECT pg_cancel_backend(pid);
-- Or force terminate
SELECT pg_terminate_backend(pid);

Indexing for Performance

-- Create a B-tree index (most common)
CREATE INDEX idx_users_email ON users(email);

-- Create a unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (only index active users)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- GIN index for full-text search
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body));

-- Check if indexes are being used
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find missing indexes (tables with high sequential scans)
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;

Backup and Restore

# Backup a single database (SQL dump)
pg_dump -U postgres myapp > myapp_backup.sql

# Backup with compression
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Backup all databases
pg_dumpall -U postgres > all_databases.sql

# Restore from SQL dump
psql -U postgres myapp < myapp_backup.sql

# Restore from compressed dump
pg_restore -U postgres -d myapp myapp_backup.dump

# Automated daily backup script
#!/bin/bash
BACKUP_DIR="/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

pg_dump -U postgres -Fc myapp > "$BACKUP_DIR/myapp_$DATE.dump"

# Keep only last 7 days
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete
echo "Backup completed: myapp_$DATE.dump"

Security Hardening

# Edit pg_hba.conf — Control who can connect
# TYPE  DATABASE  USER       ADDRESS          METHOD
local   all       postgres                    peer
host    myapp     appuser    10.0.0.0/24      scram-sha-256
host    all       all        0.0.0.0/0        reject

# In postgresql.conf — Restrict listening
listen_addresses = 'localhost,10.0.0.5'    # Only specific IPs
port = 5432
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

# Set password encryption
password_encryption = scram-sha-256

# Connection limits
max_connections = 100
superuser_reserved_connections = 3

# Reload after changes
sudo systemctl reload postgresql

Performance Tuning

# Key postgresql.conf settings (adjust for your server RAM)
# For a server with 16GB RAM:

shared_buffers = '4GB'              # 25% of RAM
effective_cache_size = '12GB'       # 75% of RAM
work_mem = '64MB'                   # Per-query sort memory
maintenance_work_mem = '512MB'      # For VACUUM, CREATE INDEX
wal_buffers = '64MB'

# Query planner
random_page_cost = 1.1              # For SSD storage (default 4.0)
effective_io_concurrency = 200      # For SSD

# WAL settings
wal_level = 'replica'
max_wal_size = '2GB'
min_wal_size = '1GB'

# Autovacuum (keep default, but tune if needed)
autovacuum = on
autovacuum_max_workers = 3

Monitoring and Health Checks

-- Database hit ratio (should be > 99%)
SELECT datname,
       round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) as cache_hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0;

-- Table bloat check
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Replication status (if configured)
SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn
FROM pg_stat_replication;

Useful psql Commands

Conclusion

PostgreSQL on Linux is a world-class database platform that can handle everything from small applications to massive enterprise workloads. Master the fundamentals — user management, indexing, backups, and security — and you will have a rock-solid foundation for any data-driven project.