Using Point-in-time Recovery

This page describes how to use point-in-time recovery (PITR) to retain and recover data in Cloud Spanner.

To learn more, see Point-in-time recovery.

Prerequisites

This guide uses the database and schema as defined in the Cloud Spanner quickstart. You can either run through the quickstart to create the database and schema, or modify the commands for use with your own database.

Setting the retention period

To set your database's retention period:

Console

Go the Database details page in the Cloud Console. The page lets you view and update the retention period.

Go to the Cloud Console

gcloud

Update the database's schema with the ALTER DATABASE statement. For example:

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

To view the retention period, get your database's DDL:

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

Here's the output:

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

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::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);
  std::vector<std::string> extra_statements;
  extra_statements.push_back("ALTER DATABASE `" + database_id + "` " +
                             "SET OPTIONS (version_retention_period='2h')");
  extra_statements.emplace_back(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX)
      ) PRIMARY KEY (SingerId))""");
  extra_statements.emplace_back(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(database, std::move(extra_statements)).get();
  if (!db) throw std::runtime_error(db.status().message());
  std::cout << "Database " << db->name() << " created.\n";

  auto ddl = client.GetDatabaseDdl(database);
  if (!ddl) throw std::runtime_error(ddl.status().message());
  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(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)
	}

	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return fmt.Errorf("createDatabaseWithRetentionPeriod.NewDatabaseAdminClient: %v", 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: %v", err)
	}
	if _, err := op.Wait(ctx); err != nil {
		return fmt.Errorf("createDatabaseWithRetentionPeriod.Wait: %v", 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);
    }
  }
}

Node

// 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($instanceId, $databaseId, $retentionPeriod)
{
    $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"

spanner  = Google::Cloud::Spanner.new project: project_id
instance = spanner.instance instance_id
version_retention_period = "7d"

job = instance.create_database database_id, 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 = job.database

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}"

Usage notes:

  • The retention period must be between 1 hour and 7 days, and can be specified in days, hours, minutes, or seconds. For example, the values 1d, 24h, 1440m, and 86400s are equivalent.
  • If you have enabled logging for the Cloud Spanner API in your project, the event is logged as UpdateDatabaseDdl and is visible in the Logs Viewer.
  • To revert to the default retention period of 1 hour, you can either set the value to 1 hour or NULL.
  • When you extend the retention period, the system does not backfill previous versions of data. For example, if you extend the retention period from 1h to 24h, then you must wait 23h for the system to accumulate old data before you can recover data from 24h in the past.

Getting the retention period and earliest version time

The Database resource includes two fields:

  • version_retention_period: the period in which Cloud Spanner retains all versions of data for the database.
  • earliest_version_time: the earliest timestamp at which older versions of the data can be read from the database. This value is continuously updated by Cloud Spanner and becomes stale the moment it is queried. If you are using this value to recover data, make sure to account for the time from the moment when the value is queried to the moment when you initiate the recovery.

Console

The retention period is in the Database details page.

The earliest version time is in the Create backup page.

gcloud

You can get these fields by calling describe databases or list databases. For example:

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

Here's the output:

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

Recovering a portion of your database

  1. Perform a stale read and specify the desired recovery timestamp. Make sure that the timestamp you specify is more recent than the database's earliest_version_time.

    gcloud

    Use execute-sql. For example:

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

    Client libraries

    See perform stale read.

  2. Store the results of the query. This is required because you cannot write the results of the query back into the database in the same transaction. For small amounts of data, you can print to console or store in-memory. For larger amounts of data, you may need to write to a file or temporary table.

  3. Write the recovered-data back to the database, for example:

    gcloud

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

    For more information, see updating data using gcloud.

    Client libraries

    See updating data using DML or updating data using mutations.

Recovering an entire database

You can recover the entire database using either Backup and Restore or Import and Export and specifying a recovery timestamp.

Backup and Restore

  1. Create a backup and set the version_time to the desired recovery timestamp.

    Console

    1. Go to the Database details page in the Cloud Console.

      Go to the Cloud Console

    2. In the Backup/Restore tab, click Create.

    3. Check the create backup from an earlier point in time box.

    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
    

    For more information, see Create a backup using gcloud.

    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;
    
    namespace GoogleCloudSamples.Spanner
    {
        public class CreateBackup
        {
            public static object SpannerCreateBackup(
                string projectId, string instanceId, string databaseId, string backupId)
            {
                // 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 = DateTime.UtcNow.ToTimestamp(),
                };
                InstanceName parentAsInstanceName = InstanceName.FromProjectInstance(projectId, instanceId);
    
                // Make the CreateBackup request.
                Operation<Backup, CreateBackupMetadata> response =
                    databaseAdminClient.CreateBackup(parentAsInstanceName, 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}");
                    return 1;
                }
    
                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}");
                return 0;
            }
        }
    }
    

    C++

    void CreateBackup(google::cloud::spanner::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);
      auto backup =
          client.CreateBackup(database, backup_id, expire_time, version_time).get();
      if (!backup) throw std::runtime_error(backup.status().message());
      std::cout
          << "Backup " << backup->name() << " of " << backup->database()
          << " of size " << backup->size_bytes() << " bytes"
          << " 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(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)
    	}
    
    	ctx := context.Background()
    	adminClient, err := database.NewDatabaseAdminClient(ctx)
    	if err != nil {
    		return fmt.Errorf("createBackup.NewDatabaseAdminClient: %v", 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: %v", err)
    	}
    	// Wait for backup operation to complete.
    	backup, err := op.Wait(ctx)
    	if err != nil {
    		return fmt.Errorf("createBackup.Wait: %v", 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())
              ));
    }

    Node

    // 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.
     */
    function create_backup($instanceId, $databaseId, $backupId, $versionTime)
    {
        $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 {
            print('Backup is not ready!' . PHP_EOL);
        }
    }

    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(1200)
    
        # 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"
    
    spanner = Google::Cloud::Spanner.new project: project_id
    client = spanner.client instance_id, database_id
    instance = spanner.instance instance_id
    database = instance.database database_id
    expire_time = Time.now + 14 * 24 * 3600 # 14 days from now
    
    job = database.create_backup backup_id, expire_time, version_time: version_time
    
    puts "Backup operation in progress"
    
    job.wait_until_done!
    
    backup = instance.backup backup_id
    puts "Backup #{backup.backup_id} of size #{backup.size_in_bytes} bytes was created at #{backup.create_time} for version of database at #{backup.version_time}"

  2. Restore from the backup to a new database. Note that Cloud Spanner preserves the retention period setting from the backup to the restored database.

    Console

    1. Go to the Instance details page in the Cloud Console.

      Go to the Cloud Console

    2. In the Backup/Restore tab, select a backup and click Restore.

    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
    

    For more information, see Restoring a database from a backup.

    C#

    
    using Google.Cloud.Spanner.Admin.Database.V1;
    using Google.Cloud.Spanner.Common.V1;
    using Google.LongRunning;
    using System;
    
    namespace GoogleCloudSamples.Spanner
    {
        public class RestoreDatabase
        {
            public static object SpannerRestoreDatabase(
                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}");
                    return 1;
                }
    
                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 0;
            }
        }
    }

    C++

    void RestoreDatabase(google::cloud::spanner::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, backup).get();
      if (!restored_db) {
        throw std::runtime_error(restored_db.status().message());
      }
      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(w io.Writer, db, backupID string) error {
    	ctx := context.Background()
    	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);
      }
    }

    Node

    // 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($instanceId, $databaseId, $backupId)
    {
        $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"
    
    spanner  = Google::Cloud::Spanner.new project: project_id
    instance = spanner.instance instance_id
    
    backup = instance.backup backup_id
    job = backup.restore database_id
    
    puts "Waiting for restore backup operation to complete"
    
    job.wait_until_done!
    
    restore_info = job.database.restore_info
    puts "Database #{restore_info.backup_info.source_database_id} was restored to #{database_id} from backup #{restore_info.backup_info.backup_id} with version time #{restore_info.backup_info.version_time}"

Import and Export

  1. Export the database, specifying the snapshotTime parameter to the desired recovery timestamp.

    Console

    1. Go to the Instance details page in the Cloud Console.

      Go to the Cloud Console

    2. In the Import/Export tab, click Export.

    3. Check the Export database from an earlier point in time box.

    For detailed instructions, see export a database.

    gcloud

    Use the Cloud Spanner to Avro Dataflow template to export the database.

    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'
    

    Usage notes:

    • You can track the progress of your import and export jobs in the Dataflow Console.
    • Cloud Spanner guarantees that the exported data will be externally and transactionally consistent at the specified timestamp.
    • Specify the timestamp in RFC 3339 format. For example, 2020-09-01T23:59:30.234233Z.
    • Make sure that the timestamp you specify is more recent than the database's earliest_version_time. If data no longer exists at the specified timestamp, you will get an error.
  2. Import to a new database.

    Console

    1. Go to the Instance details page in the Cloud Console.

      Go to the Cloud Console

    2. In the Import/Export tab, click Import.

    For detailed instructions, see Importing Cloud Spanner Avro Files.

    gcloud

    Use the Cloud Storage Avro to Cloud Spanner Dataflow template to import the Avro files.

    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'
    

Estimate the storage increase

Before increasing a database's version retention period, you can estimate the expected increase in database storage utilization by totaling the transaction bytes for the desired period of time. For example the following query calculates the number of GiB written in the past 7 days (168h) by reading from the transaction statistics tables.

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);

Note that the query provides a rough estimate and can be inaccurate for a few reasons:

  • The query doesn't account for the timestamp that must be stored for each version of old data. If your database consists of many small data types, the query may underestimate the storage increase.
  • The query includes all write operations, but only update operations create old versions of data. If your workload includes a lot of insert operations, the query may overestimate the storage increase.