数据库复制注意事项

This topic describes the behavior of certain Snowflake features in secondary databases when using database replication. For additional guidance for working with replicated objects and data, refer to Replication considerations.

数据库复制和安全对象

本节介绍了安全策略和密钥的数据库复制行为。

Masking & Row Access Policies:

如果满足以下任一条件，则复制操作会失败：

• The primary database is in an Enterprise (or higher) account and contains a policy/tag but one or more of the accounts approved for replication are on lower editions.
• An object contained in the primary database has a dangling reference to a tag in a different database.

The dangling reference behavior for database replication can be avoided when replicating multiple databases in a replication or failover group.

Tag-based masking policies:

如果满足以下任一条件，则复制操作会失败：

• The primary database is in an Enterprise (or higher) account and contains a policy/tag but one or more of the accounts approved for replication are on lower editions.
• An object contained in the primary database has a dangling reference to a tag in a different database.

For more information about tag-based masking policies, refer to Tag-based masking policies.

Password, Session, & Authentication Policies:

如果满足以下任一条件，则复制操作会失败：

• The primary database is in an Enterprise (or higher) account and contains a policy but one or more of the accounts approved for replication are on lower editions.
• Either of these objects contained in the primary database is attached to a user in the same account. In this case, Snowflake fails the replication operation.

To avoid the failed database replication operation due to a reference to a user, use a replication or failover group instead.

For details, refer to Replication and security policies.

Secrets:

You cannot replicate a secret using database replication. Use a replication or failover group to replicate a secret. For details, see Replication and secrets.

悬空引用

引用其他数据库中的对象

Carefully analyze whether views or table constraints in a primary database reference objects in another database. For database objects, you can view object dependencies in the Account Usage OBJECT_DEPENDENCIES view.

下表描述了数据库中的一个对象（引用对象）引用另一个数据库中的一个对象（被引用的对象）时的数据库复制行为：

非物化视图

非物化视图引用其他数据库（例如表列、其他视图、UDFs 或暂存区）中的对象，可以复制非物化视图，因为这类引用是基于名称的。基于名称的引用不会导致复制失败；但是，如果其他数据库未复制到同一区域，则无法对辅助数据库中的视图进行查询。

For example, suppose view v1 in database d1 references tables t1 and t2 in databases d1 and d2, respectively. To successfully query view v1 in the secondary database d1, secondary database d2 must also exist in the account (e.g. as another secondary database). In addition, for consistent query results with the primary databases, secondary databases d1 and d2 must be refreshed at the same time.

物化视图

物化视图中的悬空引用可能会导致复制失败，并显示以下错误消息：

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing (referred entity <- [referring entities])

在以下情况下，可能会出现悬空引用：

• 物化视图引用其他数据库中的对象。

物化视图通过 ID 而不是名称来引用对象。对数据库外部对象基于 ID 的引用，数据库快照无法对其进行解析。

To work around this limitation, replicate both databases together in the same replication or failover group. Alternatively, you can store materialized views and the objects they reference in the same database.

• A materialized view is invalid (i.e. references a dropped object).

  To avoid a dangling reference error for invalid materialized views, identify and fix the problem with the materialized view. Refer to the Troubleshooting section in the materialized views topic.

约束

当前，悬空外键会导致复制失败，并显示以下错误消息：

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing
(referredentity <- [referring entities])

当主数据库中的外键引用其他数据库中的主键时，就会出现这种情况，反之亦然。那是因为约束引用是基于 ID 的。对数据库外部对象基于 ID 的引用，数据库快照无法对其进行解析。

To view the foreign key references in your account, query the Information Schema TABLE_CONSTRAINTS view or the Account Usage TABLE_CONSTRAINTS view.

To work around this limitation, replicate both databases together in the same replication or failover group. Alternatively, you can store linked tables in the same database.

序列

当前，悬空序列会导致复制失败，并显示以下错误消息：

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing
(referred entity <- [referring entities])

当主数据库中的表引用其他数据库中的序列时，就会出现这种情况。那是因为序列引用基于 ID。对数据库外部对象基于 ID 的引用，数据库快照无法对其进行解析。

To work around this limitation, replicate both databases together in the same replication or failover group. Alternatively, you can reference sequences in the same database.

掩码和行访问策略及标签

A dangling reference for a masking policy, row access policy, or tag causes the replication to fail with the following error message:

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing
(referred entity <- [referring entities])

This situation occurs when the policy/tag and the object that has the policy/tag assigned to it exist in different databases. For example, a table named db1.s1.t1, a row access policy named db2.s1.rap1, and the row access policy is assigned to the table.

To work around this limitation, replicate both databases together in the same replication or failover group.

引用已删除的对象

删除由同一个或另一个数据库中的另一个对象引用的对象，会导致悬空引用。当主数据库中的对象引用已删除的对象时，复制操作会失败，并显示以下错误消息：

Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing
(referred entity <- [referring entities])

To work around this limitation, we recommend that you complete any one of the following steps:

• 取消删除所引用的对象。
• Modify the referring objects (for example, modify a materialized view using ALTER MATERIALIZED VIEW). Either reference a different object or remove the reference to the dropped object.
• 如果主数据库引用已删除的对象，则删除主数据库中的所有对象。

复制多个数据库

当复制多个数据库时，数据库之间的时间点一致性不可用。每个主数据库的快照是独立创建的，对辅助数据库的更改是独立提交的。如果视图跨不同数据库中的表进行联合，或者依赖于跨数据库事务，则可能会出现问题。例如，以原子方式更新两个主数据库的事务，可能不会同时反映在辅助数据库中。

To replicate multiple databases with point in time consistency, use a replication or failover group.

动态表和数据复制

如果动态表在数据库复制之外引用源对象，则仍可对其进行复制。但是，如果辅助数据库的名称与主数据库的名称不同，则名称解析可能会变得复杂。故障转移后，这可能会导致意外的刷新结果，具体取决于源对象的引用方式。为防止这种情况，请避免在复制设置期间对数据库进行重命名，或改用故障转移组复制。

In the following diagram, the dynamic table dt references a source object source_table using a fully qualified name. For example:

CREATE DYNAMIC TABLE dt
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = my_wh
  AS
    SELECT * FROM db2.sch1.source_table

During replication, DB1 is renamed to DB2 in the secondary account. After failover, refreshing the dynamic table dt in DB2 in the secondary account resolves the source table within the same database, not the original primary database. While this aligns with name resolution rules, it might lead to unexpected results.

In the following diagram, dt references source_table using a fully qualified name, and the replication renames DB1 to DB2 in the secondary account. dt in the secondary account now references a source table that is outside of the containing database.