# Ubuntu/Debian
apt install postgresql postgresql-contrib
# RHEL/CentOS
dnf install postgresql-server postgresql-contrib
postgresql-setup --initdb
# Connexions
listen_addresses = '*' # Écoute sur toutes les interfaces
max_connections = 100 # Nombre maximum de connexions
superuser_reserved_connections = 3
# Mémoire
shared_buffers = 2GB # 25% de la RAM pour serveurs dédiés
work_mem = 16MB # Pour opérations de tri
maintenance_work_mem = 256MB # Pour maintenance
effective_cache_size = 6GB # 50-75% de la RAM
# Write Ahead Log
wal_level = replica # Pour réplication
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_timeout = 15min
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host prod_db app_user 192.168.1.0/24 md5
-- Création base
CREATE DATABASE dbname
WITH OWNER = username
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- Liste des bases
\l
SELECT datname FROM pg_database;
-- Taille des bases
SELECT pg_size_pretty(pg_database_size('dbname'));
-- Création utilisateur
CREATE USER username WITH PASSWORD 'password';
-- Attribution droits
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
GRANT SELECT, INSERT ON table_name TO username;
-- Révocation droits
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
-- Liste utilisateurs
\du
SELECT * FROM pg_user;
-- Information schéma
\d table_name
SELECT * FROM information_schema.columns
WHERE table_name = 'table_name';
-- Taille tables
SELECT
schemaname,
relname,
pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
-- Vérification bloat
SELECT schemaname, tablename,
ROUND(CASE WHEN otta=0 THEN 0.0
ELSE sml.relpages/otta::numeric
END,1) AS tablebloat
FROM (
SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma
ELSE datahdr%ma END))+nullhdr2+4)/bs)) AS otta
FROM (
SELECT ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0
then ma
else hdr%ma end)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0
then ma
else nullhdr%ma end)))::numeric AS nullhdr2
FROM (
SELECT schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname
AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT (
SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN
('8.0','8.1','8.2') THEN 27
ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32'
THEN 8
ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid
AND nn.nspname = rs.schemaname
) AS sml;
¶ 🔄 Maintenance
-- Vacuum analyse
VACUUM ANALYZE table_name;
-- Vacuum full
VACUUM FULL table_name;
-- Vacuum automatique
ALTER TABLE table_name SET (
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_scale_factor = 0.2,
autovacuum_analyze_threshold = 50,
autovacuum_analyze_scale_factor = 0.1
);
# Backup complet
pg_dump dbname > backup.sql
# Backup compressé
pg_dump -Fc dbname > backup.dump
# Backup spécifique
pg_dump -t table_name dbname > table_backup.sql
# Restore
psql dbname < backup.sql
# Restore compressé
pg_restore -d dbname backup.dump
-- Réindexer table
REINDEX TABLE table_name;
-- Réindexer base
REINDEX DATABASE dbname;
-- Réindexer concurrent
CREATE INDEX CONCURRENTLY idx_name
ON table_name (column_name);
-- Configuration log
ALTER SYSTEM SET log_min_duration_statement = '1000'; -- ms
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_directory = 'pg_log';
-- Analyse requêtes
SELECT
substring(query, 1, 50) as query,
calls,
round(total_time::numeric, 2) as total_time,
round(mean_time::numeric, 2) as mean,
round((100 * total_time / sum(total_time::numeric)
over ())::numeric, 2) as percentage
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- Index B-tree standard
CREATE INDEX idx_name ON table_name (column_name);
-- Index multi-colonnes
CREATE INDEX idx_name ON table_name (col1, col2);
-- Index partiel
CREATE INDEX idx_name ON table_name (column_name)
WHERE condition;
-- Index unique
CREATE UNIQUE INDEX idx_name ON table_name (column_name);
-- Analyse utilisation index
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Buffer cache hit ratio
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) +
sum(heap_blks_read))::float as ratio
FROM pg_statio_user_tables;
-- Configuration shared_buffers
ALTER SYSTEM SET shared_buffers = '2GB';
-- Configuration work_mem
ALTER SYSTEM SET work_mem = '16MB';
#!/bin/bash
# monitoring.sh
# Connexions actives
psql -c "
SELECT datname, usename, client_addr,
state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';"
# Taille bases
psql -c "
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;"
# Statistiques tables
psql -c "
SELECT schemaname, relname,
n_live_tup, n_dead_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;"
-- Fonction alerte espace disque
CREATE OR REPLACE FUNCTION check_disk_space()
RETURNS void AS $$
DECLARE
db_size bigint;
threshold bigint := 5368709120; -- 5GB in bytes
BEGIN
SELECT pg_database_size(current_database())
INTO db_size;
IF db_size > threshold THEN
RAISE NOTICE 'Database size (%) exceeds threshold (%)',
pg_size_pretty(db_size), pg_size_pretty(threshold);
END IF;
END;
$$ LANGUAGE plpgsql;
- Utiliser des connexions SSL
- Limiter les accès dans pg_hba.conf
- Audit régulier des privilèges
- Rotation des logs
- Configuration adaptée à la RAM
- Vacuum régulier
- Indexation appropriée
- Monitoring actif
¶ Maintenance
- Backups réguliers
- Test des restaurations
- Mise à jour des statistiques
- Surveillance des logs