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