====== PostgreSQL ======
===== Dump & restauration =====
<WRAP center round info 60%>
pg_dump ne bloque pas les tables pendant la sauvegarde. La base peut donc continuer à être utilisée en lecture et écriture pendant le dump
</WRAP>
  * Faire le dump d'une base : <code bash># Le -W sert à afficher un prompt pour renseigner le mot de passe du user SQL. L'option -Fc permet de compresser l'archive 
pg_dump -U dbuser -W -d mydatabase -Fc > mydatabase.dump</code>
  * Faire le dump des roles : <code bash>pg_dumpall -U postgres --roles-only -w > roles.dump</code>
  * Transformer le dump (binaire) en fichier texte pour le lire : <code bash>pg_restore -f - mydatabase.bin > mydatabase.sql</code>
  * Restaurer une sauvegarde faite avec pg_dumpall (par exemple des roles) : <code bash>psql -U postgres -w roles.dump</code>
  * Restaurer une sauvegarde faite pg_dump : <code bash>pg_restore -d mydatabase mydatabase.dump</code>
Source : [[https://www.postgresql.org/docs/15/app-pgdump.html]]
===== Divers =====
<WRAP center round tip 60%>
Il est possible de se connecter directement à PostgreSQL en configurant un fichier .pgpass dans le home d'un user Unix. Le fichier doit avoir un niveau de permission 0600 et être à ce format : 
<code>
hostname:port:database:username:password

# Exemple pour un accès à toutes les bases en local
localhost:5432:*:postgres:mypassword
</code>
</WRAP>
Lister les bases :<code sql>\l;</code>
Afficher la structure d'une table :<code sql>\d matable;</code>
Afficher la taille d'une table :<code sql>select pg_size_pretty (pg_relation_size('matable'));</code>
Afficher l'historique des requêtes :<code sql>\s;</code>
Reset le mot de passe du user postgres: <code bash>
vim /etc/postgresql/XX/main/pg_hba.conf
# Remplacer la méthode md5 par trust pour le user postgres
systemctl restart postgresql
psql -U postgres
# Dans PostgreSQL
ALTER USER postgres with PASSWORD 'monmdp';
</code>

===== Mise à jour =====
==== Sur une VM ====
La mise à jour vers une nouvelle version majeure PostgreSQL implique d'installer les paquets de la nouvelle version, puis de mettre à jour les clusters.

Exemple avec une migration de PostgreSQL 11 à PostgreSQL 13 : <code bash>
systemctl stop postgresql
# Installation des paquets postgresql-11 postgresql-client-11 (directement ou via apt-get upgrade)
pg_dropcluster --stop 13 main
pg_lsclusters # Permet de lister les clusters présents et voir leur état
pg_upgradecluster 11 main
pg_dropcluster 11 main
apt-get purge postgresql-11 postgresql-client-11
</code>
==== Sous Docker ====
Grandes étapes : 
  * Créer un nouveau container dans la nouvelle version de PostgreSQL
  * Dump des données du vieux PostgreSQL dans un fichier via pg_dumpall
  * Import the data into the new container
  * Copier les données et les fichiers pg_hba.conf et postgresql.conf vers le nouveau container
  * Supprimer l'ancien container
Détail de la procédure : https://openqa-bites.github.io/posts/2023/2023-11-23-upgrade_a_postgresql_container_to_a_new_major_version/
===== Process =====
Lister les requêtes en cours : <code sql>SELECT * FROM pg_stat_activity;</code>
Tuer une requete : <code sql>SELECT pg_cancel_backend(<pid of the process>);
OU
SELECT pg_terminate_backend(<pid of the process>);</code>

===== Sources et compléments =====

https://www.postgresqltutorial.com/