m2 tiw administration des systèmes et des bases de données

L. Avrot - N. Lumineau

M2 TIWAdministration des Systèmes et des Bases de Données

ASBD

Sujets des TP pour l’administration des BDVersion 3.1.4

Travaux PratiquesTP1 - Configuration et déploiement de bases Oracle et PostgreSQLTP2 - Restauration des Bases de DonnéesTP3 - Optimisation de requêtes

PréambuleObjectifs

La réalisation de ces TP d’administration des Bases de Données vous permettra :1. d’acquérir les compétences techniques vous permettant d’être opérationnel·le pour installer et confi-

gurer une base de données de type oracle ou postgreSQL2. de vous construire un script de configuration automatique de bases postgreSQL3. d’acquérir les compétences techniques pour intervenir lors de pertes de données en restaurant une base

postgreSQL .4. d’acquérir les compétences techniques pour optimiser l’exécution de requêtes interrogeant des tables

de grandes tailles

Informations pratiquesOrganisation

Pour réaliser ce TP, vous pouvez travailler seul·e ou en binôme (les trinômes sont interdits).

Modalités d’évaluation

Pour chaque TP, vous devez fournir un rapport répondant à la sous-section ’Rendu attendu’ de chaquesection ’Travail à réaliser’. Ce rapport sera à déposer sur TOMUSS dans la cellule correspondant au TP(AdminTP1, AdminTP2 et AdminTP3).

A propos de la connexion à la VM.

Le TP se déroulera sur les machines virtuelles (VM) hébergées dans le cloud du département informa-tique. Les fichiers oracle.pem 1 et oracle.ppk 2 pour établir la connexion SSH ont été transmis par mail. Lesadresses IP des VM qui vous ont été attribuées se trouvent sur TOMUSS.

La connexion SSH peut se faire par exemple à l’aide de la commande suivante :

ssh -i pedabdcloud -X -L 1158:localhost:1158 -L 1521:localhost:1521 centos@xxxx

Le port 1521 est le port par défaut pour le TNSListener, le 1158 est le port d’enterprise manager et xxxxcorrespond à l’IP fournie en début de TP sur TOMUSS.

A propos de la configuration de la VM.

Une fois connecté·e, il est nécessaire de modifier :— le fichier /etc/hosts pour associer votre IP au nom de votre VM.

1. pour établir la connexion depuis un poste Linux2. pour établir la connexion depuis un poste Windows

ASBD - Administration des BD 2 Année 2017-18

TP1 - Configuration et déploiement de BD

L’objectif de ce TP est de vous familiariser avec l’installation et le déploiement d’instances de bases dedonnées qu’elles soient de type oracle ou de type postgreSQL .

1 Configuration d’une instance Oracle1.1 ScénarioBob a été embauché comme DBA dans l’entreprise en prévision du départ dans les mois à venir d’Alice quiest l’actuelle BDA. Cette entreprise vient d’acquérir une licence oracle pour pouvoir gérer les donnéesd’un projet interne. Pour pouvoir mettre en place la base de données, Charlie vous présente brièvement leprojet.

Charlie

"Pour votre première mission, je vous mets sur le projet P1BO. Nous avons be-soin que la base de données supporte une utilisation d’environ 50 utilisateursou utilisatrices. A priori, la base permettra le stockage d’environ 5 Go de don-nées. Il faut que vous preniez toutes les précautions nécessaires pour éviter touteperte de données et garantir qu’une restauration de la base sera possible en casde défaillance matérielle ou humaine. Pour finir, nos filiales auront besoin de seconnecter à distance à la base.Comme vous êtes nouveau dans l’entreprise, je vous invite à vous rapprocherd’Alice qui sera votre référent technique."

"Très bien, je me rapproche tout de suite d’Alice pour en savoir un peu plussur les standards de l’entreprise au niveau des configurations. Par contre, je mepermets de préciser que bien évidemment je mettrai tout en œuvre pour réduireau maximum les risques de pertes de données, mais, comme n’importe quel BDAvous le dira, je ne pourrai pas vous garantir le ’zéro risque’ de perte de données."

Bob

Alice

"Pour réaliser ton job, je te rappelle la norme dans cette entreprise. Il est néces-saire que tu aies une gestion automatique des paramètres de base via un spfile.Pour ce qui est du calibrage du nombre de processus lié au nombre d’utilisateurs,je te laisse chercher dans la documentation Oracle. Pour le paramétrage des ta-blespaces, hormis celui dédié au stockage des données utilisateurs qu’il te faudracalculer, le mieux est de leur permettre une extension de taille automatique. Sijamais tu as un souci de RAM lié à la machine sur laquelle tu travailles, pensesà créer de la swap.Évidemment, tu auras les dictionnaires à mettre en place. Tu feras attention dansla gestion de ton temps, car l’exécution des scripts peut être ’un peu’ longue, quece soit pour la création du catalogue ou la mise en place de PL/SQL.Pour t’éviter bien des problèmes en cas de restauration à effectuer, tu pense-ras d’une part à multiplexer tes fichiers de contrôle comme le préconise notrestandard. D’autre part, tu penseras à mettre en place 3 groupes de fichiers dejournalisation (Redo Log). Pour finir, tu penseras à configurer l’interface réseaudu SGBD pour permettre son interrogation distante via des ’DATABASE LINK’.Pour gagner un peu de temps pour cette installation, je te conseille d’utiliserl’interface graphique DBCA. Évidemment tu prendras le ’mode avancé’ et non lemode par défaut. Pour gagner beaucoup de temps à la prochaine installation, tupenseras à te faire un mémento sous la forme d’un document contenant toutestes actions via des impressions écrans (quand tu cliques sur une option lors de laconfiguration) et toutes les commandes que tu exécutes."


Extrait du standard de l’entreprise— Les fichiers de contrôle sont stockés dans le répertoire

Dir4Mutiplex créé à la racine de la VM— Les fichiers d’archivage sont stockés dans le répertoire

Dir4Archive créé à la racine de la VM— Taille de la zone de mémoire partagée : 1024— L’encodage des caractères est AL32UTF8— Taille maximale des fichiers de journalisation : 64 Mo— Nombre maximum de fichiers de journalisation : 32

1.2 Travail à réaliserIl vous est demandé de réaliser l’installation et la configuration de la base oracle en respectant les

directives de la dirigeante et en vous basant sur les recommandations du DBA senior.

Rendu attenduVotre rapport de TP1 devra au moins contenir, pour cette partie, les informations suivantes :— les copies d’écran des différentes fenêtres de configuration de DBCA.— une copie d’écran d’une connexion à votre base ORACLE via ORACLE SQL Developer.— une copie d’écran d’au moins une table créé dans votre base avec l’affichage d’au moins 3 tuples

peuplant cette base.— une copie d’écran montrant l’affichage d’une requête effectuant la jointure distribuée entre une table

de votre instance et une table issue d’une autre instance (d’un autre binôme).

1.3 Informations pratiques1.3.1 A propos de la documentation.

Il est fortement conseillé d’utiliser l’aide en ligne d’Oracle :http://docs.oracle.com/database/121/index.htm.https://docs.oracle.com/database/121/ADMQS/GUID-F922EBB9-BA89-4A94-B89F-E3BB4BA14ACD.htm

1.3.2 A propos des utilisateurs sur la VM.

La commande su centos permet de se connecter en tant que root sur la VM.La commande su oracle permet de se connecter en tant qu’utilisateur oracle qui est le sysdba, à utiliser

pour toutes les opérations sur la base. Le mot de passe est "mdp:oracle".

1.3.3 A propos de la configuration de la base de données.

— Oracle est installé dans le répertoire /ora01/app/oracle/product/12.1.0/db_1— Pour la gestion des variables d’environnement l’utilitaire shell oraenv peut être utilisé :

source /usr/local/bin/oraenven indiquant /ora01/app/oracle/product/12.1.0/db_1 comme répertoire ORACLE_HOME

De plus, assurez vous que $ORACLE_HOME/bin est bien dans $PATH et que $ORACLE_SID est correct(correspond bien à votre nom d’instance)

— L’utiltaire d’installation dbca se trouve dans le répertoire :/ora01/app/oracle/product/12.1.0/db_1/bin


2 Configuration de plusieurs instances postgreSQL2.1 ScénarioSuite à la réalisation et au succès du projet P1BO, Bob est affecté à un projet de plus grande ampleur,nommé PnBP. La dirigeante vous donne quelques informations.

Charlie

"Vous allez travailler sur un projet PnBP. Dans ce projet, nous devons mettreen production notre solution logicielle pour une vingtaine de clients. Pour desraisons de confidentialité, tous ces clients souhaitent un hébergement dédiédans notre datacenter. Pour chaque base, nous n’avons encore pas beaucoupd’informations. Le nombre d’utilisateurs et la quantité de données à stocker nesont pas encore clairement définis. Comme toujours, il faut que vous preniez lesprécautions nécessaires pour réduire les risques de perte de données. Pour pouvoirfaire les premiers tests, il me faudrait les 2 premières instances postgreSQL auplus vite."

Face au problème, Bob se rapproche du DBA senior qui le conseille.

Alice

"Pour ce projet, il est nécessaire de passer par un couche de virtualisation. Tun’as pas le choix ! Il va falloir automatiser tout le processus d’installation, deconfiguration et de déploiement de tes bases. Face à une telle voilure d’instances,tu devras être vigilant pour garantir la standardisation de tes installations afinde gérer efficacement tes fichiers de configuration en cas d’intervention.Je te donne ma procédure d’installation manuelle de PostgreSQL pour que tupuisses te rendre compte des tâches à automatiser.Dans l’entreprise, notre outil d’automatisation est PUPPET. Il va falloir que tute réalises un script d’installation automatique de postgreSQL via PUPPET."

2.2 Travail à réaliserIl vous est demandé de réaliser une procédure d’installation automatisée de 2 instances postgreSQL viaun master puppet . Vous exploiterez au mieux les recommandations du DBA senior.

Extrait du standard de l’entreprise— mots de passe par défaut mdp:postgresql— nom du cluster : main— nom de la base de données : PnBP— nom de l’utilisateur applicatif : PnBP— répertoire du cluster : /var/li/postgresql/<version>/<cluster>— répertoire des fichiers de configuration : /etc/postgresql/<version>/<cluster>

Rendu attenduVotre rapport de TP1 devra au moins contenir, pour cette partie, les informations suivantes :— le script contenant la procédure d’installation automatisée.— une copie d’écran d’une connexion à votre base postgreSQL .— des copies d’écran d’au moins une table créé dans chacune de vos bases avec l’affichage d’au moins 3

tuples peuplant ces bases.— un copie d’écran montrant l’affichage d’une requête effectuant la jointure distribuée entre des tables

de vos différentes instances.


2.3 Informations pratiquesQuelques liens vers des tutoriels :Pour la mise en place de puppet :https://www.digitalocean.com/community/tutorials/how-to-install-puppet-4-on-ubuntu-16-04Attention : la déclaration du server au niveau de l’agent nécessitera la commande :sudo /opt/puppetlabs/bin/puppet agent -t --server asbd-puppet-XXX.novalocal

https://codingbee.net/tutorials/postgresql/postgresql-automate-postgresql-installationsetup-using-puppet

2.4 Procédure d’installation manuelle de PostgreSQLRechercher la bonne documentationAller sur https://www.postgresql.org/download/ et cliquer sur le lien correspondant à son système.Indiquer la version précise de son système.

Indiquer le repository du PGDGIl faut créer un fichier pour dire à ‘apt-get‘ d’aller chercher le paquet dans le repository du PGDG. Il fautensuire ajouter la clé du repository.

sudo vi /etc/apt/sources.list.d/pgdg.listcat /etc/apt/sources.list.d/pgdg.listdeb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg mainwget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \> sudo apt-key add -OK

Mettre à jour ‘apt-get‘Enfin, on met à jour les repositories d’‘apt-get‘.

sudo apt-get updateAtteint:1 http://fr.archive.ubuntu.com/ubuntu xenial InReleaseAtteint:2 http://fr.archive.ubuntu.com/ubuntu xenial-updates InReleaseAtteint:3 http://fr.archive.ubuntu.com/ubuntu xenial-backports InReleaseAtteint:4 http://security.ubuntu.com/ubuntu xenial-security InRelease...168 ko réceptionnés en 1s (94,3 ko/s)Lecture des listes de paquets... Fait

Télécharger et installer les paquetsIl n’y a plus qu’à demander à apt-get de récupérer et d’installer les paquets PostgreSQL :

sudo apt-get install postgresql-9.6 postgresql-contrib-9.6Lecture des listes de paquets... FaitConstruction de l’arbre des dépendancesLecture des informations d’état... Fait...Creating new PostgreSQL cluster 9.6/main .../usr/lib/postgresql/9.6/bin/initdb -D /var/lib/postgresql/9.6/main --auth-local peer--auth-host md5

Les fichiers de ce cluster appartiendront à l’utilisateur « postgres ».Le processus serveur doit également lui appartenir.

L’instance sera initialisée avec la locale « fr_FR.UTF-8 ».L’encodage par défaut des bases de données a été configuré en conséquence avec « UTF8 ».La configuration de la recherche plein texte a été initialisée à « french ».


Les sommes de contrôles des pages de données sont désactivées.

correction des droits sur le répertoire existant /var/lib/postgresql/9.6/main... okcréation des sous-répertoires... oksélection de la valeur par défaut de max_connections... 100sélection de la valeur par défaut pour shared_buffers... 128MBsélection de l’implémentation de la mémoire partagée dynamique...posixcréation des fichiers de configuration... oklancement du script bootstrap...okexécution de l’initialisation après bootstrap...oksynchronisation des données sur disqueok

Succès. Vous pouvez maintenant lancer le serveur de bases de données en utilisant :

/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main -l |\journal_applicatif start

Ver Cluster Port Status Owner Data directory Log file9.6 main 5432 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log...

Creating config file /etc/default/sysstat with new versionupdate-alternatives: utilisation de « /usr/bin/sar.sysstat » pour fournir « /usr/bin/sar »(sar) en mode automatiqueTraitement des actions différées (« triggers ») pour libc-bin (2.23-0ubuntu9) ...Traitement des actions différées (« triggers ») pour systemd (229-4ubuntu17) ...Traitement des actions différées (« triggers ») pour ureadahead (0.100.0-19) ...

Démarrer l’instanceIl n’est pas nécessaire d’initialiser le cluster car cela été fait par défaut par apt-get après l’installation despaquets.

sudo pg_ctlcluster 9.6 main start

Lancer un clientOn peut lancer un client avec le user postgres (qui a été créé lors de l’installation du paquet).

sudo -u postgres psqlpsql (9.6.4)Saisissez « help » pour l’aide.

postgres=#


TP2 - Restauration de données

L’objectif de ce TP est de vous mettre en face d’un problème de perte de données nécessitant la restaurationd’une base.

1 Mise en place du système de sauvegarde1.1 Scénario

Suite au travail réalisé par Bob , Alice vient voir Bob .

Alice "Je viens de voir le travail que tu as effectué sur le projet PnBP. C’est très bien !Par contre, je n’ai rien vue en place pour gérer la restauration des données en casde défaillance ou d’erreurs utilisateurs. Il faudrait que tu t’en occupe rapidement."

"Quels sont les outils utilisés pour pouvoir faire les restauration"

Bob

Alice "Il va falloir que tu installes pgbackrest, et que tu créés ta procédure derestauration à partir de la dernière sauvegarde ou à partir d’une action malen-contreusement exécutée."

1.2 Travail à réaliserDans cette partie du TP, il vous est demandé de :— d’installer pgbackrest— de peupler votre base via pg_bench— de dénombrer pour chaque table son nombre de tuples— d’effectuer une sauvegarde— d’exécuter la requête D1 : "DELETE FROM pgbench_tellers ;"— d’exécuter une nouvelle sauvegarde— d’exécuter la requête R1 : "SELECT pg_current_wal_lsn(), current_timestamp ;"— d’exécuter la requête D2 : "DELETE FROM pgbench_accounts WHERE bid = 2 ;"— d’insérer au moins 3 tuples dans la table pgbench_accounts— de bien valider les insertions par un ’commit’— de restaurer la base dans l’état précédent la modification effectuée par la requête D1

Pour réaliser ce travail, vous utiliserez l’instance que vous avez créée lors du TP1 via PUPPET 3.

Rendu attenduVotre rapport de TP2 devra contenir, pour cette partie,rapport sera constitué des scripts exécutés et descopies d’écran validant les exécutions.

3. Dans le cas contraire, vous disposez des instructions pour faire une installation manuellement dans le sujet du TP1


1.3 Informations pratiques1.3.1 A propos de pgbackrest

Vous devez installer manuellement la dernière version de pgbackrest (i.e., 1.2.6 dont les instructions sont ici :http://pgbackrest.org/user-guide.html. Il est possible que certaines librairies C et python soient manquantessur les VMs 4.

1.3.2 A propos de pg_bench

pg_bench est un utilitaire fourni avec postgreSQL pour simuler des transactions et que vous utiliserezpour simuler une charge de production. Il permet de simuler plusieurs connexions de clients. Il faut d’abordinitialiser la base avec l’option -i, puis lancer l’outil en paramétrant le nombre de clients et la durée detraitement. La documentation est ici : https://www.postgresql.org/docs/10/static/pgbench.html.

Exemple : pour le TP, vous pourrez par exemple utiliser en tant qu’utilisateur ’postgres’ la commande :pgbench -i -s 60 <nomBase>

1.3.3 Extrait du standard de l’entreprise

— dans pgbackrest, le nom du cluster est ’main’ (vous pouvez réutiliser celui créé lors du TP1).— dans pgbackrest, le ’stanza’ a le même nom que le cluster.

2 Restauration depuis une action donnée2.1 Scénario

David le développeur débarque dans le bureau de Bob complètement paniqué.

David "J’ai perdu une 100000 comptes dans la table ’pgbench_accounts’ de la base dedonnées du projet PnBP ! Je ne sais pas qui a fait le DELETE et je ne veux passavoir, par contre, il fait que tu me retrouves quand le requête a été exécutée etque tu me restaures les comptes au plus vite."

"OK ! Je vais regarder dans les logs au plus vite via pg_waldump."

Bob

2.2 Travail à réaliserPour cette partie du TP, il vous est demandé :— d’identifier la date où la requête D2 a été exécutée en utilisant pg_waldump— de restaurer la base pour rétablir les comptes de "pgbench_accounts" manquants

Rendu attenduVotre rapport de TP2 devra au moins contenir, pour cette partie, les copies d’écran montrant l’état de labase avant et après restauration.

2.3 Informations pratiques2.3.1 A propos de pg_waldump

L’utilitaire pg_waldump permet de fournir une vue lisible des logs. Sa documentation est ici :https://www.postgresql.org/docs/current/static/pgwaldump.html.

Pour pouvoir identifier des actions telles qu’une suppression survenue dans une table, il est nécessaire :

4. dont python3 python-dev python3-dev build-essential libssl-dev libffi-dev libxml2-dev libxslt1-dev zlib1g-dev python-piplibdbd-pg-perl libio-socket-ssl-perl libxml-libxml-perl


— d’identifier les identifiants du tablespace, de la base et de la table. Ce type d’information peut s’obtenirvia une requête SQL de la forme :

SELECT COALESCE( tbs . oid , db . da t tab l e space ) AS tab le space ,db . o id AS database ,t . o id AS table

FROM pg_class t LEFT OUTER JOIN pg_tablespace tbsON t . r e l f i l e n o d e=tbs . o id

CROSS JOIN pg_database dbWHERE t . relname=’<nom−de−ma−tab le>’

AND db . datname=current_database ( ) ;

— d’isoler dans un fichier les instructions DELETE via un pipeline entre le résultat du ’pg_waldump’,des grep sur le mot clé ’DELETE’ et sur les identifiants de la table (sous le format : ’"rel oidTables-pace/oidBase/oidTable").

2.3.2 Extrait du standard de l’entreprise

— Pour pouvoir travailler sur les log, il est recommandé de les stocker dans une répertoire à part ’/tmp/-WAL’ dans lequel les logs pourront être dézipés, renommés si nécessaire, manipulés. En cas de problèmepour dézipper les wal qui sont encryptés, il est nécessaire de récupérer le wal dans un format lisible enfaisant :pgbackrest –stanza=main archive-get <nomDuWal> "<pathDestination>"’;

3 ÉpilogueSuite aux restaurations réussies, Alice vient féliciter Bob .

Alice "Félicitations ! Tu as eu les bons réflexes pour la gestion de crise sur le projetPadBol. David a eu de la chance de ne pas avoir fait cette boulette sur le projetP1BO sur lequel tu as travaillé à ton arrivée. "

"Pourquoi ? Il y a bien RMAN pour la restauration de données sur oracle ."

Bob

Alice "Effectivement ! Par contre, si tu envisages d’utiliser certaines fonctionnalitéscomme le flashback de RMAN par exemple, il faudra vérifier nos contrats pours’assurer que lesdites fonctionnalités sont comprises dans ce qui a été payé. Çafait partie de notre boulot de DBA."


TP3 - Optimisation de requêtes

L’objectif de ce TP consiste à explorer les possibilités d’optimisation de requêtes sur oracle .

1 Impact de l’indexation sur les requêtes en lecture1.1 Scénario

Charlie et David débarquent débarque dans le bureau de Bob .

Charlie"Je viens d’avoir le client du projet ’AstroD’. Il observe des latences sur letraitement de certaines requêtes. Est-ce qu’il y aurait moyen d’améliorer leschoses ? stp."

"Il y a sûrement moyen de faire de l’optimisation. Quelles sont les requêtes quiposent problème ?"

Bob

David

"La base contient deux relations objects et sources.Parmi les requêtes qui posent un problème, il y a :La requête R1 qui est : "Pour chaque objet de la relation objects, donner lenombre de sources de la relation sources correspondant à cet objet".La requête R2 qui est : "Donner les pairs de sources qui ont la même valeur deRA arrondi à 0.1 près".La requête R3 qui est : "Donner les objets qui ne sont pas associés à une source".

"OK ! Je vous fais un audit de la situation actuelle pour le traitement de cestrois requêtes. Ensuite, je créerai quelques index et je vous ferai un rapport pourfaire le point."

Bob

1.2 Travail à réaliserIl vous est demander de faire :1. l’étude des plans d’exécution des requêtes R1, R2 et R3 et estimation des coûts associés.2. créer les index qui vous semblent les plus appropriés pour chacune des requêtes.3. l’étude des plans d’exécution avec index des requêtes R1, R2 et R3 et estimation des coûts associés.

Rendu attenduVotre rapport de TP3, pour cette partie, devra au moins contenir les copies d’écran montrant les différentsplans d’exécution et les vos commentaires sur les observations.

1.3 Informations pratiquesBase de donnée : ORA12C login / passwd sur la base ORA12C : petasky/petaskyRemarques :

— Modifier /etc/hosts pour prendre en compte votre ip et votre nom de machine (si vous ne l’avez pasfait lors du premier TP)

— Penser à vérifier vos variables d’environnements avant de démarrer l’instance ORA12C password sys-tem et sys : mdp:oracle.


2 Impact de l’indexation sur les requêtes en écriture2.1 Scénario

Alice qui a pris connaissance de l’audit sur le projet AstroD vient voir Bob

Alice "C’est du bon travail ! Bravo. Par contre, je suis surprise qu’il n’y ait pas un motsur la taille des index sur le disque ni sur l’impact de tes index sur les requêtesen écriture."

"Gloups ! Euh... effectivement il faudrait regarder ces aspects."

Bob

2.2 Travail à réaliserIl vous est demander de :1. déterminer la taille de chaque index qui a été créés dans la section précédente.2. écrire une requête SQL de mise à jour E1 qui applique la fonction f : x → 2x2 à l’attribut RA de la

table objects.3. évaluer le temps d’exécution de la requête E1.4. évaluer à nouveau le temps d’exécution de la requête E1, après avoir supprimé vos index.5. comparer les résultats obtenus aux deux étapes précédentes.6. définir vos conclusions d’audit.

Rendu attenduVotre rapport de TP3, pour cette partie, devra présenter votre audit.


PostgreSQL 10 Reference CardConnexions des clients

• -h : nom d’hôte ou adresse IP• -p : numéro de port• -U : nom du rôle• -d : nom de la base de données• chaine de connexion : host=<nom>

port=<numéro> user=<role> dbname=<nom>

• fichier de service : ~/.pg_service.conf

• fichier de service global : pg_service.conf dans lerépertoire :

◦ Redhat : /etc/sysconfig/pgsql/◦ Debian : /etc/postgresql-common/

Client psqlArguments

• -c '<sql>' : exécute une commande• -f <fichier> : exécute un fichier• -1 : exécute les ordres dans une transaction• -s : exécute les ordres du fichier enmode pas à pas

Commandes• \c [dbname [user [ host [port]]]] : ouvrirune nouvelle connexion

• \conninfo : affiche la connexion courante• \l : liste les base de données de l’instance• \dn : liste les schémas• \dt : liste les tables visibles• \dx : liste des extensions installées• \df : liste les fonctions• \d <objet> : décrit l’objet• \! : exécute une commande externe• \i <fichier> : exécute le fichier• \x on|auto|off : change le mode d’affichage• \timing on|off : change affichage des tempsd’execution

• \? : aide sur les commandes psql• \h : aide sur les ordres SQL• \q : quitter

Répertoires et fichiersCréation avec initdb

• -D : répertoire des données ($PGDATA)• -k : active les sommes de contrôle des pages• -X : répertoire des journaux de transactions• -A : mode d’authentification par défaut

chemins par défaut• Redhat : /var/lib/pgsql/10/data• Debian : /var/lib/postgresql/10/main

contenus• base : Bases de données et toutes les données• pg_wal : Journaux binaires de transactions• pg_tblspc : Liens vers les espaces de tables

Fichiers de configuration• pg_hba.conf : gestion de l’authentification• postgresql.conf : fichier principal• postgresql.auto.conf : utilisé parALTER SYSTEM

chemins par défaut• Redhat : /var/lib/pgsql/10/data• Debian : /etc/postgresql/10/main

paramètres principaux• shared_buffers :mémoirepartagéeentre les pro-cessus

• work_mem : mémoire utilisée pour les tris ou les ha-shages

• maintenance_work_mem : mémoire utilisée parVACUUM

• autovacuum_vacuum_scale_factor : ratio d’unetable pour déclencher un VACUUM

• min_wal_size / max_wal_size : déclenche unCHECKPOINT

Commandes• SET work_mem=100MB ;• ALTER SYSTEM SET work_mem = 100MB ;• SET search_path to public, <schema1>,

<schema2> ;• ALTER DATABASE <dbname> set search_path

to CURRENT ;• ALTER TABLE <table> SET ( autovacuum_-

vacuum_scale_factor = 0.1);

Journaux d’activité• Redhat : /var/lib/pgsql/10/data/pg_log• Debian : /var/log/postgresql/

Catalogue• pg_settings : Paramètres de configuration• pg_stat_activity : Sessions en cours• pg_locks : Verrous en cours• pg_stat_user_tables : Tables et statistiques

Requêtes• requêtes en cours :

select datname, pid, query_start,wait_event_type, wait_event,state, query

from pg_stat_activitywhere backend_type=’client backend’

and pid != pg_backend_pid();

• verrous en cours :select datname, a.pid, backend_start,

wait_event, state,l.relation::regclass, l.mode, l.granted

from pg_locks l join pg_stat_activity aon l.pid=a.pid;

Fonctions SQL• pg_reload_conf() : relire la configuration• pg_cancel_backend(pid) : annuler une session• pg_terminate_backend(pid) : terminer une ses-sion

• pg_create_restore_point(text) : créer unpoint d’enregistrement pour le PITR

• pg_is_in_recovery() : l’instance est standby?• pg_wal_replay_pause() : mettre la réplication enpause

• pg_wal_replay_resume() : reprendre la réplica-tion

• pg_create_physical_replication_-slot(name) : créer un slot de réplication

• pg_drop_replication_slot(name) : supprimmerun slot de réplication

Sauvegardespg_dump : Sauvegarde logique

• -F p|c|t|d :◦ plain : texte SQL◦ custom : binaire compressé◦ tar : fichier TAR◦ directory : répertoire compressé

• -j N : nombre de connexions utilisées• -f : fichier de sauvegarde• -c : ajoute les commandes DROP des objets• -C : ajoute la création de la base de données• -n, -N : inclus ou exclu le schéma indiqué• -t, -T : inclus ou exclu la table indiquée• -s : ne sauvegarde que les schémas des objets, pasles données

• -a : ne sauvegarde que les données des objets, pasles schémaspg_dump -Fc -j4 -f dbname.20171025.dmppg_dumpall -g -f globals.sql

pg_restore Restauration de sauvegarde logique• -F c|t : format de la sauvegarde• -l : extrait le catalogue• -L <fichier> : utilise le catalogue• -j N : nombre de connexions utilisées

pg_restore -l -f dbname.20171025.dmp \> catalog.txt

pg_restore -L catalog.txt -j4 \-f /backups/dbname.20171025.dump

pg_basebackup Sauvegarde physique• -F t|p : format de sauvegarde• -r <num> : débit maximum• -X fetch|stream|none : méthode de récupéra-tion desWAL

• -c fast|spread : type de CHECKPOINT• -l text : label de la sauvegarde• -R : make recovery.conf• -P : affiche la progression de la sauvegarde

pg_basebackup -Fp -r 100 -c fast \-l ’2017.pgconf.eu’ -R -P

Contrôle depuis le système• avec SystemD

systemctl restart|reload|start|stop|status \postgresql-10

• sous Debianpg_ctlcluster 10 main \

restart|reload|start|stop|status

Scripts Debian• pg_lscluster : Liste les instances existantes• pg_createcluster : Crée une instance• pg_dropcluster : Détruit une instance

m2 tiw administration des systèmes et des bases de données

Documents