Bases de données

Introduction

Cette introduction est tirée de  : https://openclassrooms.com/fr/courses/1959476-administrez-vos-bases-de-donnees-avec-mysql/1959710-introduction

Concepts de base

Une base de données informatique est un ensemble de données qui ont été stockées sur un support informatique, organisées et structurées de manière à pouvoir facilement consulter et modifier leur contenu.

Ainsi, la base de données d'un site web pourrait contenir la liste des membres, les articles, etc... Cette abse de données doit non suelement exister, mais on doit pouvoir interagir avec et la gérer : on doit pouvoir utiliser des requêtes MySQL pour pouvoir modifier cette base. Nous avons donc besoin de :

• Une base de données
• un système pour la gérer
• Un langage pour transmettre des instructions

SGBD

Un SGBD est un logiciel permettant de manipuler les données d'une base de données, c'est-à-dire les sélectionner, les afficher, les modifier, en ajouter ou en supprimer (CRUD : Create, Read, Update, Delete). MySQL est un SGBD. Les SGBD suivent en général le modèle client-serveur.

SGBDR

Le R en plus signifie "Relationnel". C'est un SGBD qui implémente la théorie relationnelle. La théorie relationnelle c'est compliqué, mais cela signifie en gros que les données sont contenues dans des relations, représentées sous forme de tables. Une relation est composée d'une en-tête et d'un corps. L'en-tête contient plusieurs attributs (pour un client on aura par exemple nom, prénom, adresse...). Le corps est quant à lui un ensemble de lignes (ou n-uplets) composé d'autant d'éléments qu'il y'a de d'attributs dans le corps. Exemple avec 4 lignes pour la relation client :

Numéro	Prénom	Nom	Email
1	Jean	Dupont	jdupont@email.com
2	Marie	Malherbe	mama@email.com
3	Nicolas	Jacques	Jacques.nicolas@email.com
4	Hadrien	Piroux	happi@email.com

Les opérations :

Différentes opérations peuvent être appliquées à ces relations pour en tirer des des informations (issues de la logique formelle et de la théorie des ensembles, apparement):

 

Exemple de la jointure :

 

Ici, on relie les deux tables par le numéro de client.

Quelques SGBDR

• MySQL : L'un des plus utilisés, il utilise SQL. Il est open source. Il a été créé en 1994 par Widenius et Axmark, mais a depuis été racheté par Oracle, et a forké pour devenir MariaDB dans sa version Open source. Il est très utilisé mais a de nombreux défauts, car il ne suit pas toujours les normes officielles, et peux parfois manquer de certaines fonctions avancées.
• PostGreSQL : Il est open source, lui aussi. Moins connu, il semble cependant actuellement équivalent à MySQL en termes de ressources. Le langage procédural qu'il utilise s'appelle le PL/pgSQL.
• Access : propriété de Microsoft, il n'est pas du tout adapté aux gros volumes de données. Il a cependant une interface graphique ! :D
• SQLite : Pas en client-serveur, il stocke les données dans de simples fichiers. Il est adapté quand on a pas la possibilité d'installer un serveur de base de données. Il est très performant pour les tout petits volumes de données, mais difficile à sécuriser.

Organisation d'une base de données relationnelle

Comme nous dans la vie courante, une base de données classe les informations d'une façon logique. Les données sont représentées sous forme de tables : une base de données contiendra plusieurs tables. Dans chaque table se trouveront un certain nombre de colonnes. Ainsi, les données seront introduites sous formes de lignes dans la table. C'est tout simplement : UN TABLEAU ! Comme ça :

Numéro	Prénom	Nom	Email
1	Jean	Dupont	jdupont@email.com
2	Marie	Malherbe	mama@email.com
3	Nicolas	Jacques	Jacques.nicolas@email.com
4	Hadrien	Piroux	happi@email.com

 

Premier TP : MySQL/MariaDB

Le fichier du TP:

<pdf>Fichier:Tpbd1.pdf</pdf>

Le but final de ce travail sera de mettre en place une plateforme Debian, avec un serveur LAMP. On y ajoutera MySQL, un CMS, avant de migrer vers MariaDB et même de migrer vers un cluster MariaDB. Des sauvegardes et restaurations automatiques et incrémentales seront mises en place.

À chacun des étapes, il est important de faire un snapshot.

Installation de la machine de base et configuration du réseau

Le système de base sera une Debian 9.5, dotée d'un serveur SSH et des composants habituels du système. Je pars d'une machine de base que je clone dans Virtualbox pour ne pas avoir à recommencer l'installation à chaque fois (je les range dans le groupe "machines fraîches"): il faut bien penser à changer les adresses MAC ainsi que les noms d'hôtes. Les machines seront reliées entre elles en mode "réseau NAT". Au passage, je clone aussi une Debian avec interface graphique que j'avais de côté et qui me servira de navigateur web, et qui sera aussi dans le réseau NAT.

Réseau NAT:

Le réseau NAT crée un réseau similaire à un réseau domestique, Virtualbox agissant comme une box (routeur + DHCP). Il faut pour cela:

• Aller dans les paramètres virtualbox > réseau > créer un réseau NAT, et choisir les paramètres.
• On peut créer des redirections de ports à cet endroit : ce paramètre agit comme n'importe quel PAT sur un routeur (le principe est le même que sur PfSense).
• Se rendre dans les paramètres de la / des VMs et choisir l'option "Réseau NAT" avec ce réseau.

Résolution de problème:

Ça ne fonctionne pas sur la version "dépôt" de virtualbox sur Ubuntu 18.10. Les étapes que j'ai suivies:

• Télécharger Virtualbox depuis le site officiel dans sa dernière version.

Après un plantage à l'installation, apt me bloquait avec le message "Virtualbox-5.2 doit être réinstallé, mais impossible de trouver son archive":

• sudo dpkg --configure -a
• relancer l'installation avec dpkg -i (et non pas avec gdebi !) sur le .deb précédemment téléchargé. 

Redirection de port :

La règle que j'ai créée est la suivante :

J'ai ensuite ouvert le port via UFW.

La redirection fonctionne ensuite en lancant la commande suivante depuis ma machine hôte :

ssh justine@127.0.0.1 -p 2222

Installation de la plateforme LAMP

À ce stade, je commence par installer sudo et vim, ce qui sera plus simple pour la suite :

su
apt update
apt install sudo
apt install vim
###Je rajoute mon utilisateur justine en éditant le fichier /etc/sudoers
visudo
exit

On installe ensuite mysql:

sudo apt install mysql-server mysql-client
###J'ai déjà update juste avant, pas besoin de recommencer
###MariaDB ne demande pas de mot de passe pour le root mysql (il n'en a pas!)
###Comme je suis sous Debian, cette commande installe MariaDB

Avec MariaDB, le root n'as pas de mot de passe. Je peux voir ça avec :

sudo mysql -u root -p
#Rentrer n'importe quel mdp
use mysql;
select User,Password,Plugin from user where User='root';
#Je vois bien le champ "Password" vide !!!! et le Plugin "unix_socket"

En effet,  avec MariaDB, le root mysql utilise le root Linux !

On modifie le fichier de configuration du serveur dans le cadre du TP et on redémarre:

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
###Utiliser la recherche pour remplacer bind-address par 0.0.0.0
sudo service mysql restart

 Installation du serveur web et des composants PHP:

sudo apt install apache2
###Vérifier qu'il fonctionne avec la machine "navigateur"
sudo apt install php libapache2-mod-php
sudo /etc/init.d/apache2 restart
###Le serveur est redémarré !

Test de la configuration PHP: on va créer un fichier servant à tester que PHP fonctionne.

sudo vim /var/www/html/test.php
###Contrairement à ce que dit le TP, il faut mettre notre fichier php dans le dossier html, qui est la racine du serveur de base
###Je peux ensuite vérifier en allant sur http://10.0.2.15/test.php qu'il s'affiche bien
###contenu du fichier de test php:
<?php
phpinfo();
 ?>

 On installe le support mysql de php:

sudo apt install php-mysql
###Redémarrage d'Apache
sudo /etc/init.d/apache2 restart
###Retourner voir la page de test php en la rafraîchissant que les modules mysql se sont ajoutés (dans les .ini)

NOTE : une petite liste des paquets php qui pourraient s'avérér utiles par la suite: php-curl php-pear php-gd php-intl php-imagick php-imap php-mcrypt php-memcache php-pspell php-recode php-snmp php-sqlite php-tidy php-xmlrpc php-xs

Installation de phpmyadmin

sudo apt install phpmyadmin
###Choisir le serveur apache
###Choisir la base de données db-common et entrer un mot de passe
###Une fois installé, je peux me connecter via http://10.0.2.15/phpmyadmin
###Identifiants : phpmyadmin et mot de passe précédemment entré

Les informations sur les utilisateurs et les privilèges sont stockés dans la base de données "mysql".  Ses 4 tables importantes pour les privilèges:

• db : privilèges sur les bdd
• tables_priv : privilèges sur les tables
• columns_priv : privilèges sur les colonnes
• procs_priv : privilèges sur les routines

Installation de Wordpress

Nous allons ici procéder à une installation de type LAMP normale, en production on pourrait utiliser le paquet Debian.

Téléchargement de Wordpress:

wget http://wordpress.org/latest.zip

Création de la base de données pour wordpress et de l'utilisateur:

sudo mysql -u root -p #Comme expliqué plus haut, pas de mot de passe
CREATE DATABASE wordpress;
create user 'wordpressuser';
set password for 'wordpressuser' = PASSWORD('Prevert77');
grant all privileges on wordpress.* to wordpressuser@localhost identified by 'Prevert77';
###On a un utilisateur pour wordpress qui a tous les droits sur sa base
exit

Installation de wordpress:

sudo apt install zip
sudo unzip latest.zip -d /var/www/html/
###Les droits : le dossier wordpress doit appartenir à l'utilisateur et au groupe d'apache (www-data), et avoir des droits en lecture, écriture, exécution
sudo chown -Rf www-data:www-data /var/www/html/wordpress/
sudo chown 775 -R /var/www/html/wordpress/

 À l'aide de ma machine navigateur, je vais procéder à l'installation sur http://10.0.2.15/wordpress.

Il a fallu que je crée moi-même le fichier wp-config.php, que je copie le texte suggéré dedans, que je le donne à www-data et que je lui donne les droits 775.

Une fois fait, remplir les quelques boîtes (nom, mot de passe...) et le site s'installe. Il est assez lent sur une VM à 512mo de RAM, pour l'instant en tout cas.

Actions sur la base de données

Gestion de droits

Il y'a deux commande mysql pour gérer les droits:

• GRANT nom_action ON nom_table TO nom_utilisateur : Permet d'attribuer des droits/privilèges
• REVOKE nom_action ON nom_table TO nom_utilisateur : Permet de supprimer des droits
• DROP : pour supprimer un utilisateur

Réparer une table

En cas de message "Table is marked as crashed and should be repaired" :

repair table nombase.nomtable

Tests pour le TP

Je vais commencer par créer un utilisateur "invite" , puis lui donner différents droits sur la table wordpress:

create user 'invite';
create database invite;
use wordpress;
###Tests d'attribution de droits
grant insert on * to invite;
grant insert on wp_posts to invite;
grant all on * to invite;
revoke insert on * from invite;
revoke insert on wp_posts from invite;
revoke all privileges on * from invite;
###Les commandes sont assez explicites

 Sécurisation de MySQL

On commence par lancer la commande sudo mysql_secure_installation. Ensuite, dans les différents dialogues qui s'ouvrent :

• Choisir un mot de passe root
• Supprimer l'utilisateur anonyme (authentification obligatoire)
• empêcher les connexion root depuis le réseau (seulement depuis le local)
• Supprimer la base de test : elle est par défaut accessible à tout le monde, et en plus par défaut tout le monde a tous les privilèges sur les tables commencant par test_
• Faire un flush des privilèges

Récupération d'un mot de passe root perdu

La méthode fournie dans le TP ne fonctionne pas chez moi (problèmes de sockets de connexion), c'est apparement la méthode "à l'ancienne, je la note quand même:

###Je stoppe le service :
sudo systemctl stop mysql
###Démarrage en mode de récupération
 mysqld_safe --skip-grant-tables &
###Connexion:
mysql -u root
###Mise à jour du mdp
use mysql;
update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
flush privileges;
quit
#Redémarrage
sudo systemctl restart mysql

La méthode qui fonctionne chez moi:

###Je stoppe le service
sudo systemctl stop mysql
###On change une variable d'environnement pour être en mode récupération
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
###Redémarrage
sudo systemctl start mysql
###Connexion et modification du mot de passe
sudo mysql -u root
use mysql;
update user set password=PASSWORD("Prevert77") where user='root';
flush privileges;
exit;
###Unset de la variable :
sudo systemctl unset-environment MYSQL_OPTS="--skip-grant-tables"
sudo systemctl restart mysql
###La connexion fonctionne

Supervision et amélioration

MySQLTuner:

Script PERL qui permet d'optimiser les perfs et de faire un diagnostic :

wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

Il donne quelques informations, notamment sur la RAM; cependant il n'as pas l'air très bien optimisé pour MariaDB.

Mytop

Similaire à top, mais pour les bases de données : il est intégré par défaut à MariaDB et est accesible via la commande mytop:

###Plusieurs façons de le lancer
mytop
mytop --prompt -d wordpress ##Pour la base de données wordpress

Utilisation des fichiers modèles, adaptés à différentes machines

Inexistants chez moi... On est censés avoir différentes configurations à recopier vers my.cnf dans /usr/share/doc/mysql-server-X/.

Fine-tuning

Modifier le fichier /etc/mysql/mariadb.conf.d/50-server.cnf:

• thread_concurrency : Peut être activé suivi du nombre de threads de la machine
• slow_query_log : En précisant le fichier (juste à décommenter une ligne normalement), loggue les requêtes lentes. On rajoute slow_query_time = 2 pour indiquer le temps le plus lent.
•  

Logs

Par défaut, MariaDB loggue dans journalctl. Il a aussi son dossier dans /var/log/mysql.
On peut configurer pas mal d'options de log dans le fichier de serveur, cependant il faut bien lire les indications !
Quand il y'a beaucoup de transactions, il est necéssaire de changer les logs de disque : cela fait trop pour un seul en écrivant les logs ET les données : le grand nombre d'entrées/sorties pénalise la réactivité du cluster.

Pour rappel, le fichier de serveur est dans /etc/mysql/mariadb.conf.d

Il faut aussi purger régulièrement les logs.

 

Sauvegardes complètes ou partielles

NB : On sauvegarde souvent une (ou plusieurs) base en omettant de sauvegarder
également les utilisateurs et leurs privilèges. Ces informations sont dans la table users de
la base mysql.

À chaud

On peut réaliser un backup sans arrêter le serveur de données. On va pour cela passer par mysqldump, un outil fourni avec mysqld.

MYSQL_PWD=Prevert77
###Ne pas noter tel quel dans un script, mais utiliser une variable d'environnement
sudo mysqldump --user=root --all-databases | gzip > save.mysql.sql.gz
###Il faut utiliser sudo pour que ça fonctionne !

Le fichier généré contient toutes les commandes servant à recréer les bases de données.

À froid

Il est rarement possible de stopper une base de données en production. Cependant, c'est possible si l'on a de la réplication. Cela consiste simplement à archiver les fichiers:

• var/lib/mysql/
• /var/log/mysql/

Pour les tables MyISAM, il s'agit des fichiers .FRM, .MYI et .MYD; pour InnoDB .FRM, ibdata (données), tables .ibd, et logs iblogfile.

Restauration

À chaud

Sur un serveur qui tourne, on va simplement se connecter et réutiliser le dump réalisé à chaud un peu plus tôt.

gzip -d save.mysql.sql.gz
mysql -u user -p
mysql> use database votrebase;
mysql > source /home/user/mysql/db.sq

On peut aussi le faire directement :

mysql -u myuser -p < /home/user/mysql/db.sql
###Dans un script, je pourrais utiliser l'option --password

À froid

Il faut juste redéposer les fichiers là ou on les a pris.
BackupPC peut fonctionner aussi.

Archivage et journalisation automatique

Un script "à tout faire" existe, packagé avec Debian : sudo apt install automysqlbackup. Il permet :

• Notification par mail
• Compression et chiffrement des sauvegardes
• Rotation des sauvegardes configurable
• Sauvegardes incrémentales

La configuration se fait dans /etc/default/automysqlbackup

On peut utiliser aussi rsnapshot...

Migration de MySQL vers MariaDB

Attention!!!
Sur un serveur en production, avant de procéder à la migration, faire une sauvegarde des bases de
données!

Marche à suivre :

apt-get update && apt-get upgrade
apt-get install python-software-properties software-properties-common
###On peut ensuite installer MariaDB. Galera est intégré. On peut passer à la 10.1 directement depuis un MySQL>5.5, sinon il faut d'abord passer par un MariaDB 5.5
sudo apt install mariadb
###Procéder à l'installation. MySQL sera supprimé entretemps !

Cluster de serveurs MariaDB/Galera

En production, on utiliserait au moins trois serveurs, pour gérer les conflits.

On commence depuis une machine qui utilise directement MariaDB.

Sur le premier noeud du cluster :

service mysql stop
###Sur systèmes sans systemd
mysqld --wsrep-new-cluster
#Avec systemd
sudo galera_new_cluster

Pour mon deuxième noeud, je pars d'une machine neuve sur laquelle j'installe le serveur et le client mariadb:

su
apt install mariadb-server-10.1 mariadb-client
###Je rejoins le cluster
sudo systemctl stop mysql
sudo mysqld --wsrep_cluster_address=gcomm://10.0.2.15

Le statut est ensuite visible avec une commande SQL :

SHOW STATUS LIKE 'wsrep_%';

Une page web permet de générer des configurations affinnées :

http://www.severalnines.com/New-Galera-Configurator/index.html

 

 

TP2 : PostGreSQL

<pdf>Fichier:TPPOSTGRE.pdf</pdf>

Le but du TP : Mettre en place un serveur LAPP (Comme LAMP mais avec PostGreSQL). Il contiendra un CMS, et la base de données sera administrée à base d'outil en ligne de commande/graphiques.

La machine de base sera du même type que celle utilisée précédemment ! Nous réutiliserons aussi la machine navigateur.

IP Maitre : 10.0.2.6
IP Esclave : 10.0.2.7

 

Installation de la plateforme LAMP

#Installation de sudo et vim
su
apt update
apt install sudo vim
###Ajout de l'utilisateur à sudo, etc...

#Installation et paramétrage de PostGreSQL
sudo apt install postgresql postgresql-contrib
#Changement mdp utilisateur système postgre
sudo passwd postgres
#Changement mdp admin bdd postgresql
su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'Prevert77'"
#Création d'une base
createdb mytestdb
#Connexion à la base
psql mytestdb
\q #Déconnexion
#Création d'un nouvel utilisateur
psql -d template1 -c "create user test with password 'Prevert77'"
#Activation de l'accès à distance
psql postgres
CREATE EXTENSION adminpack;
#Quitter avec ctrl + d deux fois

On va ensuite activer les connexion TCP/IP:

sudo vim /etc/postgresql/9.6/main/postgresql.conf
#Puis décommenter les lignes:
listen_addresses = 'localhost'
password_encryption = on
#Puis redémarrer le service
sudo systemctl restart postgresql
 

 Ce n'est pas très important, mais on aura besoin d'un serveur web, que l'oin testera de la même façon que dans le TP précédent.

sudo apt install apache2 php libapache2-mod-php
###Procéder au test php
sudo vim /var/www/html/test.php
###Contrairement à ce que dit le TP, il faut mettre notre fichier php dans le dossier html, qui est la racine du serveur de base
###Je peux ensuite vérifier en allant sur http://10.0.2.6/test.php qu'il s'affiche bien
###contenu du fichier de test php:
<?php
phpinfo();
 ?>

On a un serveur qui fonctionne, on va installer l'interface d'administration php:

sudo apt install phppgadmin
###Se rendre dans le fichier /etc/apache2/conf-available/phppgadmin.conf et commenter:
Require local
###(Une erreur dans le TP!)
###Activer le site (depuis sites-enabled):
sudo ln -s ../conf-available/phppgadmin.conf phppgadmin.conf
sudo systemctl restart apache2
###Il faut ensuite se rendre dans /usr/share/phppgadmin/conf/config.inc.php et changer le paramètre suivant:
$conf['extra_login_security'] = true;
###En le passant à "false"
###Ensuite on re-relance:
sudo systemctl restart postgresql
sudo systemctl restart apache2

Installation de tt-rss

On va enfin installer tt-rss, parce que pourquoi pas?

sudo apt install git-core
sudo apt install php-mbstring php-dom php-curl
sudo systemctl restart apache2
cd /var/www/html/
sudo git clone https://tt-rss.org/git/tt-rss.git
###Se rendre avec le navigateur sur http://10.0.2.6/tt-rss

Remplir les boîtes comme ça :

Database type : PostgreSQL
USername : test
Password : Prevert77
Database name : mytestdb
Host name : localhost
Port : 5432

Et cliquer sur "Test configuration". Une fois que c'est bon, cliquer sur "Initialize database", et suivre les instructions (on doit nous suggérer de copier un texte donné dans un fichier "config.php", comme avec un wordpress).

Une fois que c'est fait, se rendre à nouveau sur http://10.0.2.6/tt-rss. Si des messages d'erreurs apparaissent concernant les droits, exécuter les commandes chmod données par tt-rss (les dossiers cache, feed-icons et lock du dossier tt-rss doivent avoir les droits 777). Le login par défaut est admin - password: il n'y a plus qu'à se logguer et changer le mot de passe. tt-rss est installé.

Réplication "Streaming" PostgreSQL

La réplication va nécessiter la création d'un deuxième serveur postgreSQL "vierge". Je crée donc une nouvelle machine et y installe un serveur PostgreSQL. Le format du cluster sera un format "Maitre - Esclave".

Sur le maitre, je commence par modifier le fichier /etc/postgresql/9.6/main/postgresql.conf, les lignes suivantes sont à décommenter et à modifier :

###Sur le maitre
listen_adresses = '*'
wal_level = hot_standby
wal_keep_segments = 10 #À augmenter si la table de réplication est vide
max_wal_senders = 3

On crée un utilisateur spécifique à la réplication:

###Sur le maitre
psql -h localhost -U postgres -W -c "CREATE USER utilrepl WITH REPLICATION PASSWORD 'motdepasse';"
###On l'autorise à se connecter en allant modifier le fichier /etc/postgresql/9.6/main/pg_hba.conf
###Rajouter la ligne :
host replication utilrepl 10.0.2.7/24 md5
###On stoppe le service
sudo systemctl stop postgresql

On va ensuite passer sur l'esclave:

###Sur l'esclave
###Modifier le fichier postgresql.conf
listen_addresses = '*'
hot_standby = on
###On arrête l'esclave et on détruit tous les fichiers de sa base
cd /var/lib/postgresql/9.6/main/
rm -rf *
###On crée son fichier /var/lib/postgresql/9.6/main/recovery.conf, qui contient:
primary_conninfo = 'host=10.0.2.6 port=5432 user=utilrepl password=Prevert77'
standby_mode = on

On va ensuite tout copier du Maitre vers l'esclave avec rsync:

#Sur l'esclave
###il faut commencer par autoriser les connexion ssh en root sur l'esclave, il faudra l'enlever après par sécurité
#Dans le fichier /etc/ssh/sshd_config, ajouter la ligne:
PermitRootLogin yes
#Redémarrer le service
sudo systemctl restart sshd

#Sur le maitre
rsync -av /var/lib/postgresql/9.6/main/ 10.0.2.7:/var/lib/postgresql/9.6/main/

Il n'y plus qu'à lancer les deux serveurs:

#Sur les deux :
sudo systemctl restart postgresql

Pour vérifier l'état de la réplication:

psql -h localhost -U postgres -W -c "select * from pg_stat_replication;"

Récupération de mot de passe

On commence par arrêter le service:

sudo systemctl stop postgresql

 On va ensuite se rendre dans le fichier /etc/postgresql/9.6/main/pg_hba.conf:

• Aller à la ligne "local all all ..."
• La modifier en "local all postgres trust"

Ensuite :

sudo systemctl restart postgresql
sudo su - postgres
psql -d template1 -U postgres
alter user postgres with password 'votrenouveaumotdepasse';

Maintenant que c'est fait, il faut à nouveau modifier le fichier pg_hba.conf pour le remettre comme il était et relancer le service.

Fine-tuning des bases en production

Issu de : https://www.dsfc.net/logiciel-libre/postgresql/tuning-postgresql-configuration-memoire-linux/

Mémoire disponible

La mémoire disponible sous Linux peut être déterminée à l'aide de la commande free. À noter : PostgreSQL n'utilise plus la mémoire partagée depuis la version 9.4. Pas besoin de modifier le fichier /etc/sysctl.conf !

effective_cache_size

C'est une directive de postgresql.conf, Qui détermine la quantité de mémoire utilisé par celui-ci. On peut aller jusqu'à y mettre toute la mémoire libre obtenue avec la commande free; il est recommandé par l'éditeur de PostGreSQL de n'utiliser que 75% de la RAM totale de la machine.

shared_buffers

Ce paramètre doit être à un tiers du effective_cache_size. Il règle mise en cache des blocs du système de fichiers contenant les tables et les index. L'édiiteur recommande lui 25% de la RAM totale.

work_mem

Détermine la taille de la mémoire disponible pour les opérations de tris, regroupement, au niveau des requêtes utilisateur. Elle doit être à 1/3 de effective_cache_size, divisé par le nombre de connexion max., gérées par la directive max_connections.

maintenance_work_mem

Cette mémoire est celle qui est utilisée lors des opérations de liées à la réindexation, au vacuum et au pg_dump. Les valeurs recommandées vont de 50 à 64 Mo par Go de RAM sur la machine.

Outils

À noter qu'il existe un outil qui automatise ces opérations : pgtune, mais il n'est plus maintenu depuis 9 ans...

Il existe aussi le site https://pgtune.leopard.in.ua/#/ et son outil pgtune qui propose de générer des paramètres adaptés à la machine et à l'usage de la base de données.

Quant aux backup, il existe (en plus des solutions proposées pour MariaDB) l'outil pgbarman qui permet des sauvegardes "au fil de l'eau".