PostgreSQL is the most advanced open-source relational database.
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;
Configuration¶
# postgresql.conf
listen_addresses = '*'
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
random_page_cost = 1.1
Authentication¶
# 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¶
Invest in tuning shared_buffers and work_mem.
postgresqlsqlconfiguration