PostgreSQL

Overview

PostgreSQL is a powerful, open-source relational database known for reliability, standards compliance, and extensibility. This reference covers user management, database operations, configuration, backup/restore, performance tuning, and essential SQL patterns.

Connection

PostgreSQL is accessed through the psql command-line client. You can connect with just a username or database, pass a full connection string, or set the default connection parameters via environment variables to shorten the command.

# Local connection
psql -U postgres
psql -U myuser -d mydb
psql -U myuser -h localhost -p 5432 -d mydb

# Connection string (embedded credentials, no prompts)
psql "postgresql://user:password@host:5432/mydb"

# Set connection defaults via environment variables
export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGDATABASE=mydb
psql       # picks up host/port/user/db from env vars

User management

Users and roles control who can log in and what they can do. The statements below create login users, define group roles, grant role membership, alter credentials and privileges, list users, and safely remove them.

-- Create a login role that can connect and authenticate
CREATE USER myuser WITH PASSWORD 'secure_password';
CREATE USER readonly WITH PASSWORD 'readonly_password' NOCREATEDB;

-- Create a role (no login by default — used for grouping privileges)
CREATE ROLE app_readers;
CREATE ROLE app_writers;

-- Grant role membership to bundle privileges from multiple roles
GRANT app_readers TO myuser;
GRANT app_writers TO myuser;

-- Alter a user
ALTER USER myuser WITH PASSWORD 'new_password';
ALTER USER myuser WITH SUPERUSER;
ALTER USER myuser WITH CREATEDB;
ALTER USER myuser VALID UNTIL '2026-12-31';

-- List users (psql meta-command and raw SQL query)
\du
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

-- Drop a user (reassign or drop owned objects first to avoid failures)
DROP USER myuser;
REASSIGN OWNED BY myuser TO postgres;  -- reassign objects before dropping
DROP OWNED BY myuser;                 -- drop all objects owned

Authentication (pg_hba.conf)

The pg_hba.conf file (host-based authentication) defines which authentication method is allowed for each host, database, and user combination. Rules are evaluated top to bottom and the first match wins, so the reject line must come last.

# /etc/postgresql/<version>/main/pg_hba.conf

# Type  Database  User  Address          Method
local   all       all                    peer
host    all       all   127.0.0.1/32    scram-sha-256
host    mydb      app       10.0.0.0/8   scram-sha-256
host    all       all   0.0.0.0/0       reject   # deny everything else

After editing, reload:

systemctl reload postgresql
# or
psql -c "SELECT pg_reload_conf();"

Database management

These statements create, list, rename, and drop databases, and report their on-disk sizes. Note that all connections to a database must be terminated before it can be dropped.

-- Create database
CREATE DATABASE mydb;
CREATE DATABASE mydb OWNER myuser;
CREATE DATABASE mydb ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

-- List databases
\l
SELECT datname FROM pg_database;

-- Rename database
ALTER DATABASE oldname RENAME TO newname;

-- Disconnect all active sessions, then drop the database
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
DROP DATABASE mydb;

-- Size information (raw bytes and human-readable format)
SELECT pg_database_size('mydb');
SELECT pg_size_pretty(pg_database_size('mydb'));

Schema and table operations

Schemas group related tables, and tables define the structure of your data. The examples below create a schema and table, add indexes for common lookup paths, alter the table definition, and list what already exists.

-- Create schema
CREATE SCHEMA app;

-- Create table
CREATE TABLE app.users (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    role        VARCHAR(20) DEFAULT 'user',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for fast lookups and uniqueness constraints
CREATE INDEX idx_users_email ON app.users(email);
CREATE INDEX idx_users_created ON app.users(created_at);
CREATE UNIQUE INDEX idx_users_email_lower ON app.users(LOWER(email));

-- Alter the table structure (add, modify, drop a column)
ALTER TABLE app.users ADD COLUMN active BOOLEAN DEFAULT true;
ALTER TABLE app.users ALTER COLUMN role SET NOT NULL;
ALTER TABLE app.users DROP COLUMN active;

-- List tables (psql meta-commands and raw SQL query)
\dt
\dt app.*
SELECT table_name FROM information_schema.tables WHERE table_schema = 'app';

Basic CRUD

Create

INSERT adds rows to a table. The examples cover inserting a single row, inserting multiple rows in one statement, and using RETURNING to fetch generated values (such as id and created_at) without a follow-up query.

INSERT INTO app.users (name, email, role)
VALUES ('Alice', 'alice@example.com', 'admin');

-- Insert multiple rows in a single statement
INSERT INTO app.users (name, email)
VALUES
    ('Bob', 'bob@example.com'),
    ('Carol', 'carol@example.com');

INSERT INTO app.users (name, email)
VALUES ('Dave', 'dave@example.com')
RETURNING id, created_at;

Read

SELECT retrieves data from tables. These queries demonstrate selecting all or specific columns, filtering with WHERE, ordering and limiting results, aggregating with GROUP BY, joining tables, and using window functions.

-- Select all columns
SELECT * FROM app.users;

-- Select specific columns with a combined filter
SELECT id, name, email FROM app.users
WHERE role = 'admin' AND active = true;

-- Order results and cap the number of rows returned
SELECT * FROM app.users ORDER BY created_at DESC LIMIT 10;

-- Aggregate: count users per role
SELECT role, COUNT(*) FROM app.users GROUP BY role;

-- Join users with their orders from the last 30 days
SELECT u.name, o.total, o.created_at
FROM app.users u
JOIN app.orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

-- Window function
SELECT name, email,
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
FROM app.users;

Update

UPDATE changes existing rows. The examples show setting one or more columns for rows that match a WHERE filter, and using RETURNING to inspect the rows that were modified.

UPDATE app.users
SET role = 'manager', updated_at = NOW()
WHERE email = 'bob@example.com';

-- Update with RETURNING
UPDATE app.users
SET active = false
WHERE id = 5
RETURNING id, name, active;

Delete

DELETE permanently removes rows (a hard delete). Because that data is gone for good, a soft delete — marking the row inactive with UPDATE instead — is often the preferred pattern when you need to keep history or be able to restore records.

DELETE FROM app.users WHERE id = 5;

-- Soft delete (preferred pattern)
UPDATE app.users SET active = false, updated_at = NOW() WHERE id = 5;

Transactions

Transactions bundle multiple statements into a single atomic unit: either every statement commits, or none of them take effect. Wrap related writes in BEGIN; ... COMMIT;, and substitute ROLLBACK; for COMMIT; to undo everything.

BEGIN;
    INSERT INTO app.orders (user_id, total) VALUES (1, 99.99);
    UPDATE app.users SET updated_at = NOW() WHERE id = 1;
COMMIT;
-- or ROLLBACK;

Configuration

Key configuration files

On Debian/Ubuntu, PostgreSQL keeps its configuration under /etc/postgresql/<version>/main/. These three files control the server's core behavior, authentication rules, and user name mapping.

/etc/postgresql/<version>/main/postgresql.conf    # main configuration
/etc/postgresql/<version>/main/pg_hba.conf        # authentication
/etc/postgresql/<version>/main/pg_ident.conf      # user name mapping

Essential postgresql.conf settings

postgresql.conf holds the server's runtime settings. These are the values most often tuned for memory, connections, the write-ahead log, query planning, and logging.

# Memory
shared_buffers = '256MB'           # 25% of system RAM
effective_cache_size = '1GB'        # 75% of system RAM
work_mem = '16MB'                  # per-operation sort memory
maintenance_work_mem = '64MB'      # for VACUUM, CREATE INDEX

# Connections
max_connections = 100

# WAL (Write-Ahead Log)
wal_level = replica                # 'minimal', 'replica', or 'logical'
max_wal_size = '1GB'
min_wal_size = '80MB'

# Query planning
random_page_cost = 1.1             # 1.1 if SSD, 4.0 if HDD
effective_io_concurrency = 200     # SSD: 200, HDD: 2

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000  # log queries taking >1s
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Apply changes:

systemctl reload postgresql
# or
psql -c "SELECT pg_reload_conf();"

# Check current settings
psql -c "SHOW shared_buffers;"
psql -c "SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%memory%';"

Backup and restore

pg_dump / pg_restore

pg_dump backs up a single database to a plain SQL file or a custom-format archive, and pg_restore restores custom-format dumps. Use -Fc for compressed backups that support parallel and selective restore.

# Plain SQL dump (readable, portable, restorable with psql)
pg_dump mydb > backup.sql
pg_dump -U myuser -h localhost mydb > backup.sql

# Custom format (compressed, supports parallel restore)
pg_dump -Fc mydb > backup.dump
pg_dump -Fc -j 4 mydb > backup.dump   # parallel dump

# Schema only
pg_dump --schema-only mydb > schema.sql

# Data only
pg_dump --data-only mydb > data.sql

# Single table
pg_dump -t app.users mydb > users.sql

# Restore
psql mydb < backup.sql               # plain SQL
pg_restore -d mydb backup.dump       # custom format
pg_restore -d mydb -j 4 backup.dump  # parallel restore
pg_restore -d mydb --clean backup.dump  # drop objects first
pg_restore -t app.users -d mydb backup.dump  # single table

pg_dumpall (entire cluster)

Use pg_dumpall when you need to back up the entire cluster — every database plus global objects such as roles and tablespaces, which pg_dump alone cannot capture. For a single database, stick with pg_dump; it produces a smaller backup that is easier to restore selectively.

# Dump all databases and roles
pg_dumpall > cluster_backup.sql

# Roles only
pg_dumpall --roles-only > roles.sql

# Restore
psql -f cluster_backup.sql postgres

Performance monitoring

PostgreSQL exposes live statistics through system views. These queries show currently running queries, cancel or terminate problematic sessions, and reveal table sizes, index usage, and tables that may be missing indexes.

-- Currently running queries, oldest first
SELECT pid, now() - pg_stat_activity.query_start AS duration,
    query, state
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Cancel a query
SELECT pg_cancel_backend(<pid>);
-- Terminate a connection
SELECT pg_terminate_backend(<pid>);

-- Table sizes including indexes and TOAST data
SELECT schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'app'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage
SELECT schemaname, tablename, indexname,
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Missing indexes (tables with sequential scans)
SELECT schemaname, tablename, seq_scan, seq_tup_read,
    idx_scan, seq_tup_read / seq_scan AS avg_seq_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

VACUUM and ANALYZE

VACUUM reclaims storage occupied by dead rows left behind by UPDATE and DELETE operations, and ANALYZE refreshes the statistics the query planner relies on. Autovacuum does this automatically, but a manual VACUUM is worthwhile after large bulk changes.

-- Manual vacuum (single table)
VACUUM app.users;
VACUUM FULL app.users;              -- reclaim disk space (locks table)
VACUUM ANALYZE app.users;           -- vacuum + update statistics

-- Auto-vacuum settings
SHOW autovacuum;
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables;

See also

• MongoDB Reference — document database operations
• Databases Overview — quick start and configuration locations