PostgreSQL Installation und Konfiguration

PostgreSQL ist die fortschrittlichste relationale Open-Source-Datenbank.

Installation¶

sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql
CREATE USER myapp WITH PASSWORD 'secret';
CREATE DATABASE mydb OWNER myapp;

Konfiguration¶

# postgresql.conf
listen_addresses = '*'
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
random_page_cost = 1.1

Authentifizierung¶

# pg_hba.conf
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all 10.0.0.0/8 scram-sha-256

Monitoring¶

SELECT pid, now()-query_start AS duration, query FROM pg_stat_activity WHERE state!='idle' ORDER BY duration DESC;
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database;

PostgreSQL = Standard¶

Investieren Sie in das Tuning von shared_buffers und work_mem.