Postgres

Installer PostgreSQL
PostgreSQL est un système de gestion de base de données relationnelle et objet. C'est un outil libre disponible selon les termes d'une licence de type BSD. Ce système est concurrent d'autres systèmes de gestion de base de données, qu'ils soient libres, ou propriétaires tel que Oracle. 

 Installation de PostgreSQL 

 # Importation du repository 

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Importation des clés signé 

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

OK

# Installation version 14 de PostgreSQL

apt-get install postgresql-common postgresql-14 postgresql-contrib-14 postgresql-doc-14

# Vérifie que le service PostgreSQL fonctionne correctement

sudo service postgresql status 

   

 Changer le mot de passe administrateur 

 Maintenant il faut mettre un mot de passe pour l’utilisateur admin de Postgres. 

 # On se connecte sur l'utilisateur postgres

sudo su - postgres 

# On affecte un mot de passe 

psql -c "alter user postgres with password 'StrongDBPasswordUser'"

ALTER ROLE 

   

 Se connecter sur PSQL 

 Pour accéder au terminal PSQL qui permet d’administrer les bases de données sur PostgreSQL il suffit de se connecter sur l’utilisateur postgres et d'exécuter la commande psql. 

 > sudo su - postgres

> psql

psql (14.1 (Debian 14.1-1.pgdg110+1))

Saisissez « help » pour l'aide.

postgres=# 

  

Processus de création d'une BDD
1) Créer une BDD 

 CREATE DATABASE <dbname>; 

 Sortie : 

 postgres=# CREATE DATABASE exempledb;

CREATE DATABASE 

 2) Créer un utilisateur pour cette BDD 

 Il est préférable d'avoir un utilisateur par base donnée.  

 CREATE USER <username> WITH ENCRYPTED PASSWORD 'yourpass'; 

 Sortie : 

 postgres=# CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypass';

CREATE ROLE 

 3) Donner tous les droits sur la BDD 

 Une fois l'utilisateur et la base de donnée créé, pour que celui-ci puisse la manager il est nécessaire de donner des droits au nouvel utilisateur.  

 GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username>; 

 Sortie : 

 postgres=# GRANT ALL PRIVILEGES ON DATABASE exempledb TO myuser;

GRANT 

 4) Donner les droits de connexion 

 postgres=# GRANT CONNECT ON DATABASE exempledb TO myuser;

GRANT 

 5) Modifier le propriétaire 

 Modifier le propriétaire de la BDD pour mettre notre utilisateur récemment créé :  

 ALTER DATABASE db_name OWNER TO new_owner_name; 

 56) Se connecter sur la nouvelle BDD 

 psql -U <username> -d <dbname>

Le terminal PSQL
psql   — terminal interactif  PostgreSQL 

 

 Accéder à PSQL 

 Il existe différente façon d'accéder au terminal interactif de PostgreSQL. 

 Pour se connecter avec un utilisateur et son mot de passe : 

 psql -U <username> -W 

 Liste des options PSQL 

 

 

 

 Option PSQL 

 Description  

 

 

 -U  username 

 Se connecte à la base de données en tant que l'utilisateur 

 

 

 -W 

 Force  psql à demander un mot de passe avant de se connecter à une base de données 

 

 

 -d  dbname 

 

 Indique le nom de la base de données où se connecter 

 

 

 

 -h  host 

 

 Indique le nom d'hôte de la machine sur lequel le serveur est en cours d'exécution. (Pour se connecter à distance) 

 

 

 

 -p  port 

 

 Indique le port TCP a utiliser pour la connexion (par défaut: 5432) 

 

 

 

 -o  nomfichier 

 

 Dirige tous les affichages de requêtes dans le fichier  nomfichier . 

 

 

 

 -L  nomfichier 

 

 Écrit tous les résultats des requêtes dans le fichier nomfichier 

 

 

 

 

 

 Liste des commandes PSQL 

 L'invite devient -# (pour le super utilisateur) ou -> (pour l'utilisateur normal) pour la poursuite de la commande. 

 

 

 

 Commande PSQL 

 Description  

 

 

 \? 

 

 Pour les aides sur les commandes PSQL 

 

 

 

 \h 

 Pour les aides sur les commandes SQL 

 

 

 \du 

 Liste des utilisateurs 

 

 

 \q 

 Pour quitter PSQL 

 

 

 \l 

 Lister les base de données 

 

 

 \d 

 

 Affiche toutes les tables, indexes, vues et séquences 

 

 

 

 \dt  

 Affiche toutes les tables 

 

 

 \c dbname   [username] 

 Pour se connecter à une base de donnée avec en option un utilisateur

Gérer les utilisateurs
Se connecter à PostgreSQL 

 psql -h localhost -p 5432 -U <username> -W 

 Il est possible que la commande psql vous sorte une erreur de type : commande inconnue . 

 Dans ce cas vous allez devoir vous connecter sur l'utilisateur Linux possédant les droits d'utilisation de la commande psql.  

 su - postgres 

 

 Lister les utilisateurs  

 Dans l'interface de psql : 

 \du 

 

 Créer un utilisateur 

 Il existe deux commandes différentes pour réaliser cette action. 

 CREATE USER <username>; 

 ou 

 CREATE ROLE <username> WITH LOGIN; 

 

 Créer un utilisateur avec l'utilitaire 

 La création interactive d'un utilisateur est une option pratique disponible uniquement pour l'utilitaire client. Pour créer un utilisateur de manière interactive, exécutez la commande suivante : 

 sudo -u postgres createuser --interactive 

 

 Créer un super utilisateur 

 Un super utilisateur de base de données contourne toutes les vérifications, ce qui est dangereux du point de vue de la sécurité. Utilisez cette action avec précaution et évitez de travailler avec un compte super utilisateur sauf en cas de nécessité absolue. 

 Sur PostgreSQL le "super user" est postgres mais dans certain cas il est possible de vouloir créer son propre super user.  

 Pour créer un super utilisateur : 

 CREATE USER <username> SUPERUSER; 

 Si cet utilisateur doit avoir un mot de passe : 

 CREATE USER <username> WITH SUPERUSER PASSWORD 'passwordstring'; 

 Mot de passe 

 Dans le cas ou vous avez créé un utilisateur sans mot de passe il possible de lui rajouter le mot de passe via la commande suivante. 

 ALTER USER <username> WITH PASSWORD '<password>'; 

 Créer un utilisateur avec des droits 

 CREATE USER <name> WITH <option>; 

 

 

 

 Option Syntax 

 PSQL 

 Explanation 

 

 

 -s --superuser 

 SUPERUSER 

 Add the superuser privilege. 

 

 

 -S --no-superuser 

 NOSUPERUSER 

 No superuser privilege (default). 

 

 

 -d --createdb 

 CREATEDB 

 Allows the user to create databases. 

 

 

 -D --no-createdb 

 NOCREATEDB 

 Not allowed to create databases (default). 

 

 

 -r --createrole 

 CREATEROLE 

 Allows the user to make new roles. 

 

 

 -R --no-createrole 

 NOCREATEROLE 

 Not allowed to create roles (default). 

 

 

 -i --inherit 

 INHERIT 

 Automatically inherit the privileges of roles (default). 

 

 

 -I --no-inherit 

 NOINHERIT 

 Do not inherit privileges of roles. 

 

 

 -l --login 

 LOGIN 

 Allows the user to log into a session with the role name (default). 

 

 

 -L --no-login 

 NOLOGIN 

 Not allowed to log into a session with the role name. 

 

 

 --replication 

 REPLICATION 

 Allows initiating streaming replication and activating/deactivating backup mode. 

 

 

 --no-replication 

 NOREPLICATION 

 Not allowed to initiate streaming replication or backup mode (default). 

 

 

 -P --pwprompt 

 PASSWORD '<password>' 

 Initiates password creation prompt or adds provided password to the user. Avoid using this option to create a passwordless user. 

 

 

 / 

 PASSWORD NULL 

 Specifically sets the password to null. Every password authentication fails for this user. 

 

 

 -c <number> --connection-limit=<number> 

 CONNECTION LIMIT <number> 

 Sets the maximum number of connections for user. Default is without limit. 

 

 

 

 

 Changer les droits d'un utilisateur 

 Par exemple pour donner les droits de création d'une base de donnée a un utilisateur : 

 ALTER USER <username> CREATEDB; 

 Dans le même ordre d'idée on peux donner les droits super utilisateur : 

 ALTER USER <username> WITH SUPERUSER; 

 Mais aussi les enlever : 

 ALTER USER <username> WITH NOSUPERUSER; 

 Changer le mot de passe d'un utilisateur 

 ALTER USER user_name WITH PASSWORD 'new_password'; 

 Supprimer un utilisateur 

 DROP USER [IF EXISTS] <username>; 

 Si l'utilisateur que vous essayez de supprimer possède des dépendances, la suppression échouera. 

 Vous allez devoir transférer les dépendances a un autre utilisateur.  

 1) Par exemple, pour transférer les objets appartenant à myuser à postgres, exécutez : 

 REASSIGN OWNED BY <old_user> TO <new_user>; 

  2) Supprimer les connexions de l'objet de la base de données à l'utilisateur avec : 

 DROP OWNED BY <username>; 

 3) Maintenant vous pouvez supprimer l'utilisateur 

 

 Supprimer un rôle  

 DROP ROLE [IF EXISTS] <name>; 

 

 Donner l'accès à une BDD à un utilisateur 

 GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <username>;

Sauvegarder une BDD
Réaliser une sauvegarde d'une BDD 

 Pour créer une sauvegarde ( backup en Anglais) de la base de données sur PostgreSQL c’est très simple car il existe un utilitaire pg_dump pour créer une sauvegarde même si la base données est en cours d’utilisation. 

 Les sauvegardes peuvent être produits dans des formats de script ou de fichier d'archive. elles sont constitué des fichiers en texte clair contenant les commandes SQL nécessaires pour reconstruire la base de données dans l'état où elle se trouvait au moment où elle a été enregistrée. 

 Tout d’abord nous allons créer un répertoire de sauvegarde pour centraliser les sauvegarde de base de données. Puis nous allons créer une sauvegarde pour vérifier que tout fonctionne. Ensuite nous allons créer un script de sauvegarde de BDD qui s’exécutera périodiquement. 

 

 Création du répertoire des sauvegardes 

 sudo mkdir -p /var/backup 

 Créer une sauvegarde 

 Commande pour créer une sauvegarde. Remplacer les variables par les bonnes valeur avant de l'exécuter. 

 

 

 

 ${DB_USER} 

 Utilisateur ayant accès a la base de données 

 

 

 ${DB_PASSWORD} 

 Mot de passe de l'utilisateur 

 

 

 ${DB_HOST} 

 Host de la base de données : Adresse IP ou nom de domaine 

 

 

 ${DB_NAME} 

 Nom de la base de données 

 

 

 

 /usr/bin/pg_dump postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}/${DB_NAME} | gzip -c > /var/backup/"${DB_NAME}-`date +%d-%m-%y_%T`".sql.gz; 

 Le fichier générer sera sous le nom suivant : ${DB_NAME}-${DATE}_${HEURE}.sql.gz  

   

 Automatiser la sauvegarde 

 Le processus de remplacement des variables pour réaliser une sauvegarde peut-être long et fatiguant. Il est serait plus pratique de créer un script regroupant les démarches précédentes.  

 Création du script 

 sudo nano save-database 

 Contenu du script  

 #!/bin/bash

# # # # # # # # # # # # # # #

# SAVE DATABASE by gpatruno #

# # # # # # # # # # # # # # #

# définition des variables

backupdir='/etc/backup';

datefolder=`date +%m-%y`;

datescript=`date +%d-%m-%y_%T`;

DB_USER=postgres

DB_NAME=dbname

DB_PASSWORD=dbpassword

DB_HOST=localhost

echo "Répertoire de destination : $backupdir/$datefolder"

mkdir -p $backupdir/$datefolder;

echo "PostgreSQL Backup for database : ${DB_NAME} start at " `date +%T`;

echo "Exporting ${DB_NAME}";

# Pour conserver la version non compressé du fichier 'gzip -c fichier-de-sortie.ext > fichier-de-sortie.ext.gz'

# Sinon compresse le fichier fichier-de-sortie et la remplace par la version compressé nommé avec l’extention *.gz 'gzip fichier-de-sortie.ext.gz'

echo "/usr/bin/pg_dump postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}/${DB_NAME} | gzip -c > $backupdir/$datefolder/"${DB_NAME}-$datescript".sql.gz;"

/usr/bin/pg_dump postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}/${DB_NAME} | gzip -c > $backupdir/$datefolder/"${DB_NAME}-$datescript".sql.gz;

echo "done";

echo `date +%d-%m-%y`" "`date +%T`; 

 Donner le droit d'exécution 

 sudo chmod +x save-database 

 Exécuter le script 

 ./save-database 

 L'exécution du script donne la sortie suivante : 

 Répertoire de destination : /var/backup

PostgreSQL Backup for database : dbname start at 16:38:07

Exporting dbname 

/usr/bin/pg_dump postgresql://postgres:dbpassword@localhost/dbname | gzip -c > /etc/backup/dbname-03-12-21_16:38:07.sql.gz;

done

03-12-21 16:38:07 

   

 Exécution régulière 

 Afin de mettre en place une exécution régulière du script nous allons utiliser l'outil Linux appelé Cron . C'est un programme disponible sur les systèmes de type Unix (Linux, Mac Osx ...) permettant de planifier des taches régulières. 

 Ouvrir Crontab 

 crontab -e 

 Mettre le contenu suivant  

 # Exécution du script tous les jours à 2h du matin

0 2 * * * /bin/sh /script/save-database 

 La tâche Cron ci-dessus sera exécutée tous les jours à 2 heures du matin et lancera un script save-database avec l'interpréteur bash . Ceci maintiendra la sauvegarde chaque jour.

Restaurer une BDD
Restaurer une sauvegarde 

 Nous allons créer un script de restauration afin d'éviter de répéter les commandes. 

 Dans le même dossier que pour le script de sauvegarde nous allons créer un script restore-database avec en paramètre le nom du fichier de sauvegarde a utiliser. 

 Création du script 

 sudo nano restore-database 

 Contenu du script 

 #!/bin/bash

# # # # # # # # # # # # # # # # #

# RESTORE DATABASE by gpatruno #

# # # # # # # # # # # # # # # # #

# Récupération de l'emplacement du fichier de sauvegarde

read -p "Emplacement du fichier de sauvegarde : " BACKUPDIR

# Récupération du nom du fichier de sauvegarde

read -p "Nom du fichier de sauvegarde : " FILENAME

# définition des variables

SCRIPTNAME="${FILENAME/.gz/""}";

DB_USER=postgres

DB_NAME=dbname

DB_PASSWORD=dbpassword

# RESTORE

echo `date +%d-%m-%y`" "`date +%T`;

# Dézip de la sauvegarde

echo "Unzip $BACKUPDIR/$FILENAME";

gunzip -c $BACKUPDIR/$FILENAME > $SCRIPTNAME

# Déconnecter tous les utilisateurs

echo "Disconnect all users from the db";

/usr/bin/psql postgresql://${DB_USER}:${DB_PASSWORD}@localhost/${DB_NAME} -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$DB_NAME' AND pid <> pg_backend_pid();"

# Supprimer l'ancienne database si elle existe

echo "DROP DATABASE $DATABASE";

/usr/bin/psql postgresql://${DB_USER}:${DB_PASSWORD}@localhost -c "DROP DATABASE \"$DB_NAME\";"

# Créer la nouvelle Base de données

echo "CREATE DATABASE $DB_NAME";

/usr/bin/psql postgresql://${DB_USER}:${DB_PASSWORD}@localhost -c "CREATE DATABASE \"$DB_NAME\";"

# Restauration de la nouvelle BDD

echo "RESTORE DATABASE $SCRIPTNAME";

/usr/bin/psql postgresql://${DB_USER}:${DB_PASSWORD}@localhost/${DB_NAME} -f $SCRIPTNAME

echo `date +%d-%m-%y`" "`date +%T`; 

 Donner le droit d'exécution 

 sudo chmod +x restore-database 

 Puis exécuter le script et remplir les paramètres demandé au fur-à-mesures de l'avancement.