PostgreSQL instalace a konfigurace

PostgreSQL je nejpokročilejší open-source relační databáze.

Instalace¶

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;

Konfigurace¶

postgresql.conf¶

listen_addresses = ‘*’ max_connections = 200 shared_buffers = 4GB effective_cache_size = 12GB work_mem = 64MB random_page_cost = 1.1

Autentizace¶

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¶

Investujte do tuningu shared_buffers a work_mem.