PostgreSQL for Beginners: Installation, Configuration, and Performance Tuning

Introduction: Why PostgreSQL?

PostgreSQL has earned its reputation as the world's most advanced open-source relational database — and for good reason. Over 35 years of active development have produced a database that handles everything from simple web applications to multi-terabyte data warehouses, geospatial data, time-series workloads, and full-text search, all within a single system. No plugins required.

As a server administrator, you'll encounter PostgreSQL constantly. Modern applications — from Django to Laravel to Rails to Go microservices — increasingly prefer it over MySQL (see our full PostgreSQL vs MySQL comparison for the complete picture). Managed services like AWS RDS, Google Cloud SQL, and Heroku have made it a default choice. Even tools like GitLab and Roundcube ship PostgreSQL support as first-class.

PostgreSQL vs MySQL: When to Choose Which

Rule of thumb: New application? Start with PostgreSQL. Legacy PHP app that expects MySQL? Use MySQL. Many servers run both — that's perfectly fine.

Who This Guide Is For

This guide is written for server administrators and developers comfortable with Linux but without deep database administration experience. We cover installation, configuration, daily operations, backup and restore, performance tuning, and monitoring — everything needed to run PostgreSQL confidently in production.

1. Installing PostgreSQL on Ubuntu/Debian

Ubuntu and Debian ship PostgreSQL in their default repositories, but the versions are often behind. The official PostgreSQL Global Development Group (PGDG) repository gives you the latest stable release and clean upgrade paths.

Step 1: Add the Official Repository

# Install required tools
sudo apt install -y curl gnupg

# Add PostgreSQL signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg

# Add repository (Ubuntu 24.04 / noble)
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] \
  https://apt.postgresql.org/pub/repos/apt noble-pgdg main" | \
  sudo tee /etc/apt/sources.list.d/pgdg.list

# Update and install
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17

Replace noble with your distro codename: Ubuntu 24.04 = noble, Ubuntu 22.04 = jammy, Debian 12 = bookworm, Debian 11 = bullseye.

Step 2: Verify Installation

# Check version
psql --version
# postgresql (PostgreSQL) 17.x

# Check service status
sudo systemctl status postgresql

Default Setup After Installation

PostgreSQL automatically creates a system user named postgres, initializes a database cluster at /var/lib/postgresql/17/main/, starts listening on localhost:5432, and configures peer authentication for the postgres system user.

# Connect immediately after installation (no password needed via peer auth)
sudo -u postgres psql

2. Understanding PostgreSQL Architecture

Before configuring PostgreSQL, understanding how it works internally makes configuration decisions much clearer.

The Postmaster Process

When PostgreSQL starts, it launches a postmaster process. This is the parent process that listens for incoming connections, forks a dedicated backend process for each new client connection, and manages background workers.

This is a key architectural difference from MySQL's thread-per-connection model. PostgreSQL uses processes, not threads. Each connection = one OS process. This is why connection pooling matters so much for PostgreSQL.

Background Processes

• autovacuum launcher — Manages automatic cleanup of dead tuples
• WAL writer — Writes ahead-log entries to disk
• checkpointer — Periodically flushes dirty pages from shared buffers to disk
• background writer — Proactively writes dirty pages before checkpoint
• stats collector — Gathers activity statistics
• wal archiver — Archives WAL files (if enabled)

Shared Buffers

PostgreSQL's primary memory cache is shared_buffers. All backend processes share this memory pool to cache frequently accessed data pages. Think of it as PostgreSQL's equivalent of MySQL's InnoDB buffer pool. When data is read from disk, it's cached here. Subsequent reads serve from memory. Writes go to shared buffers first, then get flushed to disk by the checkpointer.

Write-Ahead Log (WAL)

Before any data modification is written to the actual data files, PostgreSQL writes a record to the Write-Ahead Log. This guarantees durability (committed transactions survive crashes), point-in-time recovery (replay WAL to restore to any past state), and streaming replication (WAL sent to standby servers in real time). WAL files live in $PGDATA/pg_wal/. Each WAL segment is 16MB by default.

Data Directory Layout

/var/lib/postgresql/17/main/
├── PG_VERSION          # Version identifier
├── base/               # Database files (one subdirectory per database)
├── global/             # Cluster-wide tables (pg_database, pg_authid, etc.)
├── pg_hba.conf         # Client authentication rules
├── pg_ident.conf       # Username mapping
├── pg_wal/             # Write-ahead log files
├── pg_stat_tmp/        # Transient statistics files
└── postmaster.pid      # Running postmaster PID

On Debian/Ubuntu, configuration files live in /etc/postgresql/17/main/ with symlinks back to the data directory.

3. Essential Configuration Files

postgresql.conf — Main Configuration

This file controls every aspect of PostgreSQL behavior. After most changes, reload the configuration:

sudo systemctl reload postgresql
# or inside psql:
SELECT pg_reload_conf();

Some parameters require a full restart (those marked as postmaster context in the docs).

Connection Settings

# Who can connect (default: 'localhost')
# Use '*' to allow all interfaces — secure with pg_hba.conf
listen_addresses = 'localhost'

# Default port
port = 5432

# Maximum simultaneous connections
# Each connection = one OS process — set this carefully
max_connections = 100

Memory Settings (Critical)

# Primary cache: 25% of total RAM is the starting point
# For a 16GB server: shared_buffers = 4GB
shared_buffers = 4GB

# Hint to the query planner about available OS cache
# Set to 50-75% of total RAM
effective_cache_size = 12GB

# Memory per sort/hash operation, per query operation
# Formula: total_RAM / max_connections / 4
# For 16GB, 100 connections: ~40MB
work_mem = 40MB

# For VACUUM, CREATE INDEX, ALTER TABLE
maintenance_work_mem = 512MB

# WAL buffer — explicit is better than auto
wal_buffers = 64MB

Checkpoint Settings

# Spread checkpoint I/O over this fraction of checkpoint interval
# 0.9 means 90% of checkpoint_timeout — reduces write spikes
checkpoint_completion_target = 0.9

# Maximum time between checkpoints
checkpoint_timeout = 10min

SSD Optimization

# random_page_cost: SSD = 1.1, HDD = 4.0
# Tells the query planner how expensive random reads are
random_page_cost = 1.1

# effective_io_concurrency: SSD = 200, HDD = 2
effective_io_concurrency = 200

# More detailed statistics for better query plans
default_statistics_target = 100

Logging (Recommended for Production)

# Log queries slower than 1 second
log_min_duration_statement = 1000

# Enable log collection
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d

pg_hba.conf — Client Authentication

This file controls who can connect, from where, to which database, and how they authenticate. It's evaluated top-to-bottom; the first matching rule wins.

Format

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

Authentication Methods

Common pg_hba.conf Patterns

# postgres superuser via Unix socket (no password)
local   all       postgres                        peer

# All local users via Unix socket with password
local   all       all                             scram-sha-256

# Localhost connections with password
host    all       all       127.0.0.1/32          scram-sha-256
host    all       all       ::1/128               scram-sha-256

# Specific application user from specific IP only
host    myapp     myapp_user 192.168.1.100/32     scram-sha-256

# Allow a subnet (internal network)
host    all       all       10.0.0.0/8            scram-sha-256

# Explicitly reject a specific IP
host    all       all       1.2.3.4/32            reject

Common mistakes: using trust for local connections; forgetting IPv6 entries (::1/128 alongside 127.0.0.1/32); wrong rule order (more specific rules before more general ones); using md5 instead of scram-sha-256.

After editing pg_hba.conf, reload — no restart needed:

sudo systemctl reload postgresql

4. Basic Operations

Connecting to PostgreSQL

# As postgres superuser (peer auth)
sudo -u postgres psql

# Connect to specific database
sudo -u postgres psql -d myapp

# Connect as specific user with password
psql -U myuser -d myapp -h localhost

# Connection string format
psql "postgresql://myuser:mypassword@localhost:5432/myapp"

Creating Databases and Users

-- Connect as postgres first
sudo -u postgres psql

-- Create a new database
CREATE DATABASE myapp;

-- Create a user with a password
CREATE USER myuser WITH PASSWORD 'your-strong-password-here';

-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

-- Grant schema usage (required in PostgreSQL 15+)
\c myapp
GRANT ALL ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

Creating a Read-Only Reporting User

CREATE USER reporter WITH PASSWORD 'readonly-password';
GRANT CONNECT ON DATABASE myapp TO reporter;
\c myapp
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;

-- Auto-grant for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

Essential psql Commands

5. Essential SQL for Server Admins

Table Creation with Proper Types

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    uuid        UUID DEFAULT gen_random_uuid(),
    email       VARCHAR(255) UNIQUE NOT NULL,
    username    VARCHAR(64) NOT NULL,
    password    TEXT NOT NULL,
    is_active   BOOLEAN DEFAULT true,
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Standard B-tree index on email
CREATE INDEX idx_users_email ON users(email);

-- Partial index (only active users)
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;

-- JSONB index for a specific key
CREATE INDEX idx_users_metadata_role ON users((metadata->>'role'));

Index Types — When to Use Which

Choosing the right index type has an outsized effect on query speed. For a deeper dive into how indexes work under the hood, see Database Indexing Explained.

EXPLAIN ANALYZE — Understanding Query Plans

-- Basic EXPLAIN (shows plan, does not execute)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- EXPLAIN ANALYZE (runs query and shows actual timings)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Full detail with buffer hit information
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
  SELECT u.id, u.email, COUNT(o.id) AS order_count
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.email
  ORDER BY order_count DESC
  LIMIT 10;

Reading EXPLAIN output: look for Seq Scan on large tables (indicates a missing index), high estimated rows vs actual rows (outdated statistics — run ANALYZE), and high cost values.

Useful Admin Queries

-- Active connections and what they are doing
SELECT pid, usename, application_name, client_addr, state,
       now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Database sizes
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Table sizes including indexes
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
       pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
       pg_size_pretty(pg_indexes_size(tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

-- Long-running queries (over 5 minutes)
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - query_start) > interval '5 minutes'
  AND state != 'idle';

-- Kill a specific query (soft cancel — waits for checkpoint)
SELECT pg_cancel_backend(pid_number);

-- Kill a query (hard terminate — use if cancel does not work)
SELECT pg_terminate_backend(pid_number);

-- Check for table bloat (high dead tuple count = needs VACUUM)
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 > 1000
ORDER BY n_dead_tup DESC;

6. Backup and Restore

PostgreSQL ships with excellent backup tools. No third-party utilities required for solid backup coverage. If you also run MySQL alongside PostgreSQL, our MySQL backup and restore guide covers the equivalent workflow with mysqldump.

pg_dump — Single Database Backup

# Plain SQL format (human-readable, portable)
pg_dump -U postgres myapp > /backup/myapp_$(date +%Y%m%d).sql

# Custom format (compressed, faster restore, selective restore)
# Recommended for production
pg_dump -U postgres -Fc myapp > /backup/myapp_$(date +%Y%m%d).dump

# Compressed SQL
pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +%Y%m%d).sql.gz

# Include only specific tables
pg_dump -U postgres -t users -t orders myapp > /backup/users_orders.sql

# Schema only (no data)
pg_dump -U postgres -s myapp > /backup/myapp_schema.sql

# Data only (no schema)
pg_dump -U postgres -a myapp > /backup/myapp_data.sql

pg_dumpall — Entire Cluster Backup

# Backup all databases and global objects (roles, tablespaces)
pg_dumpall -U postgres > /backup/all_$(date +%Y%m%d).sql

# Globals only (users, roles — useful when migrating to a new server)
pg_dumpall -U postgres -g > /backup/globals.sql

Restoring Backups

# Restore from plain SQL
psql -U postgres myapp < /backup/myapp_20260315.sql

# Restore from custom format
pg_restore -U postgres -d myapp /backup/myapp_20260315.dump

# Restore with parallel jobs (much faster for large databases)
pg_restore -U postgres -d myapp -j 4 /backup/myapp_20260315.dump

# Restore from compressed SQL
gunzip -c /backup/myapp_20260315.sql.gz | psql -U postgres myapp

# Create database and restore in one step
createdb -U postgres myapp_restored
pg_restore -U postgres -d myapp_restored /backup/myapp_20260315.dump

Automated Backup Script with Cron

#!/bin/bash
# /opt/scripts/pg_backup.sh

BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"

mkdir -p "$BACKUP_DIR"
echo "$(date): Starting PostgreSQL backup" >> "$LOG_FILE"

DATABASES=$(sudo -u postgres psql -t -c \
  "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1') AND datallowconn = true;" \
  | tr -d ' ')

for DB in $DATABASES; do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.dump"
    sudo -u postgres pg_dump -Fc "$DB" > "$BACKUP_FILE"
    if [ $? -eq 0 ]; then
        echo "$(date): Backed up $DB -> $BACKUP_FILE ($(du -sh $BACKUP_FILE | cut -f1))" >> "$LOG_FILE"
    else
        echo "$(date): ERROR backing up $DB" >> "$LOG_FILE"
    fi
done

find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
echo "$(date): Backup complete. Removed files older than $RETENTION_DAYS days." >> "$LOG_FILE"

# Add to root crontab for daily 3 AM execution
sudo crontab -e
# Add this line:
0 3 * * * /opt/scripts/pg_backup.sh

WAL Archiving for Point-in-Time Recovery (PITR)

A base backup combined with WAL archiving allows you to restore to any point in time — not just the moment of your last backup. Essential for production systems where even 5 minutes of data loss is unacceptable.

# In postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/backups/pg_wal/%f'

# Take a base backup
sudo -u postgres pg_basebackup -D /var/backups/pg_base -Ft -z -P

# To restore to a specific point in time, create recovery configuration:
restore_command = 'cp /var/backups/pg_wal/%f %p'
recovery_target_time = '2026-03-15 14:30:00'

7. Performance Tuning

Default PostgreSQL configuration is deliberately conservative — it runs on everything from a Raspberry Pi to a 512GB RAM server. For production, tune it to your actual hardware.

Memory Tuning Reference

For a server with 16GB RAM and 100 max_connections:

# postgresql.conf
shared_buffers = 4GB                 # 25% of RAM
effective_cache_size = 12GB          # 75% of RAM
work_mem = 40MB                      # RAM / max_connections / 4
maintenance_work_mem = 512MB
wal_buffers = 64MB
max_connections = 100

For a 32GB RAM server:

shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 80MB
maintenance_work_mem = 1GB
wal_buffers = 64MB

Important: work_mem is per sort or hash operation, not per connection. A single complex query can use work_mem multiple times. With 100 connections and complex queries, actual memory use can be 100 x N x work_mem. Start conservative and measure before increasing.

Write Performance Settings

# Spread checkpoint I/O to reduce write spikes
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# SSD optimization
random_page_cost = 1.1
effective_io_concurrency = 200

# WARNING: data loss risk — only for non-critical data
# synchronous_commit = off

Connection Pooling with PgBouncer

PostgreSQL spawns a new OS process for each connection. At 200+ concurrent connections, you're consuming significant memory (5-10MB per backend process) and CPU on context switching. This is exactly the kind of bottleneck covered in our guide to database connection pooling. PgBouncer sits in front of PostgreSQL and multiplexes many application connections to a smaller pool of actual database connections.

# Install PgBouncer
sudo apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          # Best for most web apps
max_client_conn = 1000           # Application connections
default_pool_size = 25           # Actual PostgreSQL connections
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600

With PgBouncer, keep PostgreSQL max_connections = 50-100 even if your app has 500 concurrent users. PgBouncer handles the multiplexing.

VACUUM and autovacuum

PostgreSQL uses MVCC (Multi-Version Concurrency Control) — old row versions are kept for ongoing transactions. VACUUM reclaims space from dead tuples and updates visibility maps.

# postgresql.conf — autovacuum is enabled by default, leave it on
autovacuum = on
autovacuum_max_workers = 3
autovacuum_vacuum_scale_factor = 0.1   # Vacuum when 10% of rows are dead
autovacuum_analyze_scale_factor = 0.05 # Analyze when 5% of rows changed
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 400

-- Manual VACUUM (non-blocking)
VACUUM users;

-- VACUUM with statistics update
VACUUM ANALYZE users;

-- VACUUM FULL (rewrites table, reclaims disk to OS)
-- WARNING: locks the table — use only during maintenance windows
VACUUM FULL users;

-- Check VACUUM status
SELECT schemaname, tablename,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

8. Monitoring

PostgreSQL exposes rich runtime statistics through system views. No external tools required for basic monitoring.

Essential Monitoring Queries

-- Active connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

-- Connection usage vs limit
SELECT count(*), max_conn,
       round(count(*)::numeric / max_conn * 100, 1) AS usage_pct
FROM pg_stat_activity,
     (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') AS mc
GROUP BY max_conn;

-- Cache hit ratio (aim for 99%+ on well-tuned servers)
SELECT sum(heap_blks_read) AS disk_reads,
       sum(heap_blks_hit) AS cache_hits,
       round(sum(heap_blks_hit)::numeric /
             NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS cache_hit_pct
FROM pg_statio_user_tables;

-- Index usage (low idx_scan suggests unused index)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

-- Long-running queries (over 5 minutes)
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - query_start) > interval '5 minutes'
  AND state != 'idle';

-- Replication lag (if you have replicas)
SELECT client_addr, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) AS lag
FROM pg_stat_replication;

pg_stat_statements Extension

The most valuable monitoring extension — tracks execution statistics for every query type:

# In postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

# Restart PostgreSQL, then enable in your database:
CREATE EXTENSION pg_stat_statements;

-- Find the top 10 slowest queries by total execution time:
SELECT query, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Prometheus + postgres_exporter

For production monitoring with alerting, postgres_exporter exposes PostgreSQL metrics to Prometheus. Combined with Grafana, you get dashboards for connection counts, cache hit ratios, replication lag, query performance, and vacuum status — all without writing a single custom query.

Panelica ships Prometheus and Grafana as managed services, pre-configured out of the box. PostgreSQL monitoring integrates automatically through the panel's monitoring section.

9. Security Best Practices

Authentication

# Never use 'trust' for network connections
# BAD — anyone can connect without a password:
host    all    all    0.0.0.0/0    trust

# GOOD:
host    all    all    0.0.0.0/0    scram-sha-256

# Migrate existing users from md5 to scram-sha-256:
# First in postgresql.conf:
password_encryption = scram-sha-256
# Then reset each user's password:
ALTER USER myuser WITH PASSWORD 'new-password';

Network Security

# Only listen on necessary interfaces (postgresql.conf)
listen_addresses = 'localhost'
# or specific IPs:
listen_addresses = '127.0.0.1,10.0.0.1'

# Enable SSL for remote connections
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'
ssl_min_protocol_version = 'TLSv1.2'

# Require SSL in pg_hba.conf (hostssl instead of host):
hostssl    all    all    10.0.0.0/8    scram-sha-256

Principle of Least Privilege

-- Application users should NOT be superusers
CREATE USER webapp WITH PASSWORD 'strong-password';
GRANT CONNECT ON DATABASE myapp TO webapp;
GRANT USAGE ON SCHEMA public TO webapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO webapp;

-- Revoke default public schema creation (PostgreSQL 14 and earlier)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Never run application code as the postgres superuser

Audit Logging

# Log all DDL changes and privilege escalation
log_statement = 'ddl'

# For comprehensive auditing, install pgaudit:
# shared_preload_libraries = 'pgaudit'
# pgaudit.log = 'read,write,ddl,role'

10. Common Issues and Solutions

"FATAL: role does not exist"

# The PostgreSQL role does not match your Linux username
sudo -u postgres createuser --interactive
# or:
sudo -u postgres psql -c "CREATE USER yourusername WITH CREATEDB;"

"FATAL: database does not exist"

sudo -u postgres createdb myapp
# or:
sudo -u postgres psql -c "CREATE DATABASE myapp OWNER myuser;"

"could not connect to server: Connection refused"

# Check if PostgreSQL is running
sudo systemctl status postgresql

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

# Check logs for details
sudo tail -50 /var/log/postgresql/postgresql-17-main.log

"FATAL: too many connections"

# Option 1: Increase max_connections (requires restart)
# postgresql.conf: max_connections = 200

# Option 2: Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - interval '10 minutes';

# Option 3 (recommended): Deploy PgBouncer for connection pooling

"disk full" / WAL accumulation

# Check disk usage
df -h
du -sh /var/lib/postgresql/17/main/pg_wal/

# Check for stale replication slots (common cause of WAL accumulation)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

-- Drop a stale slot:
SELECT pg_drop_replication_slot('slot_name_here');

Slow Queries

-- Find the slow query and examine its plan
EXPLAIN ANALYZE SELECT ...your query...;

-- Look for Seq Scan on large tables — add an index:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- CONCURRENTLY avoids locking the table during the build

-- Update statistics if the query plan looks wrong:
ANALYZE orders;

PostgreSQL vs MySQL: Feature Comparison

Quick Reference Cheat Sheet

psql Connection Commands

# Connect as postgres superuser
sudo -u postgres psql

# Connect to specific database as specific user
psql -U myuser -d myapp -h localhost -p 5432

# Connection string
psql "postgresql://user:pass@host:5432/dbname"

# Execute a command without entering interactive mode
psql -U postgres -c "SELECT version();"

# Execute a SQL file
psql -U postgres -d myapp -f /path/to/script.sql

Essential SQL Operations

-- User management
CREATE USER name WITH PASSWORD 'pass';
ALTER USER name WITH PASSWORD 'newpass';
DROP USER name;

-- Database management
CREATE DATABASE name OWNER username;
DROP DATABASE name;
ALTER DATABASE name RENAME TO newname;

-- Privileges
GRANT ALL ON DATABASE name TO username;
GRANT SELECT, INSERT ON TABLE tablename TO username;
REVOKE ALL ON DATABASE name FROM username;

-- Schema operations
CREATE SCHEMA myschema;
SET search_path TO myschema, public;

-- Common maintenance
VACUUM ANALYZE tablename;
REINDEX TABLE tablename;

-- Transaction control
BEGIN;
SAVEPOINT my_savepoint;
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

Useful One-Liners

# Backup all databases
sudo -u postgres pg_dumpall | gzip > /backup/all_$(date +%Y%m%d).sql.gz

# List all databases with sizes
sudo -u postgres psql -c "\l+"

# Show config file locations
sudo -u postgres psql -c "SHOW config_file; SHOW hba_file;"

# Show settings that differ from defaults
sudo -u postgres psql -c \
  "SELECT name, setting, unit FROM pg_settings WHERE source != 'default' ORDER BY name;"

# Count connections per database
sudo -u postgres psql -c \
  "SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname ORDER BY count DESC;"

Managing PostgreSQL with Panelica

Everything covered in this guide — installation, user management, backup scheduling, performance monitoring, and SSL configuration — Panelica handles natively for every server it manages.

Panelica ships PostgreSQL 17 as a managed service in its fully isolated architecture. Each instance runs under /opt/panelica/ with a dedicated socket, data directory, and systemd service — completely separated from any system-level PostgreSQL installation. You can run Panelica's PostgreSQL alongside a system PostgreSQL without conflicts.

Key PostgreSQL management features in Panelica:

• pgAdmin 4 — Full-featured web-based administration with single sign-on. No separate login required.
• Automated backups — Per-database, per-user, or full-server scheduled backups with compression, remote storage (S3, SFTP, OneDrive, Google Drive), and one-click restore. See the complete backup and restore guide for details.
• Resource isolation — PostgreSQL processes run within cgroup v2 limits, preventing one heavy database from starving the entire server.
• Monitoring — Prometheus and Grafana dashboards for connection counts, query performance, and disk usage, pre-configured out of the box.
• Migration import — Direct database import from cPanel, Plesk, DirectAdmin, CyberPanel, and HestiaCP, preserving user credentials.

If you're setting up a new server, Panelica installs in under 3 minutes:

curl -sSL https://latest.panelica.com/install.sh | bash

All 20 services — including PostgreSQL 17 — configured and running in a single command.

Frequently Asked Questions

Is PostgreSQL better than MySQL?

Neither is universally better — they solve overlapping problems differently. PostgreSQL wins on SQL standards compliance, JSONB, and complex queries; MySQL remains the default for WordPress and many legacy PHP applications. Many production servers run both side by side.

How much RAM should I allocate to shared_buffers?

Start at 25% of total system RAM as a baseline, and set effective_cache_size to 50-75% of RAM. These are starting points, not final values — measure with pg_stat_statements and adjust based on your actual workload.

Do I need PgBouncer for a small application?

If your application rarely exceeds 20-30 concurrent connections, PostgreSQL handles it natively without issue. PgBouncer becomes valuable once you approach 100+ concurrent connections or run serverless/autoscaling application tiers that open and close connections rapidly.

What is the safest way to reset a forgotten PostgreSQL password?

Connect locally as the postgres system user through peer authentication (sudo -u postgres psql), which does not require a password, then run ALTER USER myuser WITH PASSWORD 'new-password'; to set a new one.

Conclusion

PostgreSQL is one of those tools that rewards the time you invest in understanding it. The default configuration runs without issues, but knowing what shared_buffers, work_mem, and pg_hba.conf actually do puts you in control of your database performance and security.

The fundamentals covered here — installation from official repositories, proper authentication configuration, regular backups with pg_dump, and memory tuning for your hardware — are what separate reliable production databases from ones that become problems at 2 AM.

Start with the defaults. Measure with pg_stat_statements. Tune what the data tells you to tune. Keep your backups tested. PostgreSQL rarely fails on its own — most incidents trace back to untested backups, exhausted connections without a pool in front, or misconfigured authentication.

The database will outlast the hype cycle. Invest in understanding it.