Effectuer une récupération d'un instantané d'une base de données PostgreSQL sur Compute Engine

Concepts

Avant de commencer ce tutoriel, révisez les concepts PostgreSQL suivants :

• Archivage continu : la base de données enregistre en continu des transactions séquentielles dans un fichier.
• Journaux de transaction (journaux WAL ou "write ahead log") : les modifications apportées aux fichiers de données sont enregistrées dans les journaux de transaction avant d'être appliquées au fichier.
• Enregistrement WAL : chaque transaction appliquée à la base de données est mise en forme et stockée sous la forme d'un enregistrement WAL.
• Fichiers segments : les fichiers segments portent des noms incrémentés de manière monotone et contiennent autant d'enregistrements WAL que possible. La taille de fichier peut être configurée et présente une valeur par défaut de 16 Mio. Vous pouvez choisir une taille supérieure si vous prévoyez des transactions dont le nombre ou le volume est élevé, afin de réduire le nombre total de fichiers segments générés et de minimiser la charge de gestion des fichiers.

Pour en savoir plus, consultez la section Fiabilité et journaux de transaction.

Le schéma suivant illustre la conservation en deux étapes des journaux de transaction.

Dans le schéma précédent, la première étape des journaux de transaction persistants consiste en l'enregistrement des transactions d'écriture dans le tampon WAL par le moteur de base de données, simultanément à l'écriture dans une table. Lorsque la transaction est validée, le tampon WAL est écrit (vidé) sur le disque au cours de la deuxième étape, par le biais d'un ajout au fichier segment WAL.

Opter pour la récupération PITR

La récupération PITR convient aux scénarios suivants :

• Minimiser l'objectif de point de récupération (RPO) : Il s'agit de la durée maximale tolérée pour une perte de données avant que celle-ci n'ait un impact significatif sur les processus métier. L'enregistrement de toutes les transactions dans les journaux WAL entre deux instantanés de sauvegarde réduit considérablement la quantité de données perdues, car vous disposez ainsi de toutes les transactions réalisées depuis la dernière sauvegarde complète, que vous pouvez appliquer à la base de données.
• Minimiser l'objectif de délai de récupération (RTO) : Le RTO correspond au délai nécessaire à la récupération d'une base de données en cas d'événement destructeur. Une fois que vous avez configuré les sauvegardes binaires et l'archivage des journaux, le temps nécessaire à la récupération de la base de données peut être minimal.
• Résoudre un bug de corruption des données ou une erreur d'administration : Si une version de code entraîne une corruption catastrophique des données ou si une erreur irrécupérable est commise lors d'une opération de maintenance de routine, vous pouvez effectuer la récupération à un état antérieur à la date de l'événement.

Certaines architectures applicatives, telles qu'une architecture de microservices, peuvent reposer sur des bases de données parallèles, qui nécessitent alors des récupérations indépendantes. Par exemple, une application de vente au détail peut héberger les données client dans une base de données et les détails des commandes et informations d'inventaire dans d'autres bases de données distinctes. Suivant l'état général des données, une, deux ou la totalité des bases de données peuvent nécessiter une récupération en parallèle.

La récupération PITR n'est pas adaptée aux scénarios suivants :

• Le RPO est élevé : Si votre stratégie de reprise après sinistre peut tolérer la perte des transactions reçues après le dernier instantané, vous pouvez vous épargner des étapes supplémentaires et vous focaliser sur la réduction du délai de récupération de vos données.
• Une récupération complète de la base de données est nécessaire : Si votre objectif est de récupérer la transaction la plus récente, votre cible de récupération est l'horodatage de la dernière transaction persistante. Ce scénario est un cas spécifique de récupération PITR, mais d'un point de vue sémantique, cet objectif est désigné par le terme de récupération complète.

Considérations sur les performances

La procédure d'archivage soumet votre serveur de base de données à une charge d'E/S supplémentaire. Cette charge supplémentaire dépend des caractéristiques de votre charge de travail, car elle est proportionnelle au volume de transactions d'écriture, de mise à jour et de suppression.

Si vous souhaitez réduire l'impact potentiel de l'activité d'archivage WAL en termes d'E/S sur votre base de données principale, vous pouvez réaliser les archives WAL périodiques à l'aide d'une instance dupliquée en lecture seule.

Cette configuration isole la base de données principale des activités d'E/S par lot liées au transfert des fichiers WAL. Les transactions destinées à l'instance dupliquée en lecture seule sont transmises en un flux constant émanant de la base de données principale, ce qui réduit nettement l'impact sur le débit à l'état stable.

De plus, si votre topologie de base de données de production inclut déjà une instance dupliquée en lecture seule, cette configuration n'entraîne aucune charge supplémentaire, que ce soit en termes de gestion ou de coûts.

Architecture de référence

Le schéma suivant illustre l'architecture que vous allez mettre en œuvre dans ce tutoriel.

Dans ce tutoriel, vous allez créer une infrastructure cloud afin d'observer une récupération PITR utilisant les composants suivants :

• Un serveur de base de données PostgreSQL s'exécutant sur Compute Engine
• Cloud Storage pour le stockage des instantanés et des journaux de transactions

Le schéma suivant illustre les deux conteneurs Docker lancés sur la machine virtuelle (VM) de base de données PostgreSQL. Pour assurer la séparation des problèmes, le serveur de base de données s'exécute dans l'un des deux conteneurs et l'archiveur WAL, dans l'autre conteneur.

Ce diagramme montre comment les volumes Docker de chaque conteneur sont mappés sur des points d'installation Persistent Disk sur la VM hôte.

Configurer les variables d'environnement

Les scripts et commandes utilisés dans ce tutoriel reposent sur des variables d'environnement de shell.

1. Dans Cloud Shell, utilisez les commandes ci-dessous pour définir les variables d'environnement associées à votre projet, au nom de l'instance et à la base de données PostgreSQL de démonstration.

   export PROJECT_ID=your-gcp-project
   export PG_INSTANCE_NAME=instance-pg-pitr
   export POSTGRES_PASSWORD=PasswordIsThis
   export POSTGRES_PITR_DEMO_DBNAME=pitr_demo
   

   Remplacez l'élément suivant :

   • your-gcp-project : nom du projet que vous avez créé pour ce tutoriel
   • PasswordIsThis : mot de passe sécurisé de la base de données PostgreSQL

2. Définissez la variable d'environnement correspondant à la zone Google Cloud. Remplacez choose-an-appropriate-zone par une zone Google Cloud.

   export ZONE=choose-an-appropriate-zone
   export REGION=${ZONE%-[a-z]}
   

3. Définissez la variable d'environnement correspondant au sous-réseau VPC (Virtual Private Cloud) par défaut pour la région associée à votre zone :

   export SUBNETWORK_URI=$(gcloud compute networks subnets \
       describe default --format=json --region=$REGION | \
       jq --raw-output '.ipCidrRange')
   

4. Définissez la variable d'environnement associée au bucket Cloud Storage. Remplacez archive-bucket par le nom unique du bucket Cloud Storage devant héberger les journaux WAL.

   export ARCHIVE_BUCKET=archive-bucket
   

Créer un bucket Cloud Storage

• Créez un bucket Cloud Storage servant à archiver les fichiers WAL à partir de la base de données PostgreSQL :

  gcloud storage buckets create gs://${ARCHIVE_BUCKET}
  

Autoriser l'accès aux instances dotées d'adresses IP privées

Pour les instances utilisées dans ce tutoriel, comme dans de nombreux cas d'utilisation de production, il n'est pas nécessaire que les instances de VM obtiennent des adresses IP publiques. Cependant, les instances ont besoin d'avoir accès à l'Internet public pour en extraire les exemples d'images de conteneurs, et vous-même devez pouvoir vous y connecter à l'aide d'une interface système sécurisée. Vous configurez une passerelle NAT (Network Address Translation) ainsi qu'un proxy IAP (Identity-Aware Proxy) pour le transfert TCP.

Créer une passerelle NAT

Comme les instances de VM que vous créez n'ont pas d'adresses IP publiques, vous devez créer une passerelle NAT afin qu'elles puissent extraire des images de conteneurs à partir de Docker Hub.

1. Dans Cloud Shell, créez un routeur Cloud :

   export CLOUD_ROUTER_NAME=${PROJECT_ID}-nat-router
   gloud compute routers create $CLOUD_ROUTER_NAME \
       --network=default --region=$REGION
   

2. Créez la passerelle NAT :

   gcloud compute routers nats create ${PROJECT_ID}-nat-gateway \
       --region=$REGION \
       --router=$CLOUD_ROUTER_NAME \
       --auto-allocate-nat-external-ips \
       --nat-all-subnet-ip-ranges
   

Configurer IAP pour le transfert TCP

IAP contrôle l'accès à vos applications cloud et à vos VM exécutées sur Google Cloud. IAP vérifie l'identité de l'utilisateur et le contexte de la requête pour déterminer si cet utilisateur est autorisé à accéder à une VM donnée.

1. Dans Cloud Shell, autorisez le trafic émanant du bloc réseau de transfert TCP vers les instances de votre projet :

   export IAP_FORWARDING_CIDR=35.235.240.0/20
   gcloud compute --project=$PROJECT_ID firewall-rules create \
       cloud-iap-tcp-forwarding --direction=INGRESS  \
       --priority=1000 --network=default \
       --action=ALLOW --rules=all  \
       --source-ranges=$IAP_FORWARDING_CIDR
   

2. Pour vous connecter à l'aide d'un tunnel de transfert TCP, ajoutez une liaison de stratégie de gestion de l'authentification et des accès (IAM). Remplacez your-email-address par l'adresse e-mail que vous utilisez pour vous connecter à la console Google Cloud.

   export GRANT_EMAIL_ADDRESS=your-email-address
   gcloud projects add-iam-policy-binding $PROJECT_ID \
      --member=user:$GRANT_EMAIL_ADDRESS \
      --role=roles/iap.tunnelResourceAccessor
   

Créer l'infrastructure de base de données PostgreSQL

1. Dans Cloud Shell, clonez le dépôt source contenant les scripts de configuration puis, dans l'interface système, déplacez le contexte vers le dépôt local :

   git clone https://github.com/GoogleCloudPlatform/gcs-postgresql-recovery-tutorial
   cd gcs-postgresql-recovery-tutorial
   

2. Pour créer et configurer l'instance de VM de base de données, exécutez le script suivant :

   cd bin
   ./create_postgres_instance.sh
   

   Pour ce tutoriel, ce script démarre une instance de VM située dans la zone choisie, et dotée du système d'exploitation optimisé pour les conteneurs et de deux nouveaux disques persistants associés. Dans ce contexte, vous pouvez ignorer le message d'avertissement renvoyé par l'API au sujet des performances d'E/S médiocres et dû au fait que les scripts créent des disques persistants de petite taille.

Examiner la configuration de cloud-init

Cloud-init est un package multidistribution qui initialise une instance cloud.

Examinez l'exemple de code cloud-init suivant :

write_files:
- path: /var/tmp/docker-entrypoint-initdb.d/init-pitr-demo-db.sql
  permissions: 0644
  owner: root
  content: |
    CREATE DATABASE ${POSTGRES_PITR_DEMO_DBNAME};

    \c ${POSTGRES_PITR_DEMO_DBNAME}

    CREATE SCHEMA pitr_db_schema;

    CREATE TABLE pitr_db_schema.customer
       (id SERIAL NOT NULL,
        name VARCHAR(255),
        create_timestamp TIMESTAMP DEFAULT current_timestamp,
        PRIMARY KEY (id));

    CREATE TABLE pitr_db_schema.invoice
       (id SERIAL NOT NULL,
        customer_id INTEGER
          REFERENCES pitr_db_schema.customer(id),
        description VARCHAR(1000),
        create_timestamp TIMESTAMP DEFAULT current_timestamp,
        PRIMARY KEY (customer_id, id));

- path: /etc/systemd/system/postgres.service
  permissions: 0644
  owner: root
  content: |
    [Unit]
    Requires=docker.service
    After=docker.service
    Description=postgres docker container

    [Service]
    TimeoutStartSec=0
    KillMode=none
    Restart=always
    RestartSec=5s
    ExecStartPre=-/usr/bin/docker kill postgres-db
    ExecStartPre=-/usr/bin/docker rm -v postgres-db
    ExecStart=/usr/bin/docker run -u postgres --name postgres-db \
                                  -v /var/tmp/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d \
                                  -v /mnt/disks/data:/var/lib/postgresql/data \
                                  -v /mnt/disks/wal:/var/lib/postgresql/wal \
                                  -e PGDATA=/var/lib/postgresql/data/pgdata \
                                  -e POSTGRES_PASSWORD=${POSTGRES_PASSWORD} \
                                  -e POSTGRES_INITDB_WALDIR=/var/lib/postgresql/wal/pg_wal \
                                  -p ${POSTGRES_PORT}:${POSTGRES_PORT} \
                               postgres:11-alpine
    ExecStop=-/usr/bin/docker stop postgres-db
    ExecStopPost=-/usr/bin/docker rm postgres-db

- path: /etc/systemd/system/wal_archive.service
  permissions: 0644
  owner: root
  content: |
    [Unit]
    Requires=docker.service postgres.service
    After=docker.service postgres.service
    Description=WAL archive docker container

    [Service]
    TimeoutStartSec=10min
    Type=oneshot
    ExecStart=/usr/bin/docker run --name wal-archive \
                                  -v /mnt/disks/wal/pg_wal_archive:/mnt/wal_archive \
                               google/cloud-sdk:slim gsutil mv /mnt/wal_archive/[0-9A-F]*[0-9A-F] gs://${ARCHIVE_BUCKET}
    ExecStopPost=-/usr/bin/docker rm wal-archive

- path: /etc/systemd/system/wal_archive.timer
  permissions: 0644
  owner: root
  content: |
    [Unit]
    Description=Archive WAL to GCS (every 5 minutes)

    [Timer]
    OnBootSec=5min
    OnUnitInactiveSec=5min
    OnUnitActiveSec=5min

    [Install]
    WantedBy=timers.target

Dans le cadre de ce tutoriel, cloud-init est utilisé pour effectuer les opérations suivantes :

1. Créer deux périphériques de stockage de blocs à base de disques persistants
2. Créer les systèmes de fichiers sur ces deux périphériques, un pour les données et un autre pour les journaux d'archive
3. Installer les périphériques sur des points d'installation logiques de l'instance de VM qui sont partagés avec les conteneurs Docker
4. Créer, puis lancer un service systemd (postgres.service), qui démarre un conteneur Docker PostgreSQL comportant les éléments suivants :
   • Les disques persistants installés en tant que volumes
   • Le port PostgreSQL (5432) publié sur la VM hôte
5. Créer un fichier /var/tmp/docker-entrypoint-initdb.d/init-pitr-demo-db.sql afin de générer un ensemble simple de tables dans une base de données et un schéma de démonstration
6. Créer, puis lancer un deuxième service systemd (wal_archive.service) qui exécute un conteneur Docker Google Cloud CLI avec les disques WAL installés en tant que volume (Ce service sauvegarde les fichiers WAL archivés dans Cloud Storage.)
7. Créer, activer, puis démarrer un minuteur systemd (wal_archive.timer) qui exécute régulièrement le service wal_archive.service
8. Vérifier que le port PostgreSQL (5432) est ouvert pour le sous-réseau VPC afin que le générateur de transactions puisse atteindre le port de la base de données

Modifier la configuration de l'instance de base de données

Le serveur de base de données est en cours d'exécution, mais vous devez configurer l'accès au réseau et démarrer la procédure d'archivage WAL.

Se connecter à l'instance de VM de base de données

1. Dans Google Cloud Console, accédez à la page Instances de VM.

   Accéder à la page "Instances de VM"

2. Pour ouvrir une interface système de terminal, cliquez sur l'élément SSH qui figure à côté de l'instance instance-pg-pitr que vous avez créée.

3. Dans l'interface système du terminal, vérifiez que le conteneur Docker a bien démarré : docker ps

   Le résultat ressemble à ce qui suit :

   CONTAINER ID   IMAGE                COMMAND                  CREATED              STATUS              PORTS   NAMES
   8bb65d8c1197   postgres:11-alpine   "docker-entrypoint.s…"   About a minute ago   Up About a minute           postgres-db
   

   Si le conteneur n'est pas encore en cours d'exécution, patientez quelques instants, puis utilisez la même commande pour vérifier à nouveau.

Autoriser les connexions réseau entrantes vers la base de données

1. Dans l'interface système du terminal de l'instance instance-pg-pitr, ouvrez le fichier de configuration de l'authentification basée sur l'hôte pour PostgreSQL afin d'en modifier le contenu :

   sudoedit /mnt/disks/data/pgdata/pg_hba.conf
   

2. Pour supprimer l'accès par défaut à la base de données (accordé à toute adresse IP), ajoutez la ligne suivante à la fin du fichier, en veillant à ajouter le caractère # en début de ligne. La ligne figurant dans le fichier se présente comme suit :

   #host all all all md5
   

3. Pour autoriser les connexions protégées par un mot de passe émanant des hôtes du bloc CIDR 10.0.0.0/8, ajoutez la ligne suivante à la fin du fichier :

   host    all             all             10.0.0.0/8               md5
   

   Cette entrée active la connectivité à partir du sous-réseau VPC où sera ultérieurement créé le générateur de transactions.

4. Enregistrez le fichier, puis fermez-le.

Configurer l'archivage WAL

1. Dans l'interface système du terminal de l'instance instance-pg-pitr, modifiez le fichier postgresql.conf :

   sudoedit /mnt/disks/data/pgdata/postgresql.conf
   

2. Remplacez les lignes archive_mode, archive_command et archive_timeout existantes, qui sont commentées, par les lignes suivantes :

   archive_mode=on
   archive_command = '( ARCHIVE_PATH=/var/lib/postgresql/wal/pg_wal_archive;
   test ! -f $ARCHIVE_PATH/%f && cp %p $ARCHIVE_PATH/%f.cp && mv
   $ARCHIVE_PATH/%f.cp $ARCHIVE_PATH/%f ) '
   archive_timeout = 120
   

   Une fois que vous avez remplacé les lignes du fichier modifié, celui-ci ressemble à l'extrait de code suivant :

   
   .... illustrative snippet start ....
   
   # - Archiving -
   archive_mode=on
   archive_command = '( ARCHIVE_PATH=/var/lib/postgresql/wal/pg_wal_archive;  test ! -f $ARCHIVE_PATH/%f && cp %p $ARCHIVE_PATH/%f.cp && mv $ARCHIVE_PATH/%f.cp $ARCHIVE_PATH/%f ) '
   archive_timeout = 120
   #------------------------------------------------------------------------------
   # REPLICATION
   #------------------------------------------------------------------------------
   
   .... illustrative snippet end ....
   
   

3. Enregistrez le fichier, puis fermez-le.

Appliquer les modifications de configuration et les vérifier

1. Dans l'interface système du terminal de l'instance instance-pg-pitr, redémarrez le conteneur pour appliquer les modifications :

   sudo systemctl restart postgres
   

2. Vérifiez l'existence de fichiers segments WAL :

   sudo ls -l /mnt/disks/wal/pg_wal
   

   Le résultat ressemble à ce qui suit :

   total 16388
   -rw------- 1 postgres 70 16777216 Sep  5 23:07 000000010000000000000001
   drwx------ 2 postgres 70     4096 Sep  5 23:05 archive_status
   

3. Vérifiez la connectivité réseau vers la base de données :

   export LOCAL_IP=127.0.0.1
   docker exec postgres-db psql -w --host=$LOCAL_IP \
         --command='SELECT 1'
   

   Le résultat ressemble à ce qui suit :

   ?column?
   ----------
          1
   (1 row)
   

4. Fermez la connexion SSH à l'instance.

Démarrer le générateur de transactions afin de remplir la base de données

Les étapes ci-dessous démarrent un programme Go qui génère des transactions pour ce tutoriel. Le programme s'exécute dans un conteneur hébergé sur une instance de VM.

L'image du conteneur est créée et prête à l'emploi. Elle est hébergée dans un projet associé à un registre Container Registry public.

1. Dans Cloud Shell, accédez au répertoire du générateur de transactions :

   cd ~/gcs-postgresql-recovery-tutorial/bin
   

2. Définissez les variables d'environnement :

   export TRANS_GEN_INSTANCE_NAME=instance-trans-gen
   export POSTGRES_HOST_IP=$(gcloud compute instances describe  \
       --format=json --zone=${ZONE} ${PG_INSTANCE_NAME} | \
       jq --raw-output '.networkInterfaces[0].networkIP')
   

3. Pour exécuter le générateur de transactions, démarrez l'instance :

   ./run_trans_gen_instance.sh
   

   Ignorez le message d'avertissement concernant le risque de performances d'E/S médiocres.

4. Attendez quelques instants, puis vérifiez que des transactions atteignent la base de données PostgreSQL :

   gcloud compute ssh $PG_INSTANCE_NAME \
      --tunnel-through-iap \
      --zone=$ZONE \
      --command="docker exec postgres-db psql \
      --dbname=$POSTGRES_PITR_DEMO_DBNAME \
      --command='SELECT COUNT(*) FROM pitr_db_schema.customer;'"
   

   La sortie contient un nombre supérieur à 0 dès que des enregistrements sont insérés dans la base de données par le générateur de transactions :

    count
   -------
      413
   (1 row)
   

Configurer le planning de sauvegarde des instantanés binaires

Vous pouvez sauvegarder les disques persistants selon un planning donné et conserver ces sauvegardes pendant une durée définie dans la stratégie de ressources.

Créer la programmation des instantanés

1. Dans Cloud Shell, définissez les variables d'environnement suivantes :

   export ZONE=zone-of-your-instance
   export SNAPSHOT_SCHEDULE_NAME=pg-disk-schedule
   export REGION=${ZONE%-[a-z]}
   export SNAPSHOT_WINDOW_START=$(TZ=":GMT" date "+%H:00")
   export SNAPSHOT_RETENTION_DAYS=2
   export SNAPSHOT_FREQUENCY_HOURS=1
   

   Remplacez zone-of-your-instance par la zone Google Cloud dans laquelle vous avez précédemment démarré la VM de base de données.

2. Créez la programmation d'instantanés :

   gcloud compute resource-policies create snapshot-schedule \
       $SNAPSHOT_SCHEDULE_NAME \
       --region=$REGION \
       --max-retention-days=$SNAPSHOT_RETENTION_DAYS \
       --on-source-disk-delete=apply-retention-policy \
       --hourly-schedule=$SNAPSHOT_FREQUENCY_HOURS \
       --start-time=$SNAPSHOT_WINDOW_START \
       --storage-location=$REGION
   

Associer la programmation d'instantanés aux disques

Lorsque vous avez exécuté le script de création d'instance, le volume de données et le volume WAL ont été créés sous la forme de deux disques persistants indépendants. Pour créer des instantanés de disques persistants selon un programme défini, vous devez associer une stratégie de ressources à chaque disque persistant. Dans le cadre de ce tutoriel, vous souhaitez que les instantanés de disque aient lieu simultanément. Vous devez donc utiliser la même stratégie pour les deux disques persistants associés à la VM Compute Engine.

1. Dans Cloud Shell, définissez les variables d'environnement suivantes :

   export SNAPSHOT_SCHEDULE_NAME=pgdata-disk-schedule
   export PG_INSTANCE_NAME=instance-pg-pitr
   export ZONE=zone-of-your-instance
   

2. Associez la stratégie de programmation au disque persistant dédié aux données :

   gcloud beta compute disks add-resource-policies ${PG_INSTANCE_NAME}-data \
       --resource-policies $SNAPSHOT_SCHEDULE_NAME \
       --zone $ZONE
   

3. Associez la stratégie de programmation au disque persistant dédié aux journaux WAL :

   gcloud beta compute disks add-resource-policies ${PG_INSTANCE_NAME}-wal \
       --resource-policies $SNAPSHOT_SCHEDULE_NAME \
       --zone $ZONE
   

Exécuter manuellement un instantané

(Facultatif) Les instantanés programmés se produisent au cours de la fenêtre de programmation. Il est donc peu probable qu'un instantané soit pris immédiatement après la création du programme. Si vous ne souhaitez pas attendre l'exécution programmée, vous pouvez exécuter manuellement l'instantané initial.

1. Dans Cloud Shell, définissez les variables d'environnement suivantes :

   export ZONE=zone-of-your-instance
   export PG_INSTANCE_NAME=instance-pg-pitr
   export REGION=${ZONE%-[a-z]}
   

2. Créez un instantané des deux disques persistants de l'instance PostgreSQL :

   gcloud compute disks snapshot \
       ${PG_INSTANCE_NAME}-data ${PG_INSTANCE_NAME}-wal \
       --snapshot-names=${PG_INSTANCE_NAME}-data-`date+%s`,${PG_INSTANCE_NAME}-wal-`date +%s` \
       --zone=$ZONE --storage-location=$REGION
   

3. Affichez les instantanés que vous venez de créer :

   gcloud compute snapshots list
   

   Le résultat ressemble à ce qui suit :

   NAME                              DISK_SIZE_GB  SRC_DISK                                   STATUS
   instance-pg-pitr-data-1578339767  200           us-central1-f/disks/instance-pg-pitr-data  READY
   instance-pg-pitr-wal-1578339767   100           us-central1-f/disks/instance-pg-pitr-wal   READY
   

Effectuer une récupération PITR

Souvent, une récupération PITR est utilisée pour récupérer les données perdues en raison d'une erreur opérationnelle ou programmatique.

Dans cette section du tutoriel, vous allez mettre à jour une base de données pour simuler un incident de perte de données. Vous simulerez ensuite une réponse de type panique, avant de lancer une récupération au moment précis où la commande erronée a été exécutée.

S'assurer qu'une récupération PITR est possible

Avant d'effectuer une récupération PITR, vous devez laisser suffisamment de temps s'écouler pour que les éléments suivants s'exécutent :

• Les sauvegardes binaires (instantanés de disque)
• L'archivage WAL

Pour ce tutoriel, la valeur de archive_timeout a été définie sur une durée anormalement faible de 120 secondes afin de forcer une rotation fréquente des fichiers WAL. Vous devez également attendre qu'un instantané de disque programmé au moins ait été exécuté ; une autre solution consiste à créer manuellement un instantané du disque.

1. Vérifiez qu'un instantané au moins a été exécuté :

   1. Dans la console Google Cloud, accédez à la page Instantanés.

      Accéder à la page Instantanés

   2. Vérifiez qu'il existe au moins deux instantanés : un pour le volume de données et un autre pour le volume WAL, par exemple, instance-pg-pitr--us-central1-a-20190805023535-i3hpw7kn.

2. Vérifiez que les fichiers segments sont archivés dans Cloud Storage :

   1. Dans la console Google Cloud, accédez à la page du Navigateur Cloud Storage.

      Accéder à la page du navigateur Cloud Storage

   2. Cliquez sur archive-bucket.

      

Endommager les données

Pour simuler une perte de données, ouvrez une interface de ligne de commande vers la base de données PostgreSQL et endommagez les données figurant dans la table remplie par le générateur de transactions.

1. Dans Google Cloud Console, accédez à la page Instances de VM.

   Accéder à la page "Instances de VM"

2. Cliquez sur l'élément SSH correspondant à l'instance instance-pg-pitr.

3. Dans le terminal SSH, exécutez l'interface PostgreSQL de type terminal dans le conteneur Docker :

   docker exec -it postgres-db psql --dbname=pitr_demo
   

4. Envoyez dans l'interface système PostgreSQL une instruction LMD SQL visant à modifier une ligne de la table des clients et comportant une faute de frappe intentionnelle :

   UPDATE pitr_db_schema.customer
   SET name = 'New Name for customer id=1';
   WHERE id = 1;
   

   Le résultat ressemble à ce qui suit :

   UPDATE 999
   pitr_demo=#  WHERE id = 1;
   ERROR:  syntax error at or near "WHERE"
   LINE 1: WHERE id = 1;
           ^
    

   Dans cet exemple, l'erreur est due à un point-virgule superflu inséré avant la clause WHERE. Toutes les lignes de la base de données ont été mises à jour. Vous pouvez maintenant effectuer une récupération PITR afin de restaurer les lignes modifiées par l'instruction erronée.

Déterminer l'heure cible de la récupération

La première étape d'une récupération PITR consiste à déterminer l'heure cible de la récupération. Cette heure est déterminée grâce à un examen de vos données visant à identifier un point légèrement antérieur à l'événement ayant causé les dégâts.

1. Dans l'interface système du terminal de l'instance instance-pg-pitr, obtenez l'horodatage maximal des lignes endommagées :

   SELECT MAX(create_timestamp)::timestamptz
     FROM pitr_db_schema.customer
   WHERE name = 'New Name for customer id=1';
   

   Le résultat ressemble à ce qui suit :

                max              .
   -------------------------------
   2019-08-05 18:14:58.226511+00
   (1 row)
   

   Dans une base de données de production, la requête permettant de déterminer la cible de récupération est plus complexe. C'est plus particulièrement le cas lorsque la table concernée est volumineuse et que la colonne indicative n'est pas indexée.

2. Copiez le résultat : vous utiliserez la valeur renvoyée par cette requête à l'étape suivante.

Récupérer la base de données

Dans le cadre de ce tutoriel, un script de récupération automatise la récupération PITR. Nous vous recommandons de mettre en place une procédure automatisée de récupération de la base de données et de la tester régulièrement.

1. Dans Cloud Shell, déplacez le répertoire de travail courant vers l'emplacement du script de récupération :

   cd ~/gcs-postgresql-recovery-tutorial/bin
   

2. Définissez les variables d'environnement requises par le script. Remplacez YYYY-MM-DD HH:MM:SS.999999+00 par le résultat de la requête, que vous avez copié précédemment.

   export PROJECT_ID=$(gcloud config get-value project)
   export PG_INSTANCE_NAME=instance-pg-pitr
   export POSTGRES_PASSWORD=PasswordIsThis
   export PG_INSTANCE_NAME=instance-pg-pitr
   export RECOVER_BUCKET=archive-bucket
   export PIT_RECOVERY_TARGET="YYYY-MM-DD HH:MM:SS.999999+00"
   export ZONE=zone-of-your-instance
   

3. Exécutez le script de récupération :

   ./recover_to_point_in_time.sh
   

Comprendre le script de récupération

Cette section fournit des détails sur les paramètres d'entrée du script et les étapes qui le constituent.

Le script nécessite la définition des variables d'environnement suivantes :

• PIT_RECOVERY_TARGET : heure cible pour la récupération
• PROJECT_ID : projet contenant l'instance PG_INSTANCE_NAME
• ZONE : zone où se trouve l'instance PG_INSTANCE_NAME
• PG_INSTANCE_NAME : instance sur laquelle s'exécute l'instance PostgreSQL production
• RECOVER_BUCKET : bucket Cloud Storage où sont archivés les fichiers segments WAL
• POSTGRES_PASSWORD : mot de passe pour l'utilisateur de la base de données PostgreSQL

Le script exécute les tâches suivantes :

1. Il détermine les instantanés de disque les plus récents en fonction de la date et de l'heure de la cible de récupération.

2. Il crée un fichier cloud-init.yaml transmis à une VM de stockage optimisée pour les conteneurs, qui exécute la base de données de récupération à un moment précis. Le fichier cloud-init.yaml crée les fichiers de configuration et exécute un certain nombre de commandes système afin d'établir l'environnement suivant :

   • Un conteneur gcsfuse qui installe en tant que volume le bucket d'archivage des fichiers segments WAL, qui est ensuite mis à la disposition de l'hôte à travers un montage lié Docker.

   • Un conteneur postgres-db dans lequel le moteur de base de données exécute les éléments suivants :

     • Le système de fichiers hôte auquel les disques persistants sont associés en tant que volumes.
     • Le système de fichiers hôte auquel le bucket Cloud Storage est associé en tant que volume.

   • Un fichier de récupération recovery.conf hébergé dans le répertoire de données PostgreSQL et comportant les informations suivantes :

     • La date cible.
     • La commande restore : commande de copie paramétrée, utilisée par la base de données pour copier les fichiers segments WAL requis à partir du système de fichiers d'archive. %f est le fichier segment et %p correspond au chemin utilisé par la base de données pour le traitement des fichiers lors de la récupération.

   • Les paramètres archive_ sont commentés dans le fichier de paramètres postgresql.conf afin d'éviter de corrompre le répertoire d'archives WAL.

3. Il démarre l'instance de récupération PITR avec les informations suivantes :

   • Un nom créé en combinant la variable d'environnement $PG_INSTANCE_NAME avec les valeurs alphanumériques de la variable d'environnement $PIT_RECOVERY_TARGET.
   • Des disques persistants créés à partir des instantanés de disque identifiés précédemment.

Voici un exemple de fichier recovery.conf :

restore_command = '(test -d /var/lib/postgresql/wal/pg_wal_recover && cp /var/lib/postgresql/wal/pg_wal_recover/%f %p ) '
recovery_target_time='YYYY-MM-DD HH:MM:SS UTC'
recovery_target_inclusive=true

Valider la récupération

1. Dans Google Cloud Console, accédez à la page Instances de VM.

   Accéder à la page "Instances de VM"

2. Cliquez sur l'élément SSH correspondant à l'instance instance-pg-pitr-YYYYMMDDHHMMSS.

3. Dans le terminal SSH, exécutez l'interface PostgreSQL de type terminal dans le conteneur Docker :

   docker exec -it postgres-db psql --dbname=pitr_demo
   

   Si vous obtenez l'erreur suivante, attendez quelques instants que le conteneur PostgreSQL démarre, puis exécutez à nouveau la commande :

   Error: No such container: postgres-db
   

4. Vérifiez les données figurant dans la table des clients :

   SELECT * FROM pitr_db_schema.customer
   WHERE id > (SELECT MAX(id)-10 FROM pitr_db_schema.customer);
   

   Le résultat ressemble à ce qui suit :

      id  |           name            |      create_timestamp
   ------+---------------------------+----------------------------
     711 | customer_name_from_golang | 2019-12-06 18:03:51.229444
     712 | customer_name_from_golang | 2019-12-06 18:03:52.531755
     713 | customer_name_from_golang | 2019-12-06 18:03:53.555441
     714 | customer_name_from_golang | 2019-12-06 18:03:54.581872
     715 | customer_name_from_golang | 2019-12-06 18:03:55.607459
     716 | customer_name_from_golang | 2019-12-06 18:03:56.633362
     717 | customer_name_from_golang | 2019-12-06 18:03:57.658523
     718 | customer_name_from_golang | 2019-12-06 18:03:58.685469
     719 | customer_name_from_golang | 2019-12-06 18:03:59.706939
   

   La colonne des noms présente une valeur créée par le générateur de transactions. L'horodatage de la dernière ligne est antérieur à la cible de récupération (que vous avez fournie au script de récupération dans une variable d'environnement). Suivant le nombre d'enregistrements à récupérer, vous devrez peut-être patienter quelques instants pour que la totalité des lignes soient mises à jour.