Recuperar dados com a recuperação pontual (PITR, na sigla em inglês)

Nesta página, descrevemos como usar a recuperação pontual (PITR, na sigla em inglês) para reter e recuperar dados no Spanner.

Para saber mais, consulte Recuperação pontual.

Pré-requisitos

Este guia usa o banco de dados e o esquema definidos no Guia de início rápido do Cloud Spanner. Você pode executar o guia de início rápido para criar o banco de dados e o esquema ou modificar os comandos para uso com seu próprio banco de dados.

Defina o período de armazenamento

Para definir o período de armazenamento do banco de dados:

Console

  1. Acesse a página "Instâncias do Spanner" no console do Google Cloud.

    Acessar a página "Instâncias do Spanner"

  2. Clique na instância que contém o banco de dados para abrir a página Visão geral correspondente.

  3. Clique no banco de dados para abrir a página Visão geral correspondente.

  4. Selecione a guia Backup/Restaurar.

  5. Clique no ícone de lápis no campo Período de armazenamento da versão.

  6. Digite uma quantidade e uma unidade de tempo para o período de armazenamento e clique em Atualizar.

gcloud

Atualize o esquema do banco de dados com a instrução ALTER DATABASE. Exemplo:

gcloud spanner databases ddl update example-db --instance=test-instance \
    --ddl='ALTER DATABASE `example-db` SET OPTIONS (version_retention_period="7d");'

Para visualizar o período de armazenamento, localize a DDL do seu banco de dados:

gcloud spanner databases ddl describe example-db --instance=test-instance

Esta é a saída:

ALTER DATABASE example-db SET OPTIONS (
  version_retention_period = '7d'
);
...

Bibliotecas de cliente

C#


using Google.Cloud.Spanner.Data;
using System.Threading.Tasks;

public class CreateDatabaseWithRetentionPeriodAsyncSample
{
    public async Task CreateDatabaseWithRetentionPeriodAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}";

        using var connection = new SpannerConnection(connectionString);
        var versionRetentionPeriod = "7d";
        var createStatement = $"CREATE DATABASE `{databaseId}`";
        var alterStatement = @$"ALTER DATABASE `{databaseId}` SET OPTIONS
                   (version_retention_period = '{versionRetentionPeriod}')";
        // The retention period cannot be set as part of the CREATE DATABASE statement,
        // but can be set using an ALTER DATABASE statement directly after database creation.
        using var createDbCommand = connection.CreateDdlCommand(
            createStatement,
            alterStatement
        );
        await createDbCommand.ExecuteNonQueryAsync();
    }
}

C++

void CreateDatabaseWithVersionRetentionPeriod(
    google::cloud::spanner_admin::DatabaseAdminClient client,
    std::string const& project_id, std::string const& instance_id,
    std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements("ALTER DATABASE `" + database.database_id() +
                               "` " +
                               "SET OPTIONS (version_retention_period='2h')");
  request.add_extra_statements(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX),
          FullName   STRING(2049)
              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED
      ) PRIMARY KEY (SingerId))""");
  request.add_extra_statements(R"""(
      CREATE TABLE Albums (
          SingerId     INT64 NOT NULL,
          AlbumId      INT64 NOT NULL,
          AlbumTitle   STRING(MAX)
      ) PRIMARY KEY (SingerId, AlbumId),
          INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created.\n";

  auto ddl = client.GetDatabaseDdl(db->name());
  if (!ddl) throw std::move(ddl).status();
  std::cout << "Database DDL is:\n" << ddl->DebugString();
}

Go

import (
	"context"
	"fmt"
	"io"
	"regexp"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func createDatabaseWithRetentionPeriod(ctx context.Context, w io.Writer, db string) error {
	matches := regexp.MustCompile("^(.+)/databases/(.+)$").FindStringSubmatch(db)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("createDatabaseWithRetentionPeriod: invalid database id %q", db)
	}

	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return fmt.Errorf("createDatabaseWithRetentionPeriod.NewDatabaseAdminClient: %w", err)
	}
	defer adminClient.Close()

	// Create a database with a version retention period of 7 days.
	retentionPeriod := "7d"
	alterDatabase := fmt.Sprintf(
		"ALTER DATABASE `%s` SET OPTIONS (version_retention_period = '%s')",
		matches[2], retentionPeriod,
	)
	req := adminpb.CreateDatabaseRequest{
		Parent:          matches[1],
		CreateStatement: "CREATE DATABASE `" + matches[2] + "`",
		ExtraStatements: []string{
			`CREATE TABLE Singers (
				SingerId   INT64 NOT NULL,
				FirstName  STRING(1024),
				LastName   STRING(1024),
				SingerInfo BYTES(MAX)
			) PRIMARY KEY (SingerId)`,
			`CREATE TABLE Albums (
				SingerId     INT64 NOT NULL,
				AlbumId      INT64 NOT NULL,
				AlbumTitle   STRING(MAX)
			) PRIMARY KEY (SingerId, AlbumId),
			INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,
			alterDatabase,
		},
	}
	op, err := adminClient.CreateDatabase(ctx, &req)
	if err != nil {
		return fmt.Errorf("createDatabaseWithRetentionPeriod.CreateDatabase: %w", err)
	}
	if _, err := op.Wait(ctx); err != nil {
		return fmt.Errorf("createDatabaseWithRetentionPeriod.Wait: %w", err)
	}
	fmt.Fprintf(w, "Created database [%s] with version retention period %q\n", db, retentionPeriod)
	return nil
}

Java


import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.Database;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerException;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.spanner.admin.database.v1.CreateDatabaseMetadata;
import java.util.Arrays;
import java.util.concurrent.ExecutionException;

public class CreateDatabaseWithVersionRetentionPeriodSample {

  static void createDatabaseWithVersionRetentionPeriod() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";
    String versionRetentionPeriod = "7d";

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
      DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
      createDatabaseWithVersionRetentionPeriod(adminClient, instanceId, databaseId,
          versionRetentionPeriod);
    }
  }

  static void createDatabaseWithVersionRetentionPeriod(DatabaseAdminClient adminClient,
      String instanceId, String databaseId, String versionRetentionPeriod) {
    OperationFuture<Database, CreateDatabaseMetadata> op =
        adminClient.createDatabase(
            instanceId,
            databaseId,
            Arrays.asList(
                "CREATE TABLE Singers ("
                    + "  SingerId   INT64 NOT NULL,"
                    + "  FirstName  STRING(1024),"
                    + "  LastName   STRING(1024),"
                    + "  SingerInfo BYTES(MAX)"
                    + ") PRIMARY KEY (SingerId)",
                "CREATE TABLE Albums ("
                    + "  SingerId     INT64 NOT NULL,"
                    + "  AlbumId      INT64 NOT NULL,"
                    + "  AlbumTitle   STRING(MAX)"
                    + ") PRIMARY KEY (SingerId, AlbumId),"
                    + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE",
                "ALTER DATABASE " + "`" + databaseId + "`"
                    + " SET OPTIONS ( version_retention_period = '" + versionRetentionPeriod + "' )"
            ));
    try {
      Database database = op.get();
      System.out.println("Created database [" + database.getId() + "]");
      System.out.println("\tVersion retention period: " + database.getVersionRetentionPeriod());
      System.out.println("\tEarliest version time: " + database.getEarliestVersionTime());
    } catch (ExecutionException e) {
      // If the operation failed during execution, expose the cause.
      throw (SpannerException) e.getCause();
    } catch (InterruptedException e) {
      // Throw when a thread is waiting, sleeping, or otherwise occupied,
      // and the thread is interrupted, either before or during the activity.
      throw SpannerExceptionFactory.propagateInterrupt(e);
    }
  }
}

Nó

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  // Create a new database with an extra statement which will alter the
  // database after creation to set the version retention period.
  console.log(`Creating database ${instance.formattedName_}.`);
  const versionRetentionStatement = `
    ALTER DATABASE \`${databaseId}\`
    SET OPTIONS (version_retention_period = '1d')`;
  const [, operation] = await database.create({
    extraStatements: [versionRetentionStatement],
  });

  console.log(`Waiting for operation on ${database.id} to complete...`);
  await operation.promise();
  console.log(`
      Created database ${databaseId} with version retention period.`);

  const [data] = await database.get();
  console.log(
    `Version retention period: ${data.metadata.versionRetentionPeriod}`
  );
  const earliestVersionTime = Spanner.timestamp(
    data.metadata.earliestVersionTime
  );
  console.log(`Earliest version time: ${earliestVersionTime}`);
} catch (err) {
  console.error('ERROR:', err);
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Creates a database with data retention for Point In Time Restore.
 * Example:
 * ```
 * create_database_with_version_retention_period($instanceId, $databaseId, $retentionPeriod);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $retentionPeriod The data retention period for the database.
 */
function create_database_with_version_retention_period(string $instanceId, string $databaseId, string $retentionPeriod): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);

    if (!$instance->exists()) {
        throw new \LogicException("Instance $instanceId does not exist");
    }

    $operation = $instance->createDatabase($databaseId, ['statements' => [
        'CREATE TABLE Singers (
            SingerId     INT64 NOT NULL,
            FirstName    STRING(1024),
            LastName     STRING(1024),
            SingerInfo   BYTES(MAX)
        ) PRIMARY KEY (SingerId)',
        'CREATE TABLE Albums (
            SingerId     INT64 NOT NULL,
            AlbumId      INT64 NOT NULL,
            AlbumTitle   STRING(MAX)
        ) PRIMARY KEY (SingerId, AlbumId),
        INTERLEAVE IN PARENT Singers ON DELETE CASCADE',
        "ALTER DATABASE `$databaseId` SET OPTIONS (
        version_retention_period = '$retentionPeriod')"
    ]]);

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    $database = $instance->database($databaseId);
    $databaseInfo = $database->info();

    printf('Database %s created with version retention period %s and earliest version time %s' . PHP_EOL,
        $databaseId, $databaseInfo['versionRetentionPeriod'], $databaseInfo['earliestVersionTime']);
}

Python

def create_database_with_version_retention_period(
    instance_id, database_id, retention_period
):
    """Creates a database with a version retention period."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    ddl_statements = [
        "CREATE TABLE Singers ("
        + "  SingerId   INT64 NOT NULL,"
        + "  FirstName  STRING(1024),"
        + "  LastName   STRING(1024),"
        + "  SingerInfo BYTES(MAX)"
        + ") PRIMARY KEY (SingerId)",
        "CREATE TABLE Albums ("
        + "  SingerId     INT64 NOT NULL,"
        + "  AlbumId      INT64 NOT NULL,"
        + "  AlbumTitle   STRING(MAX)"
        + ") PRIMARY KEY (SingerId, AlbumId),"
        + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE",
        "ALTER DATABASE `{}`"
        " SET OPTIONS (version_retention_period = '{}')".format(
            database_id, retention_period
        ),
    ]
    db = instance.database(database_id, ddl_statements)
    operation = db.create()

    operation.result(30)

    db.reload()

    print(
        "Database {} created with version retention period {} and earliest version time {}".format(
            db.database_id, db.version_retention_period, db.earliest_version_time
        )
    )

    db.drop()

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

instance_path = database_admin_client.instance_path project: project_id, instance: instance_id

version_retention_period = "7d"

db_path = database_admin_client.database_path project: project_id,
                                              instance: instance_id,
                                              database: database_id

job = database_admin_client.create_database parent: instance_path,
                                            create_statement: "CREATE DATABASE `#{database_id}`",
                                            extra_statements: [
                                              "CREATE TABLE Singers (
    SingerId     INT64 NOT NULL,
    FirstName    STRING(1024),
    LastName     STRING(1024),
    SingerInfo   BYTES(MAX)
  ) PRIMARY KEY (SingerId)",

                                              "CREATE TABLE Albums (
    SingerId     INT64 NOT NULL,
    AlbumId      INT64 NOT NULL,
    AlbumTitle   STRING(MAX)
  ) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE",

                                              "ALTER DATABASE `#{database_id}`
    SET OPTIONS ( version_retention_period = '#{version_retention_period}' )"
                                            ]

puts "Waiting for create database operation to complete"

job.wait_until_done!
database = database_admin_client.get_database name: db_path

puts "Created database #{database_id} on instance #{instance_id}"
puts "\tVersion retention period: #{database.version_retention_period}"
puts "\tEarliest version time: #{database.earliest_version_time}"

Observações de uso:

  • O período de armazenamento precisa ser de 1 hora a 7 dias e pode ser especificado em dias, horas, minutos ou segundos. Por exemplo, os valores 1d, 24h, 1440m e 86400s são equivalentes.
  • Se você tiver ativado a geração de registros da API Cloud Spanner no projeto, o evento será registrado como UpdateDatabaseDdl e ficará visível no Explorador de registros.
  • Se quiser reverter para o período de armazenamento padrão de uma hora, defina a opção version_retention_period como NULL para bancos de dados GoogleSQL ou DEFAULT para bancos de dados PostgreSQL.
  • Quando você estende o período de armazenamento, o sistema não preenche versões anteriores de dados. Por exemplo, se você estender o período de armazenamento de 1h para 24h, será preciso aguardar 23h para que o sistema acumule dados antigos antes de recuperar dados das 24h anteriores.

Conseguir o período de armazenamento e o horário da versão mais antiga

O recurso Banco de dados inclui dois campos:

  • version_retention_period: o período em que o Cloud Spanner retém todas as versões de dados para o banco de dados.

  • earliest_version_time: o carimbo de data/hora mais antigo em que as versões mais antigas dos dados podem ser lidas no banco de dados. Esse valor é atualizado continuamente pelo Cloud Spanner e fica desatualizado quando é consultado. Se você estiver usando esse valor para recuperar dados, não deixe de considerar o momento entre o momento em que o valor é consultado e o momento em que você inicia a recuperação.

Console

  1. Acesse a página "Instâncias do Spanner" no console do Google Cloud.

    Acessar a página "Instâncias do Spanner"

  2. Clique na instância que contém o banco de dados para abrir a página Visão geral correspondente.

  3. Clique no banco de dados para abrir a página Visão geral correspondente.

  4. Selecione a guia Backup/Restaurar para abrir a página Backup/Restaurar e exibir o período de armazenamento.

  5. Clique em Criar para abrir a página Criar um backup e exibir o horário da versão mais antiga.

gcloud

Para receber esses campos, chame descrever bancos de dados ou listar bancos de dados. Exemplo:

gcloud spanner databases describe example-db --instance=test-instance

Esta é a saída:

createTime: '2020-09-07T16:56:08.285140Z'
earliestVersionTime: '2020-10-07T16:56:08.285140Z'
name: projects/my-project/instances/test-instance/databases/example-db
state: READY
versionRetentionPeriod: 3d

Recuperar uma parte do banco de dados

  1. Execute uma leitura desatualizada e especifique o carimbo de data/hora de recuperação desejado. Verifique se o carimbo de data/hora especificado é mais recente que o earliest_version_time. do banco de dados

    gcloud

    Use o execute-sql. Exemplo:

    gcloud spanner databases execute-sql example-db --instance=test-instance
    --read-timestamp=2020-09-11T10:19:36.010459-07:00 --sql='SELECT * FROM SINGERS'

    Bibliotecas de cliente

    Consulte Executar leitura desatualizada.

  2. Armazenar os resultados da consulta. Isso é necessário porque não é possível gravar os resultados da consulta no banco de dados na mesma transação. Para pequenas quantidades de dados, você pode imprimir no console ou armazenar na memória. Para volumes maiores de dados, pode ser necessário gravar em um arquivo local.

  3. Grave os dados recuperados novamente na tabela que precisa ser recuperada. Exemplo:

    gcloud

    gcloud spanner rows update --instance=test-instance
    --database=example-db --table=Singers --data=SingerId=1,FirstName='Marc'

    Para mais informações, consulte Como atualizar dados usando o gcloud.

    Bibliotecas de cliente

    Consulte Como atualizar dados usando DML ou Como atualizar dados com mutações.

    Opcionalmente, se você quiser fazer uma análise dos dados recuperados antes de gravar, crie manualmente uma tabela temporária no mesmo banco de dados, grave os dados recuperados nessa tabela temporária primeiro, faça a análise e, em seguida, leia os dados que você deseja recuperar da tabela temporária e grave-os na tabela que precisa ser recuperada.

Recuperar um banco de dados inteiro

É possível recuperar todo o banco de dados usando as opções Backup e restauração ou Importar e exportar e especificando um carimbo de data/hora de recuperação.

Backup e restauração

  1. Crie um backup e defina o version_time para o carimbo de data/hora de recuperação desejado.

    Console

    1. Acesse a página Detalhes do banco de dados no console do Cloud.

      Acessar o Console do Cloud

    2. Na guia Backup/Restaurar, clique em Criar.

    3. Marque a caixa Criar backup de um momento mais antigo.

    gcloud

    gcloud spanner backups create example-db-backup-1 --instance=test-instance \
  --database=example-db --retention-period=1y --version-time=2021-01-22T01:10:35Z --async

    Para mais informações, consulte Criar um backup usando o gcloud.

    Bibliotecas de cliente

    C#

    // Copyright 2020 Google Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using Google.LongRunning;
using Google.Protobuf.WellKnownTypes;
using System;

public class CreateBackupSample
{
    public Backup CreateBackup(string projectId, string instanceId, string databaseId, string backupId, DateTime versionTime)
    {
        // Create the DatabaseAdminClient instance.
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        // Initialize request parameters.
        Backup backup = new Backup
        {
            DatabaseAsDatabaseName = DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId),
            ExpireTime = DateTime.UtcNow.AddDays(14).ToTimestamp(),
            VersionTime = versionTime.ToTimestamp(),
        };
        InstanceName instanceName = InstanceName.FromProjectInstance(projectId, instanceId);

        // Make the CreateBackup request.
        Operation<Backup, CreateBackupMetadata> response = databaseAdminClient.CreateBackup(instanceName, backup, backupId);

        Console.WriteLine("Waiting for the operation to finish.");

        // Poll until the returned long-running operation is complete.
        Operation<Backup, CreateBackupMetadata> completedResponse = response.PollUntilCompleted();

        if (completedResponse.IsFaulted)
        {
            Console.WriteLine($"Error while creating backup: {completedResponse.Exception}");
            throw completedResponse.Exception;
        }

        Console.WriteLine($"Backup created successfully.");

        // GetBackup to get more information about the created backup.
        BackupName backupName = BackupName.FromProjectInstanceBackup(projectId, instanceId, backupId);
        backup = databaseAdminClient.GetBackup(backupName);
        Console.WriteLine($"Backup {backup.Name} of size {backup.SizeBytes} bytes " +
                      $"was created at {backup.CreateTime} from {backup.Database} " +
                      $"and is in state {backup.State} " +
                      $"and has version time {backup.VersionTime.ToDateTime()}");
        return backup;
    }
}

    C++

    void CreateBackup(google::cloud::spanner_admin::DatabaseAdminClient client,
                  std::string const& project_id, std::string const& instance_id,
                  std::string const& database_id, std::string const& backup_id,
                  google::cloud::spanner::Timestamp expire_time,
                  google::cloud::spanner::Timestamp version_time) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::spanner::admin::database::v1::CreateBackupRequest request;
  request.set_parent(database.instance().FullName());
  request.set_backup_id(backup_id);
  request.mutable_backup()->set_database(database.FullName());
  *request.mutable_backup()->mutable_expire_time() =
      expire_time.get<google::protobuf::Timestamp>().value();
  *request.mutable_backup()->mutable_version_time() =
      version_time.get<google::protobuf::Timestamp>().value();
  auto backup = client.CreateBackup(request).get();
  if (!backup) throw std::move(backup).status();
  std::cout
      << "Backup " << backup->name() << " of " << backup->database()
      << " of size " << backup->size_bytes() << " bytes as of "
      << google::cloud::spanner::MakeTimestamp(backup->version_time()).value()
      << " was created at "
      << google::cloud::spanner::MakeTimestamp(backup->create_time()).value()
      << ".\n";
}

    Go

    
import (
	"context"
	"fmt"
	"io"
	"regexp"
	"time"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	pbt "github.com/golang/protobuf/ptypes/timestamp"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func createBackup(ctx context.Context, w io.Writer, db, backupID string, versionTime time.Time) error {
	// versionTime := time.Now().AddDate(0, 0, -1) // one day ago
	matches := regexp.MustCompile("^(.+)/databases/(.+)$").FindStringSubmatch(db)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("createBackup: invalid database id %q", db)
	}

	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return fmt.Errorf("createBackup.NewDatabaseAdminClient: %w", err)
	}
	defer adminClient.Close()

	expireTime := time.Now().AddDate(0, 0, 14)
	// Create a backup.
	req := adminpb.CreateBackupRequest{
		Parent:   matches[1],
		BackupId: backupID,
		Backup: &adminpb.Backup{
			Database:    db,
			ExpireTime:  &pbt.Timestamp{Seconds: expireTime.Unix(), Nanos: int32(expireTime.Nanosecond())},
			VersionTime: &pbt.Timestamp{Seconds: versionTime.Unix(), Nanos: int32(versionTime.Nanosecond())},
		},
	}
	op, err := adminClient.CreateBackup(ctx, &req)
	if err != nil {
		return fmt.Errorf("createBackup.CreateBackup: %w", err)
	}
	// Wait for backup operation to complete.
	backup, err := op.Wait(ctx)
	if err != nil {
		return fmt.Errorf("createBackup.Wait: %w", err)
	}

	// Get the name, create time, version time and backup size.
	backupCreateTime := time.Unix(backup.CreateTime.Seconds, int64(backup.CreateTime.Nanos))
	backupVersionTime := time.Unix(backup.VersionTime.Seconds, int64(backup.VersionTime.Nanos))
	fmt.Fprintf(w,
		"Backup %s of size %d bytes was created at %s with version time %s\n",
		backup.Name,
		backup.SizeBytes,
		backupCreateTime.Format(time.RFC3339),
		backupVersionTime.Format(time.RFC3339))
	return nil
}

    Java

    static void createBackup(DatabaseAdminClient dbAdminClient, DatabaseId databaseId,
    BackupId backupId, Timestamp versionTime) {
  // Set expire time to 14 days from now.
  Timestamp expireTime = Timestamp.ofTimeMicroseconds(TimeUnit.MICROSECONDS.convert(
      System.currentTimeMillis() + TimeUnit.DAYS.toMillis(14), TimeUnit.MILLISECONDS));
  Backup backup =
      dbAdminClient
          .newBackupBuilder(backupId)
          .setDatabase(databaseId)
          .setExpireTime(expireTime)
          .setVersionTime(versionTime)
          .build();
  // Initiate the request which returns an OperationFuture.
  System.out.println("Creating backup [" + backup.getId() + "]...");
  OperationFuture<Backup, CreateBackupMetadata> op = backup.create();
  try {
    // Wait for the backup operation to complete.
    backup = op.get();
    System.out.println("Created backup [" + backup.getId() + "]");
  } catch (ExecutionException e) {
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }

  // Reload the metadata of the backup from the server.
  backup = backup.reload();
  System.out.println(
      String.format(
          "Backup %s of size %d bytes was created at %s for version of database at %s",
          backup.getId().getName(),
          backup.getSize(),
          LocalDateTime.ofEpochSecond(
              backup.getProto().getCreateTime().getSeconds(),
              backup.getProto().getCreateTime().getNanos(),
              OffsetDateTime.now().getOffset()),
          LocalDateTime.ofEpochSecond(
              backup.getProto().getVersionTime().getSeconds(),
              backup.getProto().getVersionTime().getNanos(),
              OffsetDateTime.now().getOffset())
          ));
}

    Nó

    // Imports the Google Cloud client library and precise date library
const {Spanner} = require('@google-cloud/spanner');
const {PreciseDate} = require('@google-cloud/precise-date');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const backupId = 'my-backup';
// const versionTime = Date.now() - 1000 * 60 * 60 * 24; // One day ago

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const backup = instance.backup(backupId);

// Creates a new backup of the database
try {
  console.log(`Creating backup of database ${database.formattedName_}.`);
  const databasePath = database.formattedName_;
  // Expire backup 14 days in the future
  const expireTime = Date.now() + 1000 * 60 * 60 * 24 * 14;
  // Create a backup of the state of the database at the current time.
  const [, operation] = await backup.create({
    databasePath: databasePath,
    expireTime: expireTime,
    versionTime: versionTime,
  });

  console.log(`Waiting for backup ${backup.formattedName_} to complete...`);
  await operation.promise();

  // Verify backup is ready
  const [backupInfo] = await backup.getMetadata();
  if (backupInfo.state === 'READY') {
    console.log(
      `Backup ${backupInfo.name} of size ` +
        `${backupInfo.sizeBytes} bytes was created at ` +
        `${new PreciseDate(backupInfo.createTime).toISOString()} ` +
        'for version of database at ' +
        `${new PreciseDate(backupInfo.versionTime).toISOString()}`
    );
  } else {
    console.error('ERROR: Backup is not ready.');
  }
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  await database.close();
}

    PHP

    use Google\Cloud\Spanner\Backup;
use Google\Cloud\Spanner\SpannerClient;

/**
 * Create a backup.
 * Example:
 * ```
 * create_backup($instanceId, $databaseId, $backupId, $versionTime);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $backupId The Spanner backup ID.
 * @param string $versionTime The version of the database to backup. Read more
 * at https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.backups#Backup.FIELDS.version_time
 */
function create_backup(string $instanceId, string $databaseId, string $backupId, string $versionTime = '-1hour'): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $expireTime = new \DateTime('+14 days');
    $backup = $instance->backup($backupId);
    $operation = $backup->create($database->name(), $expireTime, [
        'versionTime' => new \DateTime($versionTime)
    ]);

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    $backup->reload();
    $ready = ($backup->state() == Backup::STATE_READY);

    if ($ready) {
        print('Backup is ready!' . PHP_EOL);
        $info = $backup->info();
        printf(
            'Backup %s of size %d bytes was created at %s for version of database at %s' . PHP_EOL,
            basename($info['name']), $info['sizeBytes'], $info['createTime'], $info['versionTime']);
    } else {
        printf('Unexpected state: %s' . PHP_EOL, $backup->state());
    }
}

    Python

    def create_backup(instance_id, database_id, backup_id, version_time):
    """Creates a backup for a database."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    # Create a backup
    expire_time = datetime.utcnow() + timedelta(days=14)
    backup = instance.backup(
        backup_id, database=database, expire_time=expire_time, version_time=version_time
    )
    operation = backup.create()

    # Wait for backup operation to complete.
    operation.result(2100)

    # Verify that the backup is ready.
    backup.reload()
    assert backup.is_ready() is True

    # Get the name, create time and backup size.
    backup.reload()
    print(
        "Backup {} of size {} bytes was created at {} for version of database at {}".format(
            backup.name, backup.size_bytes, backup.create_time, backup.version_time
        )
    )

    Ruby

    # project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
# backup_id = "Your Spanner backup ID"
# version_time = Time.now - 60 * 60 * 24 # 1 day ago

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

instance_path = database_admin_client.instance_path project: project_id, instance: instance_id
db_path = database_admin_client.database_path project: project_id,
                                              instance: instance_id,
                                              database: database_id
backup_path = database_admin_client.backup_path project: project_id,
                                                instance: instance_id,
                                                backup: backup_id
expire_time = Time.now + (14 * 24 * 3600) # 14 days from now

job = database_admin_client.create_backup parent: instance_path,
                                          backup_id: backup_id,
                                          backup: {
                                            database: db_path,
                                              expire_time: expire_time,
                                              version_time: version_time
                                          }

puts "Backup operation in progress"

job.wait_until_done!

backup = database_admin_client.get_backup name: backup_path
puts "Backup #{backup_id} of size #{backup.size_bytes} bytes was created at #{backup.create_time} for version of database at #{backup.version_time}"

  2. Restaurar do backup para um novo banco de dados. Observe que o Spanner preserva a configuração do período de armazenamento do backup para o banco de dados restaurado.

    Console

    1. Acesse a página Detalhes da instância no Console do Cloud.

      Acessar o Console do Cloud

    2. Na guia Backup/Restaurar, selecione um backup e clique em Restaurar.

    gcloud

    gcloud spanner databases restore --async \
  --destination-instance=destination-instance --destination-database=example-db-restored \
  --source-instance=test-instance --source-backup=example-db-backup-1

    Para mais informações, consulte Como restaurar um banco de dados de um backup.

    Bibliotecas de cliente

    C#

    
using Google.Cloud.Spanner.Admin.Database.V1;
using Google.Cloud.Spanner.Common.V1;
using Google.LongRunning;
using System;

public class RestoreDatabaseSample
{
    public RestoreInfo RestoreDatabase(string projectId, string instanceId, string databaseId, string backupId)
    {
        // Create the DatabaseAdminClient instance.
        DatabaseAdminClient databaseAdminClient = DatabaseAdminClient.Create();

        InstanceName parentAsInstanceName = InstanceName.FromProjectInstance(projectId, instanceId);
        BackupName backupAsBackupName = BackupName.FromProjectInstanceBackup(projectId, instanceId, backupId);

        // Make the RestoreDatabase request.
        Operation<Database, RestoreDatabaseMetadata> response = databaseAdminClient.RestoreDatabase(parentAsInstanceName, databaseId, backupAsBackupName);

        Console.WriteLine("Waiting for the operation to finish");

        // Poll until the returned long-running operation is complete.
        var completedResponse = response.PollUntilCompleted();

        if (completedResponse.IsFaulted)
        {
            Console.WriteLine($"Database Restore Failed: {completedResponse.Exception}");
            throw completedResponse.Exception;
        }

        RestoreInfo restoreInfo = completedResponse.Result.RestoreInfo;
        Console.WriteLine(
            $"Database {restoreInfo.BackupInfo.SourceDatabase} was restored " +
            $"to {databaseId} from backup {restoreInfo.BackupInfo.Backup} " +
            $"with version time {restoreInfo.BackupInfo.VersionTime.ToDateTime()}");

        return restoreInfo;
    }
}

    C++

    void RestoreDatabase(google::cloud::spanner_admin::DatabaseAdminClient client,
                     std::string const& project_id,
                     std::string const& instance_id,
                     std::string const& database_id,
                     std::string const& backup_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  google::cloud::spanner::Backup backup(database.instance(), backup_id);
  auto restored_db =
      client
          .RestoreDatabase(database.instance().FullName(),
                           database.database_id(), backup.FullName())
          .get();
  if (!restored_db) throw std::move(restored_db).status();
  std::cout << "Database";
  if (restored_db->restore_info().source_type() ==
      google::spanner::admin::database::v1::BACKUP) {
    auto const& backup_info = restored_db->restore_info().backup_info();
    std::cout << " " << backup_info.source_database() << " as of "
              << google::cloud::spanner::MakeTimestamp(
                     backup_info.version_time())
                     .value();
  }
  std::cout << " restored to " << restored_db->name();
  std::cout << " from backup " << backup.FullName();
  std::cout << ".\n";
}

    Go

    
import (
	"context"
	"fmt"
	"io"
	"regexp"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func restoreBackup(ctx context.Context, w io.Writer, db, backupID string) error {
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("Invalid database id %s", db)
	}
	instanceName := matches[1]
	databaseID := matches[2]
	backupName := instanceName + "/backups/" + backupID

	// Start restoring backup to a new database.
	restoreOp, err := adminClient.RestoreDatabase(ctx, &adminpb.RestoreDatabaseRequest{
		Parent:     instanceName,
		DatabaseId: databaseID,
		Source: &adminpb.RestoreDatabaseRequest_Backup{
			Backup: backupName,
		},
	})
	if err != nil {
		return err
	}
	// Wait for restore operation to complete.
	dbObj, err := restoreOp.Wait(ctx)
	if err != nil {
		return err
	}
	// Newly created database has restore information.
	backupInfo := dbObj.RestoreInfo.GetBackupInfo()
	if backupInfo != nil {
		fmt.Fprintf(w, "Source database %s restored from backup %s\n", backupInfo.SourceDatabase, backupInfo.Backup)
	}

	return nil
}

    Java

    static void restoreBackup(
    DatabaseAdminClient dbAdminClient,
    BackupId backupId,
    DatabaseId sourceDatabaseId,
    DatabaseId restoreToDatabase) {
  Backup backup = dbAdminClient.newBackupBuilder(backupId).build();
  // Initiate the request which returns an OperationFuture.
  System.out.println(String.format(
      "Restoring backup [%s] to database [%s]...",
      backup.getId().toString(),
      restoreToDatabase.toString()));
  try {
    OperationFuture<Database, RestoreDatabaseMetadata> op = backup.restore(restoreToDatabase);
    // Wait until the database has been restored.
    Database db = op.get();
    // Refresh database metadata and get the restore info.
    RestoreInfo restore = db.reload().getRestoreInfo();
    Timestamp versionTime = Timestamp.fromProto(restore
        .getProto()
        .getBackupInfo()
        .getVersionTime());
    System.out.println(
        "Restored database ["
            + restore.getSourceDatabase().getName()
            + "] from ["
            + restore.getBackup().getName()
            + "] with version time [" + versionTime + "]");
  } catch (ExecutionException e) {
    throw SpannerExceptionFactory.newSpannerException(e.getCause());
  } catch (InterruptedException e) {
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

    Nó

    // Imports the Google Cloud client library and precise date library
const {Spanner} = require('@google-cloud/spanner');
const {PreciseDate} = require('@google-cloud/precise-date');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// const backupId = 'my-backup';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

// Restore the database
console.log(
  `Restoring database ${database.formattedName_} from backup ${backupId}.`
);
const [, restoreOperation] = await database.restore(
  `projects/${projectId}/instances/${instanceId}/backups/${backupId}`
);

// Wait for restore to complete
console.log('Waiting for database restore to complete...');
await restoreOperation.promise();

console.log('Database restored from backup.');
const restoreInfo = await database.getRestoreInfo();
console.log(
  `Database ${restoreInfo.backupInfo.sourceDatabase} was restored ` +
    `to ${databaseId} from backup ${restoreInfo.backupInfo.backup} ` +
    'with version time ' +
    `${new PreciseDate(restoreInfo.backupInfo.versionTime).toISOString()}.`
);

    PHP

    use Google\Cloud\Spanner\SpannerClient;

/**
 * Restore a database from a backup.
 * Example:
 * ```
 * restore_backup($instanceId, $databaseId, $backupId);
 * ```
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $backupId The Spanner backup ID.
 */
function restore_backup(string $instanceId, string $databaseId, string $backupId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);
    $backup = $instance->backup($backupId);

    $operation = $database->restore($backup->name());
    // Wait for restore operation to complete.
    $operation->pollUntilComplete();

    // Newly created database has restore information.
    $database->reload();
    $restoreInfo = $database->info()['restoreInfo'];
    $sourceDatabase = $restoreInfo['backupInfo']['sourceDatabase'];
    $sourceBackup = $restoreInfo['backupInfo']['backup'];
    $versionTime = $restoreInfo['backupInfo']['versionTime'];

    printf(
        'Database %s restored from backup %s with version time %s' . PHP_EOL,
        $sourceDatabase, $sourceBackup, $versionTime);
}

    Python

    def restore_database(instance_id, new_database_id, backup_id):
    """Restores a database from a backup."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    # Create a backup on database_id.

    # Start restoring an existing backup to a new database.
    backup = instance.backup(backup_id)
    new_database = instance.database(new_database_id)
    operation = new_database.restore(backup)

    # Wait for restore operation to complete.
    operation.result(1600)

    # Newly created database has restore information.
    new_database.reload()
    restore_info = new_database.restore_info
    print(
        "Database {} restored to {} from backup {} with version time {}.".format(
            restore_info.backup_info.source_database,
            new_database_id,
            restore_info.backup_info.backup,
            restore_info.backup_info.version_time,
        )
    )

    Ruby

    # project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID of where to restore"
# backup_id = "Your Spanner backup ID"

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

instance_path = database_admin_client.instance_path project: project_id, instance: instance_id

db_path = database_admin_client.database_path project: project_id,
                                              instance: instance_id,
                                              database: database_id

backup_path = database_admin_client.backup_path project: project_id,
                                                instance: instance_id,
                                                backup: backup_id

job = database_admin_client.restore_database parent: instance_path,
                                             database_id: database_id,
                                             backup: backup_path

puts "Waiting for restore backup operation to complete"

job.wait_until_done!

database = database_admin_client.get_database name: db_path
restore_info = database.restore_info
puts "Database #{restore_info.backup_info.source_database} was restored to #{database_id} from backup #{restore_info.backup_info.backup} with version time #{restore_info.backup_info.version_time}"

Importar e exportar

  1. Exporte o banco de dados, especificando o parâmetro snapshotTime para o carimbo de data/hora de recuperação desejado.

    Console

    1. Acesse a página Detalhes da instância no Console do Cloud.

      Acessar o Console do Cloud

    2. Na guia Importar/Exportar, clique em Exportar.

    3. Marque a caixa Exportar banco de dados de um momento mais antigo.

    Para instruções detalhadas, consulte exportar um banco de dados.

    gcloud

    Use o modelo do Cloud Spanner para Avro do Dataflow para exportar o banco de dados.

    gcloud dataflow jobs run JOB_NAME \
   --gcs-location='gs://cloud-spanner-point-in-time-recovery/Import Export Template/export/templates/Cloud_Spanner_to_GCS_Avro'
   --region=DATAFLOW_REGION --parameters='instanceId=test-instance,databaseId=example-db,outputDir=YOUR_GCS_DIRECTORY,snapshotTime=2020-09-01T23:59:40.125245Z'

    Observações sobre o uso:

    • É possível acompanhar o andamento dos jobs de importação e exportação no Console do Dataflow.
    • O Cloud Spanner garante que os dados exportados terão consistência transacional e externa no carimbo de data/hora especificado.
    • Especifique o carimbo de data/hora no formato RFC 3339. Por exemplo, 2020-09-01T23:59:30.234233Z.
    • Verifique se o carimbo de data/hora especificado é mais recente que o earliest_version_time do banco de dados. Se os dados não existirem mais no carimbo de data/hora especificado, você receberá um erro.

  2. Importar para um novo banco de dados.

    Console

    1. Acesse a página Detalhes da instância no Console do Cloud.

      Acessar o Console do Cloud

    2. Na guia Importar/Exportar, clique em Importar.

    Para instruções detalhadas, consulte Como importar arquivos Avro do Cloud Spanner.

    gcloud

    Use o modelo do Cloud Dataflow Cloud Storage Avro para Cloud Spanner para importar os arquivos Avro.

    gcloud dataflow jobs run JOB_NAME \
   --gcs-location='gs://cloud-spanner-point-in-time-recovery/Import Export Template/import/templates/GCS_Avro_to_Cloud_Spanner' \
   --region=DATAFLOW_REGION \
   --staging-location=YOUR_GCS_STAGING_LOCATION \
   --parameters='instanceId=test-instance,databaseId=example-db,inputDir=YOUR_GCS_DIRECTORY'

Estimar o aumento do armazenamento

Antes de aumentar o período de armazenamento da versão de um banco de dados, calcule o aumento esperado no uso do armazenamento do banco de dados totalizando os bytes da transação pelo período desejado. Por exemplo, a consulta a seguir calcula o número de GiB escrito nos últimos sete dias (168h) lendo as tabelas de estatísticas de transações.

GoogleSQL 

SELECT
  SUM(bytes_per_hour) / (1024 * 1024 * 1024 ) as GiB
FROM (
  SELECT
    ((commit_attempt_count - commit_failed_precondition_count - commit_abort_count) * avg_bytes) AS bytes_per_hour,
    interval_end
  FROM
    spanner_sys.txn_stats_total_hour
  ORDER BY
    interval_end DESC
  LIMIT
    168);

PostgreSQL 

SELECT
  bph /  (1024 * 1024 * 1024 ) as GiB
FROM (
  SELECT
    SUM(bytes_per_hour) as bph
  FROM (
    SELECT
      ((commit_attempt_count - commit_failed_precondition_count - commit_abort_count) * avg_bytes) AS bytes_per_hour,
      interval_end
    FROM
      spanner_sys.txn_stats_total_hour
    ORDER BY
      interval_end DESC
    LIMIT
      168)
sub1) sub2;

Observe que a consulta fornece uma estimativa aproximada e pode ser imprecisa por alguns motivos:

  • A consulta não considera o carimbo de data/hora que precisa ser armazenado para cada versão dos dados antigos. Se o banco de dados consistir em muitos pequenos tipos de dados, a consulta poderá subestimar o aumento do armazenamento.
  • A consulta inclui todas as operações de gravação, mas apenas operações de atualização criam versões antigas de dados. Se a carga de trabalho incluir muitas operações de inserção, a consulta poderá superestimar o aumento do armazenamento.