PostgreSQL

Administer and optimize PostgreSQL databases.

Installation & Setup

# Install
apt install postgresql postgresql-contrib

# Access
sudo -u postgres psql

# Create database and user
CREATE USER myapp WITH PASSWORD 'secret';
CREATE DATABASE mydb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

Configuration

# /etc/postgresql/15/main/postgresql.conf
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 4MB
maintenance_work_mem = 64MB

Backup & Restore

# Backup
pg_dump mydb > backup.sql
pg_dump -Fc mydb > backup.dump  # Custom format

# Restore
psql mydb < backup.sql
pg_restore -d mydb backup.dump

Replication

# Primary
ALTER SYSTEM SET wal_level = replica;
CREATE USER replicator REPLICATION LOGIN PASSWORD 'secret';

# Replica
pg_basebackup -h primary -U replicator -D /var/lib/postgresql/15/main -P

Best Practices

• Regular VACUUM and ANALYZE
• Monitor slow queries
• Implement connection pooling (PgBouncer)
• Regular backups with pg_dump or pg_basebackup