PostgreSQL na VPS — instalacja, konfiguracja i zabezpieczenie

PostgreSQL vs MySQL — kiedy wybrać PostgreSQL?

Powiązane tematy: MySQL na hostingu, MariaDB vs MySQL, backup strategia oraz Redis cache. Porównaj serwery VPS na stronie VPS.

Instalacja PostgreSQL (Ubuntu/Debian)

# Dodaj oficjalne repozytorium PostgreSQL (najnowsza wersja)
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Lub z domyślnego repo Ubuntu (może być starsza wersja)
sudo apt update
sudo apt install postgresql postgresql-contrib

# Sprawdź status
sudo systemctl status postgresql

# Sprawdź wersję
psql --version

Pierwsze kroki — użytkownik postgres

# Zaloguj się jako superuser postgres
sudo -u postgres psql

# Zmień hasło postgres
ALTER USER postgres WITH PASSWORD 'silne_haslo';

# Stwórz bazę danych i użytkownika aplikacji
CREATE DATABASE moja_app;
CREATE USER app_user WITH PASSWORD 'haslo_app';
GRANT ALL PRIVILEGES ON DATABASE moja_app TO app_user;

# Połącz się z bazą
\c moja_app

# Przyznaj uprawnienia do schematu (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO app_user;

# Wyjdź
\q

Konfiguracja pg_hba.conf — uwierzytelnianie

Plik pg_hba.conf kontroluje kto i jak może łączyć się z bazą. Lokalizacja: /etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE  USER     ADDRESS         METHOD
local   all       postgres                 peer      # root przez unix socket
local   all       all                      md5       # inni użytkownicy - hasłem
host    moja_app  app_user 127.0.0.1/32    scram-sha-256  # PHP z localhosta
host    all       all      0.0.0.0/0       scram-sha-256  # zdalne (ostrożnie!)

# Przeładuj konfigurację
sudo systemctl reload postgresql

Bezpieczeństwo: Nigdy nie otwieraj PostgreSQL na publiczne IP bez VPN lub SSH tunelu. Użyj 127.0.0.1/32 dla lokalnych połączeń aplikacji.

Konfiguracja postgresql.conf — optymalizacja

Kluczowe parametry w /etc/postgresql/16/main/postgresql.conf:

# Połączenia
max_connections = 100           # Dostosuj do liczby procesów aplikacji

# Pamięć (reguła: 25% RAM dla shared_buffers)
shared_buffers = 256MB          # 25% RAM (np. dla 1 GB RAM)
effective_cache_size = 768MB    # ~75% RAM (wskazówka dla planera)
work_mem = 4MB                  # RAM per operację sortowania (ostrożnie!)
maintenance_work_mem = 64MB     # VACUUM, CREATE INDEX

# WAL (write-ahead log)
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Logging
log_min_duration_statement = 1000  # Loguj zapytania wolniejsze niż 1s
log_checkpoints = on
log_connections = on

# Autovacuum — automatyczne czyszczenie
autovacuum = on
autovacuum_vacuum_scale_factor = 0.05

# Zastosuj zmiany
sudo systemctl restart postgresql

Pomocne narzędzie: PGTune — kalkulator konfiguracji PostgreSQL na podstawie RAM i liczby połączeń.

Backup i przywracanie — pg_dump

# Backup jednej bazy (SQL format)
pg_dump -U postgres -h localhost moja_app > /backup/moja_app_$(date +%Y%m%d).sql

# Backup z kompresją (szybszy, mniejszy plik)
pg_dump -U postgres -Fc moja_app > /backup/moja_app_$(date +%Y%m%d).dump

# Backup wszystkich baz
pg_dumpall -U postgres > /backup/all_databases_$(date +%Y%m%d).sql

# Przywracanie z SQL
psql -U postgres moja_app < /backup/moja_app_20240115.sql

# Przywracanie z custom format (-Fc)
pg_restore -U postgres -d moja_app /backup/moja_app_20240115.dump

#!/bin/bash
# /usr/local/bin/pg-backup.sh — cron backup
BACKUP_DIR="/backup/postgresql"
DAYS_TO_KEEP=30
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# Backup każdej bazy osobno
for DB in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate=false"); do
    pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
done

# Usuń stare backupy
find "$BACKUP_DIR" -name "*.dump" -mtime +$DAYS_TO_KEEP -delete

echo "$(date): Backup PostgreSQL zakończony" >> /var/log/pg-backup.log

Monitorowanie i diagnostyka

-- Aktywne połączenia
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Najdłużej działające zapytania
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 10;

-- Rozmiary baz danych
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Cache hit ratio (powinno być >99%)
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

-- Indeksy nieużywane (kandydaci do usunięcia)
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

Przydatne komendy psql