Como executar uma recuperação pontual (PITR) de um banco de dados do PostgreSQL no Compute Engine

Conceitos

Antes de iniciar o tutorial, revise os seguintes conceitos do PostgreSQL:

• Arquivamento contínuo. Quando o banco de dados salva continuamente as transações sequenciais em um arquivo.
• Registro gravado com antecedência (WAL). As alterações nos arquivos de dados são registradas no WAL antes de serem feitas no arquivo.
• Registro WAL. Cada transação aplicada ao banco de dados é formatada e armazenada como um registro WAL.
• Segmentar arquivos. Os arquivos de segmento têm nomes de arquivo monotonicamente crescentes e contêm o maior número possível de registros WAL. O tamanho do arquivo é configurável, com um padrão de 16 MiB. Caso esteja esperando um grande volume de transações em número ou tamanho, é possível escolher um tamanho maior e, dessa forma, diminuir o número agregado de arquivos de segmento gerados e a carga de gerenciamento de arquivos.

Para mais informações, consulte Confiança e o registro gravado com antecedência.

O diagrama a seguir mostra como os WALs são persistente em dois estágios.

No diagrama anterior, o primeiro estágio dos WALs persistentes consiste no mecanismo de banco de dados que grava transações de gravação no buffer do WAL simultaneamente com a gravação em uma tabela. Quando a transação é confirmada, o buffer do WAL é gravado (liberado) no disco durante o segundo estágio com um acréscimo ao arquivo de segmento do WAL.

Como escolher uma PITR

Uma PITR é apropriada nos seguintes cenários:

• Minimizar o objetivo do ponto de recuperação (RPO, na sigla em inglês). O RPO é o tempo máximo de perda de dados que é tolerado antes de afetar significativamente os processos empresariais. Salvar todas as transações nos WALs entre snapshots de backup diminui drasticamente a quantidade de dados perdidos porque você tem as transações desde o último backup completo que serão aplicadas ao banco de dados.
• Minimizar o objetivo do tempo de recuperação (RTO, na sigla em inglês). O RTO é o tempo necessário para recuperar um banco de dados, caso ocorra um evento destrutivo. Depois de configurar backups binários e o arquivamento de registros, o tempo necessário para recuperar o banco de dados pode ser mínimo.
• Corrigir um bug em dados corrompidos ou um erro administrativo. Se uma versão de código causar uma corrupção de dados catastrófica ou um erro irrecuperável for cometido durante uma manutenção de rotina, é possível realizar uma recuperação para um momento anterior.

Em algumas arquiteturas de aplicativos, como uma arquitetura de microsserviços, pode haver bancos de dados paralelos que podem exigir recuperações independentes. Por exemplo, um aplicativo de varejo pode ter dados de clientes em um banco de dados e detalhes de pedidos de varejo e informações de inventário em outros bancos de dados. Dependendo do estado geral dos dados, um, dois ou todos os bancos de dados precisam ser recuperados paralelamente.

Uma PITR não é apropriada nestes cenários:

• O RPO é grande. Se a política de recuperação de desastres puder tolerar a perda de transações recebidas após o snapshot recente, evite etapas adicionais e concentre-se na redução do tempo na recuperação dos dados.
• É necessária uma recuperação completa do banco de dados. Se a meta for recuperar para a transação mais recente, a meta de recuperação será o carimbo de data/hora da última transação persistente. Esse cenário é um caso específico de PITR, mas semanticamente essa meta é chamada de recuperação completa.

Considerações sobre desempenho

O processo de arquivamento coloca uma carga de E/S adicional no servidor de banco de dados. A carga adicional depende das características da carga de trabalho, porque é proporcional ao volume de transações de gravação, atualização e exclusão.

Se você quiser reduzir o impacto de E/S que a atividade de arquivamento do WAL pode incorrer no banco de dados primário, execute os arquivos periódicos do WAL usando uma réplica somente leitura.

Essa configuração isola o banco de dados primário das atividades de E/S orientadas a lote relacionadas à transferência dos arquivos WAL. As transações destinadas à réplica somente leitura são transmitidas em um fluxo constante do banco de dados primário, exigindo um impacto muito menor na capacidade de estado estável.

Além disso, se a topologia do banco de dados de produção já incluir uma réplica somente leitura, essa configuração não sobrecarregará o gerenciamento, o preço ou o outros elementos.

Arquitetura de referência

O diagrama a seguir ilustra a arquitetura implementada neste tutorial.

Neste tutorial, é possível criar uma infraestrutura de nuvem para observar uma PITR que usa estes componentes:

• Um servidor de banco de dados do PostgreSQL em execução no Compute Engine.
• Cloud Storage para armazenamento de snapshots e registros de transações.

O diagrama a seguir mostra os dois contêineres do Docker iniciados na máquina virtual (VM) do banco de dados do PostgreSQL. Como uma separação de preocupações, o servidor de banco de dados está sendo executado em um dos contêineres, e o arquivador WAL está sendo executado no outro.

Este diagrama mostra como os volumes do Docker em cada contêiner são mapeados para os pontos de montagem do Persistent Disk na VM do host.

Como configurar variáveis de ambiente

Os scripts e comandos usados neste tutorial dependem de variáveis do ambiente shell.

1. No Cloud Shell, defina variáveis de ambiente para o projeto, o nome da instância e o banco de dados do PostgreSQL de demonstração.

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

   Substitua:

   • your-gcp-project: o nome do projeto criado para este tutorial.
   • PasswordIsThis: uma senha segura para o banco de dados do PostgreSQL.

2. Defina a variável de ambiente para a zona do Google Cloud. Substitua choose-an-appropriate-zone por uma zona do Google Cloud.

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

3. Defina a variável de ambiente para a sub-rede padrão da nuvem privada virtual (VPC) para a região da sua zona:

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

4. Defina a variável de ambiente para o bucket do Cloud Storage. Substitua archive-bucket por um nome exclusivo para o bucket do Cloud Storage em que os WALs são salvos.

   export ARCHIVE_BUCKET=archive-bucket
   

Crie um bucket do Cloud Storage

• Crie um bucket do Cloud Storage para arquivar os arquivos WAL do banco de dados do PostgreSQL:

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

Como permitir o acesso a instâncias de endereços IP privados

Para as instâncias usadas neste tutorial, como em muitos casos de uso de produção, as instâncias de VM não precisam acessar endereços IP públicos. No entanto, as instâncias exigem acesso à Internet pública para extrair as imagens de contêiner de exemplo e você precisa de acesso para se conectar usando um shell seguro. É possível configurar um gateway de conversão de endereços de rede (NAT, na sigla em inglês) e o Identity-Aware Proxy (IAP) para encaminhamento de TCP.

Criar um gateway NAT

Como as instâncias de VM criadas não têm endereços IP públicos, crie um gateway NAT para que as instâncias possam extrair imagens de contêiner do Docker Hub.

1. No Cloud Shell, crie um Cloud Router:

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

2. Crie o gateway 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
   

Configurar o IAP para encaminhamento de TCP

O IAP controla o acesso aos seus aplicativos de nuvem e VMs em execução no Google Cloud. O IAP verifica a identidade e o contexto da solicitação para determinar se um usuário tem permissão para acessar uma VM.

1. No Cloud Shell, permita o tráfego do bloco da rede de encaminhamento de TCP para as instâncias no projeto:

   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. Para se conectar usando um túnel de encaminhamento de TCP, adicione uma vinculação de política de gerenciamento de identidade e acesso (IAM). Substitua your-email-address pelo endereço de e-mail usado para fazer login no console do 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
   

Como criar a infraestrutura de banco de dados do PostgreSQL

1. No Cloud Shell, clone o repositório de origem que contém os scripts de configuração e altere o contexto do shell para o repositório local:

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

2. Para criar e configurar a instância de VM do banco de dados, execute o seguinte script:

   cd bin
   ./create_postgres_instance.sh
   

   Neste tutorial, esse script inicia uma instância de VM na zona escolhida com o sistema operacional otimizado para contêiner e dois novos discos permanentes anexados. Nesse caso, ignore a mensagem de aviso retornada pela API sobre o baixo desempenho de E/S porque os scripts criam pequenos Persistent Disk.

Como analisar a configuração do cloud-init

O Cloud-init é um pacote de distribuição múltipla que inicializa uma instância de nuvem.

Analise o mesmo exemplo de código cloud-init a seguir:

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

Neste tutorial, o cloud-init é usado para:

1. Criar dois dispositivos de armazenamento em blocos no Persistent Disk.
2. Criar os sistemas de arquivos nos dois dispositivos: um para os dados e outro para os registros de arquivamento.
3. Monte os dispositivos em pontos de montagem lógicos na instância de VM que são compartilhados com os contêineres do Docker.
4. Crie e inicie um serviço systemd (postgres.service), que inicia um contêiner do Docker do PostgreSQL com o seguinte:
   • Os discos permanentes montados como volumes.
   • A porta do PostgreSQL (5432) publicada no host da VM.
5. Crie um arquivo /var/tmp/docker-entrypoint-initdb.d/init-pitr-demo-db.sql para criar um conjunto simples de tabelas em um banco de dados e esquema de demonstração.
6. Crie e inicie um segundo serviço systemd (wal_archive.service) que executa um contêiner do Docker da Google Cloud CLI com os discos WAL montados como volume. Esse serviço faz backup de arquivos WAL arquivados no Cloud Storage.
7. Crie, ative e inicie um timer systemd (wal_archive.timer) que executa periodicamente o wal_archive.service.
8. Verifique se a porta do PostgreSQL (5432) está aberta para a sub-rede VPC para que o gerador de transações possa alcançar a porta do banco de dados.

Modificar a configuração da instância do banco de dados

O servidor do banco de dados está em execução, mas é necessário configurar o acesso à rede e iniciar o processo de arquivamento do WAL.

Conectar-se à instância de VM do banco de dados

1. No Console do Google Cloud, acesse a página Instâncias de VMs.

   Acessar a instância de VM

2. Para abrir um shell de terminal, ao lado da instância instance-pg-pitr criada, clique em SSH.

3. No shell do terminal, verifique se o contêiner do Docker foi iniciado: docker ps

   A saída será assim:

   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
   

   Se o contêiner ainda não estiver em execução, aguarde um momento e use o mesmo comando para realizar a verificação novamente.

Permitir conexões de rede de entrada com o banco de dados

1. No shell do terminal da instância instance-pg-pitr, abra o arquivo de configuração de autenticação baseado em um host do PostgreSQL para edição:

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

2. Para remover o acesso o padrão de todos os endereços IP ao banco de dados, comente a linha a seguir do final do arquivo adicionando # no início da linha. A linha no arquivo é semelhante a esta:

   #host all all all md5
   

3. Para permitir conexões protegidas por senha de hosts no bloco CIDR 10.0.0.0/8, adicione a seguinte linha no final do arquivo:

   host    all             all             10.0.0.0/8               md5
   

   Essa entrada ativa a conectividade da sub-rede VPC em que o gerador de transações é criado posteriormente.

4. Salve e feche o arquivo.

Configurar o arquivamento WAL

1. No shell do terminal da instância instance-pg-pitr, edite o arquivo postgresql.conf:

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

2. Substitua as linhas archive_mode, archive_command e archive_timeout comentadas pelo seguinte:

   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
   

   Ao substituir as linhas no arquivo modificado, ele se parece com o seguinte snippet de código:

   
   .... 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. Salve e feche o arquivo.

Aplicar e verificar as alterações de configuração

1. No shell do terminal da instância instance-pg-pitr, reinicie o contêiner para aplicar as alterações:

   sudo systemctl restart postgres
   

2. Verifique os arquivos de segmento do WAL:

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

   A saída será assim:

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

3. Verifique a conectividade de rede com o banco de dados:

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

   A saída será assim:

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

4. Feche a conexão SSH com a instância.

Como iniciar o gerador de transações para preencher o banco de dados

As etapas a seguir iniciam um programa Go que gera transações para este tutorial. O programa é executado dentro de um contêiner em uma instância de VM.

A imagem do contêiner já foi criada e hospedada em um projeto com um Container Registry público.

1. No Cloud Shell, mude para o diretório do gerador de transações:

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

2. Defina as variáveis de ambiente:

   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. Para executar o gerador de transações, inicie a instância:

   ./run_trans_gen_instance.sh
   

   Ignore a mensagem de aviso sobre o baixo desempenho de E/S.

4. Aguarde alguns instantes e verifique se as transações estão chegando ao banco de dados do 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;'"
   

   A saída contém uma contagem maior do que 0 quando os registros são adicionados ao banco de dados pelo gerador de transações:

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

Como configurar a programação de backup de snapshot binário

É possível fazer o backup de discos permanentes de acordo com uma programação e retê-los por um período definido na política de recursos.

Criar a programação de snapshot

1. No Cloud Shell, defina as variáveis de ambiente:

   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
   

   Substitua zone-of-your-instance pela zona do Google Cloud em que você iniciou a VM do banco de dados.

2. Crie a programação de snapshot:

   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
   

Anexar a programação de snapshot aos discos

Quando você executou o script para criar uma instância, os volumes de dados e WAL foram criados como dois discos permanentes independentes. Para criar snapshots de discos permanentes de acordo com uma programação definida, associe uma política de recursos a cada disco permanente. Nesse caso, o objetivo é que os snapshots do disco ocorram simultaneamente. Portanto, use a mesma política para os dois discos permanentes anexados à VM do Compute Engine.

1. No Cloud Shell, defina as variáveis de ambiente:

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

2. Anexe a política de programação ao disco de dados permanentes:

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

3. Anexe a política de programação ao disco WAL permanente:

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

Executar um snapshot manualmente

(Opcional) Os snapshots programados ocorrem na janela de programação. Portanto, é improvável que um snapshot seja tirado imediatamente ao criar a programação. Se você não quiser esperar pelo snapshot programado, poderá executar o snapshot inicial manualmente.

1. No Cloud Shell, defina as variáveis de ambiente:

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

2. Crie um snapshot dos dois discos permanentes da instância do 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. Veja os snapshots que você criou:

   gcloud compute snapshots list
   

   A saída será assim:

   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
   

Como executar uma PITR

Uma PITR geralmente é realizada para recuperar dados perdidos devido a um erro operacional ou programático.

Nesta seção do tutorial, você executará uma atualização do banco de dados para simular uma perda dados catastrófica. Em seguida, simulará uma resposta para uma situação de pânico, antes de iniciar a recuperação para o momento anterior à emissão do comando incorreto.

Verifique se é possível executar uma PITR

Antes de executar uma PITR, você precisa permitir que haja tempo suficiente para que os seguintes elementos sejam executados:

• Os backups binários (snapshots do disco)
• Arquivamento de WAL

Neste tutorial, o archive_timeout foi definido como 120 segundos atipicamente baixos para forçar a rotação frequente de arquivos WAL. Também é necessário aguardar até que pelo menos um snapshot de disco programado seja executado ou você precise fazer o snapshot do disco manualmente.

1. Verifique se pelo menos um snapshot foi capturado:

   1. No console do Google Cloud, acesse a página Snapshots.

      Acessar a página "Snapshots"

   2. Verifique se há pelo menos dois snapshots: um para o volume de dados e outro para o volume do WAL, por exemplo, instance-pg-pitr--us-central1-a-20190805023535-i3hpw7kn.

2. Verifique se os arquivos de segmento estão arquivados no Cloud Storage:

   1. No console do Google Cloud, acesse a página Navegador do Cloud Storage.

      Acessar a página "Navegador do Cloud Storage"

   2. Clique em archive-bucket.

      

Prejudicar os dados

Para simular uma perda de dados catastrófica, abra um shell de linha de comando no banco de dados do PostgreSQL e danifique os dados na tabela preenchida pelo gerador de transações.

1. No Console do Google Cloud, acesse a página Instâncias de VMs.

   Acessar a página "Instâncias de VM"

2. Para a instância instance-pg-pitr, clique em SSH.

3. No terminal SSH, execute o front-end baseado no terminal do PostgreSQL no contêiner do Docker:

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

4. Para modificar uma linha na tabela do cliente, envie uma instrução DML SQL com um erro de digitação intencional no shell do PostgreSQL:

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

   A saída será assim:

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

   O erro foi gerado porque um ponto e vírgula extra foi inserido antes da cláusula WHERE. Todas as linhas no banco de dados foram atualizadas. Agora é possível fazer uma PITR para recuperar as linhas que a instrução incorreta modificou.

Determine o tempo desejado para a recuperação

A primeira etapa em uma PITR é determinar o tempo desejado para a recuperação. Esse tempo é determinado examinando os dados para identificar um ponto um pouco antes do evento que causa danos aos dados.

1. No shell do terminal da instância instance-pg-pitr, consiga o carimbo de data/hora máximo das linhas danificadas:

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

   A saída será assim:

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

   Em um banco de dados de produção, a consulta para determinar o destino de recuperação é mais complexa, especialmente nos casos em que a tabela afetada é grande e a coluna indicativa não é indexada.

2. Copie o resultado. você usará o valor retornado por essa consulta na próxima etapa.

Recuperar o banco de dados

Neste tutorial, um script de recuperação automatiza a PITR. Recomendamos que você tenha um processo automatizado para recuperar o banco de dados e que teste esse processo periodicamente.

1. No Cloud Shell, altere o diretório de trabalho atual para o local do script de recuperação:

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

2. Defina as variáveis de ambiente necessárias para o script. Substitua YYYY-MM-DD HH:MM:SS.999999+00 pela saída da consulta que você copiou anteriormente.

   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. Execute o script de recuperação:

   ./recover_to_point_in_time.sh
   

Entenda o script de recuperação

Esta seção fornece alguns detalhes sobre os parâmetros de entrada e as etapas seguidas pelo script.

O script exige que as seguintes variáveis de ambiente sejam definidas:

• PIT_RECOVERY_TARGET: o tempo de recuperação solicitado.
• PROJECT_ID: o projeto em que a instância PG_INSTANCE_NAME está localizada.
• ZONE: a zona em que a instância PG_INSTANCE_NAME está localizada.
• PG_INSTANCE_NAME: a instância em que a instância do PostgreSQL de produção está em execução.
• RECOVER_BUCKET: o bucket do Cloud Storage em que os arquivos de segmento WAL são arquivados.
• POSTGRES_PASSWORD: a senha usada para o usuário do banco de dados do PostgreSQL.

O script executa as seguintes etapas:

1. Determina os snapshots de disco mais recentes com base na data e hora de recuperação solicitadas.

2. Cria um arquivo cloud-init.yaml fornecido para uma VM de armazenamento otimizada para contêineres que executa o banco de dados da PITR. O arquivo cloud-init.yaml cria arquivos de configuração e executa vários comandos do sistema para estabelecer este ambiente:

   • Um contêiner gcsfuse que monta o bucket do repositório de arquivos do segmento WAL como um volume que é exposto ao host com uma montagem de vinculação do Docker.

   • Um contêiner postgres-db em que o mecanismo de banco de dados executa o seguinte:

     • O sistema de arquivos host em que os discos permanentes estão anexados como volumes.
     • O sistema de arquivos do host em que o bucket do Cloud Storage está anexado como um volume.

   • Um arquivo de recuperação recovery.conf no diretório de dados do PostgreSQL com as seguintes informações:

     • A data desejada.
     • O comando restore: um comando de cópia parametrizado que o banco de dados usa para copiar arquivos de segmento WAL necessários para o repositório do sistema de arquivos. %f é o arquivo de segmento e %p é o caminho usado pelo banco de dados para processar arquivos durante a recuperação.

   • As configurações archive_ são comentadas no arquivo de configurações postgresql.conf para evitar o diretório de arquivos WAL seja corrompido.

3. Inicia a instância da PITR com as seguintes informações:

   • Um nome criado por meio da combinação da variável de ambiente $PG_INSTANCE_NAME e dos valores alfanuméricos da variável de ambiente $PIT_RECOVERY_TARGET.
   • Discos permanentes criados a partir dos snapshots de disco identificados anteriormente.

A seguir, um exemplo de arquivo 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

Validar a recuperação

1. No Console do Google Cloud, acesse a página Instâncias de VMs.

   Acessar a página "Instâncias de VM"

2. Para a instância instance-pg-pitr-YYYYMMDDHHMMSS, clique em SSH.

3. Nos terminais SSH, execute o front-end baseado no terminal do PostgreSQL no contêiner do Docker:

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

   Se você receber o seguinte erro, aguarde um momento até que o contêiner do PostgreSQL seja iniciado e execute o comando novamente:

   Error: No such container: postgres-db
   

4. Verifique os dados na tabela do cliente:

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

   A saída será assim:

      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
   

   O nome mostra o valor criado pelo gerador de transações. A linha final tem um carimbo de data/hora anterior ao destino de recuperação (que você forneceu ao script de recuperação em uma variável de ambiente). Dependendo do número de registros que você precisa recuperar, talvez seja necessário aguardar alguns instantes para que todas as linhas sejam atualizadas.