PostgreSQL

Dump & restauration

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

Faire le dump d'une base :

# 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

Faire le dump des roles :

pg_dumpall -U postgres --roles-only -w > roles.dump

Transformer le dump (binaire) en fichier texte pour le lire :
```
pg_restore -f - mydatabase.bin > mydatabase.sql
```
Restaurer une sauvegarde faite avec pg_dumpall (par exemple des roles) :
```
psql -U postgres -w roles.dump
```

Restaurer une sauvegarde faite pg_dump :

pg_restore -d mydatabase mydatabase.dump

Source : https://www.postgresql.org/docs/15/app-pgdump.html

Divers

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 :

hostname:port:database:username:password

# Exemple pour un accès à toutes les bases en local
localhost:5432:*:postgres:mypassword

Lister les bases :

\l;

Afficher la structure d'une table :

\d matable;

Afficher la taille d'une table :

SELECT pg_size_pretty (pg_relation_size('matable'));

Afficher l'historique des requêtes :

\s;

Reset le mot de passe du user postgres:

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';

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 :

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

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 :

SELECT * FROM pg_stat_activity;

Tuer une requete :

SELECT pg_cancel_backend(<pid OF the process>);
OU
SELECT pg_terminate_backend(<pid OF the process>);

Sources et compléments

https://www.postgresqltutorial.com/