# PostgreSQL Management

Ce document a pour but de rassembler les opérations de base qu'un administrateur de base de données PostgreSQL doit connaître.

## 1) Sauvegarde

- Avec la commande **pg_dump** (via CLI) :

Syntaxe : `pg_dump base_de_donnees > fichier_de_sortie`

Cette commande génère un fichier de type SQL. Ce fichier va permettre tout d'abord de mettre en place les différentes variables que Postgres a besoin (les timeouts, l'encoding, les différentes configurations...). Ensuite, il va créer ou altérer les différentes fonctions et tables de la base de données, copier les données dans ces tables, créer les index s'il n'y en a et enfin réattribuer les droits que l'on avait attribué aux utilisateurs.

Par exemple, pour faire une sauvegarde de la base de données `RDBMS`, on utilise la commande suivante : `pg_dump -vc rdbms | gzip > rdbms.gz`

L'option `-v` ou `--verbose` permet d'afficher les détails des instructions exécutées par la commande `pg_dump`.

L'option `-c` ou `--clean` permet de supprimer les éventuels objets de la base de données s'ils existent. C'est en fait des instructions `DROP` qui sont ajoutées au fichier SQL généré.

Le résultat de cette commande est ensuite compressé dans une archive nommée `rdbms.gz`. Cette solution peut être pertinente lorsque l'on souhaite sauvegarder les données d'une grosse base de données sinon on utilisera la première solution.

- Avec DBeaver :

Depuis DBeaver la manipulation à effectuer est la suivante : Clic droit sur la base de données > Tools > Backup > Sélectionner ce qu'on veut backup (une ou plusieurs tables ou toute la base) > Start

S'il n'y a pas de client local de configurer, il faut rajouter le chemin vers postgres `/usr/lib/postgresql/{version}`.

Tutoriel ici : [Restauration avec DBeaver](https://dbeaver.com/docs/wiki/Backup-Restore/).

- Avec le File System :

- Pour savoir dans quel dossier sont stockées les données de postgres :

`sudo -u postgres psql`

- Une fois dans le terminal psql :

`SHOW data_directory;`

Cela devrait afficher le chemin vers le dossier qui est similaire à celui-ci :

`/var/lib/postgresql/{version}/main`

- Ensuite, il suffit de stopper le service postgres :

`sudo systemctl stop postgresql`

- Copier le dossier des données à sauvegarder vers le dossier où l'on souhaite stocker une copie :

`cp -r /var/lib/postgresql/{version}/main dossier_de_destination`

- Redémarrer le service postgres :

`sudo systemctl start postgresql`

- Avec Docker :

`docker exec -u <utilisateur_postgres> <nom_conteneur_postgres> pg_dump -Fc <nom_base_de_données> > db.dump`

## 2) Restauration

- Avec la commande **pg_restore** :

Pour restaurer une sauvegarde dans une base de données existante, il suffit d'utiliser la commande suivante :

`pg_restore -d rdbms backup.tar.gz`

où `-d` est le nom de la base de données dans laquelle les données doivent être restaurées.

- Avec la commande **psql** :

Se connecter au préalable à l'utilisateur postgres :

`sudo -i -u postgres`

Puis à l'aide de la commande suivante on restaure une sauvegarde de la base de données `RDBMS` :

`psql rdbms < backup.tar.gz`

- Avec Docker :

`docker exec -i -u <utilisateur_postgres><nom_conteneur_postgres> pg_restore -C -d postgres < db.dump`

## 3) Nettoyage

L'instruction `VACUUM` récupère l'espace de stockage occupé par des lignes supprimées. Par défaut, les lignes supprimées ou rendues obsolètes par une mise à jour ne sont pas physiquement supprimées de leur table.

Il est donc nécessaire de lancer `VACUUM` régulièrement, surtout sur les tables qui sont fréquemment modifiées, afin de récupérer de l'espace de stockage.

Exemple d'utilisation :

`VACUUM [FULL] [VERBOSE] [ANALYZE]`

Le paramètre optionnel `VERBOSE` permet d'obtenir les détails de l'opération en cours.

Le paramètre optionnel `ANALYZE` permet de récupérer les statistiques sur une base de données.

Le paramètre optionnel `FULL` permet de récupérer plus d'espace mais est beaucoup plus long et prend un verrou exclusif sur la table.

Dans les dernières versions de PostgreSQL, le daemon autovacuum qui permet d'automatiquement exécuter la commande vaccum, est activé par défaut. Pour le désactiver il suffit de taper la commande suivante :

`ALTER TABLE table_name SET (autovacuum_enabled = false);`

## 4) Réindexation

L'instruction `REINDEX` reconstruit un index en utilisant les données stockées dans la table, remplaçant l'ancienne copie de l'index. Il y a deux raisons principales pour utiliser `REINDEX` :

- Un index a été corrompu et ne contient plus de données valides.
- L'index en question contient beaucoup de pages d'index mortes qui ne sont pas réclamées.

Syntaxe : `REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]`

## 5) Liste de contrôle d'accès (ACL)

Quand un objet est créé, il est assigné à un propriétaire. Un objet peut-être par exemple une table ou bien une base de données entière. De base, le propriétaire peut faire ce qu'il souhaite avec cet objet. Pour autoriser d'autres utilisateurs ou rôles à l'utiliser des privilèges doivent leur être attribués.

Il y a différents types de privilèges qui sont les suivants : `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, `CREATE`, `CONNECT`, `TEMPORARY`, `EXECUTE`, and `USAGE`. Ils correspondent aux différentes actions applicables sur un objet.

- Définir un utilisateur propriétaire d'une table :

`ALTER TABLE table_name OWNER TO new_owner;`

- Donner tous les privilèges à un utilisateur sur une base de données :

`GRANT ALL PRIVILEGES ON database_name TO user;`

- Enlever tous les privilèges à un utilisateur sur une table :

`REVOKE ALL PRIVILEGES ON table_name FROM user;`

Il existe 3 différents rôles :

- Droits administrateurs (superuser, correspondant au rôle de propriétaire)
- Droits Read/Write (correspondant au privilège `UPDATE`)
- Droits Read only (correspondant au privilège `READ`)

Plus d'informations sur la documentation : [Les privilèges](https://www.postgresql.org/docs/13/ddl-priv.html).

Il est possible de définir les droits d'accès aux bases de données sur une machine grâce au fichier `pg_hba.conf` situé par défaut dans le dossier `/etc/postgres/{version}/main`. Ce fichier définit quels hôtes sont autorisés à se connecter, à travers quelle méthode les clients se connectent, quels nom d'utilisateurs PostgreSQL ils peuvent utiliser et quelles base de données ils peuvent accéder (Documentation : [Fichier pg_hba.conf](https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html)).

---

Hugo AMALRIC DO3 - 20/03/2021