La maintenance des données

• pg_dump est un client PostgreSQL permettant ainsi des connexions distantes.
• Il est possible de limiter la sauvegarde à un schéma, à une table ...
• Pour se connecter il faudra lui indiquer :
  • un nom de machine (ou adresse IP) (option -h)
  • un numéro de port (option -p),
  • un nom d'utilisateur (option -U),
  • éventuellement un mot de passe qui sera automatiquement demandé.
• Pour permettre la sauvegarde pg_dump doit disposer des droits de lecture sur l'ensemble des éléments à sauvegarder.
• Il est donc plus pratique d'effectuer les sauvegardes en administrateur de PostgreSQL.

• Les sauvegardes créées par pg_dump sont cohérentes.
• La sauvegarde représente une image de la base de données au moment où commence l'exécution de pg_dump.
• pg_dump ne bloque pas les autres opérations sur la base à l'exception de celles nécessitant un verrou exclusif.
• Dans ce cas, selon l'ordre d'arrivée :
  • soit la sauvegarde débutera dès que le verrou sera relâché,
  • soit la sauvegarde prendra le verrou, puis le relâchera une fois l'action terminée.

• pg_dump ne sauvegarde qu'une seule base de données.
• Les informations des rôles et des tablespaces portant sur le cluster ne sont pas sauvegardées.
• La commande pg_dumpall permet une sauvegarde de tout le contenu d'un cluster.
• pg_dumpall exigera d'être lancé en administrateur.
• L'option --clean permet de supprimer les bases de données avant de les régénérer.
• Exemple :

  pg_dumpall -U postgres --clean > fichier_du_cluster

• L'option --globals-only permet de ne sauvegarder que les informations globales : utilisateurs et tablespaces.

• La souplesse des commandes UNIX permet de faire :
  • des transferts directs d'un serveur vers un autre :

    pg_dump ma_database | psql -h autre_serveur la_bdd

  • de la compression à la volée :

    pg_dump ma_database | bzip2 > mon_fichier_arch.bz2

  • du découpage en petits fichiers :

    pg_dump ma_database | split -b 100m - mon_fichier

  • des archivages planifiés par mise en place de commandes dans la crontab
  • ...

• Exemple de sauvegarde de la base de données test_postgres dans le fichier test_postgres.tar au format tar :

  pg_dump --username=postgres --dbname=test_postgres\
                --verbose --format=tar --file=test_postgres.tar
  pg_dump: last built-in OID is 16383
  pg_dump: reading extensions
  pg_dump: identifying extension members
  pg_dump: reading schemas
  ...

• Exemple de restauration de la table villes du schéma geo_france de la base de données test_postgres située dans le fichier généré précédemment :

  pg_restore --username=postgres --dbname=test_postgres\
                   --table=villes --schema=geo_france\
                   --verbose test_postgres.tar
  pg_restore: connecting to database for restore
  pg_restore: creating TABLE "geo_france.villes"
  pg_restore: processing data for table "geo_france.villes"

• La commande rsync peut être utilisée pour un archivage.
• Afin de limiter le temps d'arrêt du serveur il est conseillé de procéder ainsi :
  • exécution de rsync serveur actif.
  • arrêt du serveur
  • exécution de rsync serveur arrêté.
  • réactivation du serveur.
• Une sauvegarde des fichiers de données sera généralement plus volumineuse qu'une sauvegarde SQL, mais la restauration sera plus rapide.

• Syntaxe de la commande en archivage :

  COPY {nom_table [(colonne [, ...])]|(requête)}
     TO '/chemin/vers/fichier'
     [WITH (option [, ...])]

• Syntaxe de la commande en restauration :

  COPY nom_table [(colonne [, ...])]
     FROM '/chemin/vers/fichier'
     [WITH (option [, ...])]

Les options de la commande copy

• Exemple d'exportation simple :

  COPY departements TO '/tmp/departements.txt'
     WITH DELIMITER '|';

• Exemple d'exportation partielle de table :

  COPY (SELECT id_dept,nom,cp,lat,lon,pop FROM villes
     ORDER by id_dept,nom) TO '/tmp/villes.txt';

• Exemple d'importation simple :

  COPY departements FROM '/tmp/departements.txt'
     WITH DELIMITER '|';

• Exemple d'importation organisée :

  COPY villes (id_dept, nom, cp, lat, lon, pop)
     FROM '/tmp/villes.txt';

• Il est également possible de vider partiellement une table tout en stockant les données supprimées dans un fichier CSV.
• Pour cela il faudra utiliser la commande DELETE accompagnée de la directive RETURNING liste de colonnes.
• Exemple d'exportation partielle avec suppression des lignes exportées :

  COPY (DELETE FROM villes
     WHERE nom ILIKE '%lille%' RETURNING nom,pop,lon,lat)
     TO '/tmp/villes_part.txt'
     WITH (FORMAT CSV,
           FORCE_QUOTE (nom), -- afin de tester les quotes
           HEADER true);      -- on veut la ligne d'entête

• Contenu du fichier /tmp/villes_part.txt :

  nom,pop,lon,lat
  "Lille",225784,3.0666,50.6332
  "Lillebonne",9310,0.55,49.5167
  "Lillemer",225,-1.8666,48.5666
  ...

• Pour utiliser pg_upgrade il faut :
  • interrompre le service associé au cluster à monter en version,
  • créer un cluster dans la nouvelle version par initdb,
  • appliquer la commande pg_upgrade avec les arguments suivants :
    • l'ancien répertoire des binaires : -b old_bin_directory,
    • le nouveau répertoire des binaires : -B new_bin_directory,
    • l'ancien répertoire des données : -d old_data_directory,
    • le nouveau répertoire des données : -D new_data_directory.
• Exemple :

  pg_upgrade -b /usr/pgsql/11/bin -B /usr/pgsql/12/bin \
         -d /var/lib/pg_sql/11/data -D /var/lib/pg_sql/12/data