PostgreSQL

Débuter

Utiliser l'utilisateur postgres pour faire les manipulation :

su - postgres

Connexion

Pour se connecter à une base, il faut 2 étape : se connecter à l'invite de commande postgreSQL puis se connecter à la base elle même :

$ psql dbName # connexion à l'invite postgreSQL, puis connexion à la base (ci-dessous)
postgres=# \c dbName

Remarques : les lignes commençant par > ou $ indiquent des commandes à exécuter dans le shell en tant que simple utilisateur. Celles commençants par # sont à exécuter en tant que super-utilisateur (root).

Remarques : les lignes commençant par postgres=# indiquent des commandes à exécuter dans l'invite PostgreSQL (après avoir fait psql dbName).

Important :

De façon à pouvoir scripter les sauvegardes, il est nécessaire de modifier les configurations de postgreSQL, ceci est documenté dans la documentation Pleade.

Installation pour Pleade

Une fois connecté à l'utilisateur postgre, on créera les utilisateurs nécessaire et les bases liées.

Création de l'utilisateur

On créé deux utilisateurs :

• $projet-prod : un pour l'application de « production » ;
• $projet-recette et un pour l'application de « recettage » ;

createuser -P $projet-prod
createuser -P $projet-recette

On saisi le mot de passe et on donne le droit de créé des bases :

Saisir le mot de passe pour le nouveau rôle :
Le saisir de nouveau :
Le nouveau rôle est-il super-utilisateur ? (o/n) n
Le nouveau rôle est-il autorisé à créer des bases de données ? (o/n) o
Le nouveau rôle est-il autorisé à créer de nouveaux rôles ? (o/n) n

Création de la base

En relations à nos utilisateurs, on créé une base pour l'application de « production » et une pour le « recettage ».

createdb -O $projet-prod db-prod
createdb -O $projet-recette db-recette

Notice :

Il n'est pas strictement nécessaire d'avoir cette configuration, mais il sera plus simple de débugger sur une application qui n'est pas en production mais qui a les mêmes configuration.

Configuration Pleade

Paramètres principaux : pleade-admin.properties

Une fois ces étapes terminé il faudra configurer Pleade de façon à ce l'application utilise la base PostgreSQL et non HSQL (par défaut). Pour cela référez-vous à la documentation paramètres Pleade, sections paramètre principaux et aux variables pleade.sdx-database, pleade.sdx-ead-repository, pleade.sdx-eac-repository.

Paramètres Base de donnée : cocoon.xconf

<jdbc name="db-conf-prod" logger="sdx.rdbms.pleade3">
  <pool-controller min="5" max="100" />
  <dburl>jdbc:postgresql://localhost/db-prod</dburl>
  <!-- INFO (MP) : JDBC URL pour MySQL. La difficulté est de résoudre le
  problème des pertes de connexion lorsque l'application montre une
  inactivité de plus de 8 heures (valeur par défaut dans MySQL). Ici, on la
  règle à 24 heures.
  <dburl>jdbc:mysql://localhost/pleade3?autoReconnectForPools=true&amp;sessionVariables=wait_timeout=86400;</dburl>-->
  <user>ville-dole-prod</user>
  <password>pg:/vd/pr0d</password>
</jdbc>

• <dburl> indique l'URL vers la base de données, le suffixe est donc le nom de celle-ci
• <user> et <password> indique respectivement l'utilisateur et le mot de passe postgreSQL

Commandes utiles

Lister toutes les bases

Remarques : possible limitation liée à l'utilisateur exécutant la commande.

Depuis un environnement shell :

psql -l

Depuis un environnement PostgreSQL :

postgres=# \l

Lister les tables

Depuis un environnement PostgreSQL :

\c dbName
\d

Supprimer toutes les tables

Important :

Cette opération est irréversible ! Vérifier que vous disposez de sauvegardes avant de vider les tables.

DROP TABLE module_eadeac_eac, module_eadeac_eac_docs;
DROP TABLE module_eadeac_ead;
DROP TABLE module_eadeac_ead_docs;
DROP TABLE module_eadeac_ead_oai;
DROP TABLE module_eadeac_mets, module_eadeac_mets_docs;

Notice :

L'opération est assez rapide. Au dela d'une dizaine de minutes c'est que quelque chose pose problème. Si en relançant la commande le problème persiste, vous pouvez tenter :

1. d'arrêter la commande et tenter de supprimer les tables une à une;
2. re-démarrer le serveur PostgreSQL et relancer la commande.

Étrangement ça marche.

Type de données bytea sous forme XML

Voir les données de type bytea de la table « module_eadeac_ead_docs » sous forme de texte XML :

SELECT encode(data, 'escape') AS txt
FROM module_eadeac_ead_docs
WHERE id = 'frajlsm-pleade-test-01-fr_images-attachees';

Taille des Bases

Liste toutes les bases et affiche leur taille en Kb, Mb ou Gb.<ref name="size">How to find the PostgreSQL database size</ref>

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

Taille d'une table

Pour avoir la taille d'une table <ref>15 Advanced PostgreSQL Commands with Examples</ref> <ref name="size">How to find the PostgreSQL database size</ref>

SELECT pg_size_pretty(pg_relation_size('xyz'));

Export et Import

Notice :

Pour plus d'informations on consultera :

• les procédures de restauration dans la section Doc/Remonter une sauvegarde ;
• la tâche redmine: Import d'un dump de l'appli de recette dans celle de prod ;
• Gestion base de données postgreSQL

On travaille depuis un shell (bash, zsh).

Exporter

De façon basique, on fera :

pg_dump dbname > dump-"$date".sql

Pour travailler proprement, on on compressera l'export avec gzip et on utlisera pg_dump pour l'export (respectivement pg_restore pour l'import) :

pg_dump --blobs --clean -U "$dbUser" "$dbName" | gzip > dump-"$date".sql.gz

Importer

psql dbname < dump.sql

Pour travailler proprement, on utlisera pg_restore et gunzip pour importer la table compressé :

gunzip -c dump.sql.gz | psql "$dbName" -U "$dbUser"

Remarques : pour plus d'informations, on consultera la page de la documentation officielle sur SQL dump.