Snowflake Postgres 时间点恢复¶

概述

Snowflake Postgres supports creating forks of an instance using point-in-time recovery (PITR). A fork is a new instance that reflects the state of an existing instance at a specific time. A fork is similar to a CLONE operation in Snowflake. However, unlike the CLONE operation, a fork performs a full copy of all of the origin data.

由于分支与源实例隔离，对分支进行的任何更改（包括架构或数据）都不会影响源实例。

在以下情况下，时间点恢复非常有用：

Recover from accidental changes, such as dropped tables or incorrect data updates.
检查数据的历史状态，以进行调试或审计。
Test application changes against a realistic copy of production data without impacting the origin instance.

分支是基于指定时间之前源实例的最新基础备份创建的。源实例的预写日志 (WAL) 记录会被回放到所选时间点，从而确保分支实例在该时刻与源实例在事务上保持一致。

分支中复制的内容

创建分支时，将从源实例复制以下内容：

Postgres 版本。复制版本以保证二进制兼容性。
The high availability setting (enabled or disabled).
用于访问实例的凭据。

在创建新实例时，您可以自定义一些属性，例如存储和 实例大小（计划）。分支实例的定价与其他实例一样，取决于分支的配置（计划、存储和高可用性）。

创建分支

In the navigation menu, select Postgres.
选择要创建分支的实例。
Under Manage on the Postgres Instance page, select the Fork item and enter the configuration options.
Select Fork to create the fork.

要将 Postgres 实例创建为源实例的分支，请执行 CREATE POSTGRES INSTANCE 命令，并指定 FORK 子句。该命令会根据 AT 或 BEFORE 子句指定的时间点，从源实例创建分支。如果省略该子句，分支将基于源实例的当前状态创建。

CREATE POSTGRES INSTANCE <name>
  FORK <orig_name>
  [ { AT | BEFORE } ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> } ) ]
  [ COMPUTE_FAMILY = <compute_family> ]
  [ STORAGE_SIZE_GB = <storage_gb> ]
  [ HIGH_AVAILABILITY = { TRUE | FALSE } ]
  [ POSTGRES_SETTINGS = '<json_string>' ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , ... ] ) ]

对于命令参数：

FORK orig_name

指定分支的源实例。

{ AT | BEFORE } ( { TIMESTAMP => timestamp | OFFSET => time_difference } )

Specifies the point in time to fork from. The timestamp or offset must fall within the 10 day postgres data retention time.

Default: Uses current time.

The AT | BEFORE clause accepts one of the following parameters:

TIMESTAMP => timestamp
指定要用于 Time Travel 的确切日期和时间。该值必须显式转换为 TIMESTAMP、TIMESTAMP_LTZ、TIMESTAMP_NTZ 或 TIMESTAMP_TZ 数据类型。
If no explicit cast is specified, the timestamp in the AT clause is treated as a timestamp with the UTC time zone (equivalent to TIMESTAMP_NTZ). Using the TIMESTAMP data type for an explicit cast may also result in the value being treated as a TIMESTAMP_NTZ value. For details, see Date & time data types.
OFFSET => time_difference
Specifies the difference in seconds from the current time to use for Time Travel, in the form -N where N can be an integer or arithmetic expression (e.g. -120 is 120 seconds, -30*60 is 1800 seconds or 30 minutes).
Default: Copied from the origin.

COMPUTE_FAMILY = compute_family

Specifies the name of an instance size from the Snowflake Postgres Instance Sizes tables.

Default: Copied from the origin.

STORAGE_SIZE_GB = storage_gb

指定存储大小，单位为 GB。必须介于 10 到 65,535 之间。

Default: Copied from the origin.

HIGH_AVAILABILITY = { TRUE | FALSE }

指定要用于分支的高可用性设置。

Default: Copied from the origin.

POSTGRES_SETTINGS = 'json_string'

Allows you to optionally set Postgres configuration parameters on your instance in JSON format. See Snowflake Postgres Server Settings for a list of available Postgres parameters.

'{"component:name" = "value", ...}'

Default: Copied from the origin.

COMMENT = 'string_literal'

指定用户的注释。

Default: NULL

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas.

将返回一行，包含以下列：

status
host

CREATE FORK SQL 示例

Create a fork my_fork from the origin instance my_origin_instance at the timestamp 2025-01-01 12:00:00.
CREATE POSTGRES INSTANCE my_fork
  FORK my_origin_instance
  AT (TIMESTAMP => '2025-01-01 12:00:00');
Create a fork my_fork from the origin instance my_origin_instance as it was 120 seconds ago.
CREATE POSTGRES INSTANCE my_fork
  FORK my_origin_instance
  AT (OFFSET => -120);
Create a fork my_fork from the origin instance my_origin_instance as of the current time, using the STANDARD_M instance size and no high availability.
CREATE POSTGRES INSTANCE my_fork
  FORK my_origin_instance
  COMPUTE_FAMILY = STANDARD_M
  HIGH_AVAILABILITY = FALSE;

When you create a fork, no credentials will be displayed. Credentials for the fork are the same as the origin instance. You can regenerate credentials later if needed.

创建分支所需时间取决于源实例的大小。