postgresql-devsec

# Debian / Ubuntu
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# RHEL / Amazon Linux
sudo dnf install -y postgresql15-server postgresql15-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# Verify
psql --version
sudo systemctl status postgresql

# Switch to the postgres system user
sudo -u postgres psql

-- Create an application user
CREATE USER myapp WITH PASSWORD 'strong_password_here';

-- Create the database owned by that user
CREATE DATABASE mydb OWNER myapp;

-- Grant connection privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

-- Connect to the database and set default privileges
\c mydb
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp;

\l              -- list databases
\dt             -- list tables in current database
\d+ tablename   -- describe table with storage info
\du             -- list roles
\x              -- toggle expanded output
\timing on      -- show query execution time
\i file.sql     -- execute SQL from file
\copy           -- fast client-side COPY

# Connection settings
listen_addresses = '*'
max_connections = 200

# Memory — adjust to ~25% of total RAM for shared_buffers
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL / write performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
min_wal_size = 1GB
max_wal_size = 4GB

# Planner
random_page_cost = 1.1          # lower for SSD
effective_io_concurrency = 200  # for SSD

# Logging
log_min_duration_statement = 250   # log queries slower than 250 ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Reload configuration without restart
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Some settings (shared_buffers, max_connections) require a full restart
sudo systemctl restart postgresql

# /etc/postgresql/15/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       postgres                  peer
host    mydb      myapp     10.0.0.0/8      scram-sha-256
host    all       all       0.0.0.0/0       reject

sudo systemctl reload postgresql

# Plain SQL backup
pg_dump -U myapp -h localhost mydb > /backups/mydb_$(date +%F).sql

# Custom compressed format (recommended)
pg_dump -U myapp -h localhost -Fc mydb > /backups/mydb_$(date +%F).dump

# Backup a single table
pg_dump -U myapp -h localhost -t orders -Fc mydb > /backups/orders.dump

# Restore from custom format
pg_restore -U myapp -h localhost -d mydb --clean --if-exists /backups/mydb_2025-01-15.dump

# Restore plain SQL
psql -U myapp -h localhost -d mydb < /backups/mydb_2025-01-15.sql

# Full base backup (used for PITR and replica seeding)
pg_basebackup -h localhost -U replicator -D /backups/base_$(date +%F) \
  --wal-method=stream --checkpoint=fast --progress --verbose

# Verify the backup
pg_verifybackup /backups/base_2025-01-15