Manage and monitor mirrors¶

Use the procedures in the snowflake.postgres schema to alter, drop, inspect, and monitor mirrors after they’re created.

Roles and permissions¶

Managing mirrors (create, alter, drop, inspect, monitor) requires the postgres_mirror_admin application role on snowflake.postgres. ACCOUNTADMIN grants it to the role that manages mirrors:

GRANT APPLICATION ROLE snowflake.postgres_mirror_admin TO ROLE ACCOUNTADMIN;

The caller of every mirror management procedure must also own the source PostgreSQL instance. The procedures verify this before doing any privileged work, so a user who holds postgres_mirror_admin but doesn’t own the source instance can’t manage its mirrors.

For the role that governs reading mirrored data, see Query mirrored data.

Alter a mirror¶

Use alter_mirror to change the refresh interval, or to add or remove tables and schemas from the mirror:

CALL SNOWFLAKE.POSTGRES.ALTER_MIRROR(
    mirror_name      => 'orders_mirror',
    refresh_interval => '30 seconds',
    add_tables       => ['public.shipments'],
    remove_tables    => NULL,
    add_schemas      => NULL,
    remove_schemas   => NULL
);

Renaming a schema or table on PostgreSQL doesn’t require reconfiguring the mirror. The rename is captured by the source extension and automatically applied to the target database.

Drop a mirror¶

Use drop_mirror to remove a mirror and clean up all associated infrastructure, including the publication on the source, the apply task on Snowflake, and mirror metadata:

CALL SNOWFLAKE.POSTGRES.DROP_MIRROR('orders_mirror');

By default, the target database is left in place and target tables remain queryable. The database is marked as previously used so it can’t be reassigned to a new mirror.

The target database is owned by the snowflake application, so it can’t be dropped or modified directly. To drop it, ACCOUNTADMIN can transfer ownership first:

GRANT OWNERSHIP ON DATABASE POSTGRESMIRRORTOSNOWFLAKE TO ROLE ACCOUNTADMIN;
DROP DATABASE POSTGRESMIRRORTOSNOWFLAKE;

To drop the target database as part of drop_mirror, use drop_target_database => TRUE:

CALL SNOWFLAKE.POSTGRES.DROP_MIRROR('orders_mirror', drop_target_database => TRUE);

Inspect mirrors¶

Use describe_mirror to check the configuration and runtime state of a specific mirror (error_message contains the error from the most recent apply run, or NULL if the mirror is healthy):

CALL SNOWFLAKE.POSTGRES.DESCRIBE_MIRROR('orders_mirror');

Use list_mirrors to see all mirrors on a Postgres instance:

CALL SNOWFLAKE.POSTGRES.LIST_MIRRORS('my_instance');

Use list_mirrored_tables to see the replication state of each table in a mirror:

CALL SNOWFLAKE.POSTGRES.LIST_MIRRORED_TABLES('orders_mirror');

Each table has one of the following status indicators:

• SNAPSHOTTING: Snowflake is copying the current contents of the Postgres table into Snowflake for the first time.
• REPLICATING: The initial sync is complete and the mirror is continuously applying new changes from Postgres to Snowflake. This is the normal healthy state.

For full result column details, see Mirror management procedures.

Monitor mirroring¶

The describe_mirror procedure surfaces a rolling window of recent apply runs in the recent_query_durations column for a quick look at cadence and latency. Each apply run displays a state of SUCCEEDED, FAILED, or CANCELLED.

For a full history, ACCOUNTADMIN can query Snowflake’s task history directly:

SELECT name, state, scheduled_time, completed_time, error_code, error_message
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.TASK_HISTORY(
    SCHEDULED_TIME_RANGE_START => DATEADD('hour', -24, CURRENT_TIMESTAMP()),
    RESULT_LIMIT => 100
))
WHERE name LIKE 'APPLY_MIRROR_%'
ORDER BY scheduled_time DESC;

The query_admin_log procedure exposes mirror-internal events with optional filters:

CALL SNOWFLAKE.POSTGRES.QUERY_ADMIN_LOG(
    mirror_name       => 'orders_mirror',
    postgres_instance => 'my_instance',
    level             => 'ERROR',
    since_ts          => NULL,
    max_rows          => 500
);

If an apply task run fails, its error message is persisted on the mirror and surfaced by describe_mirror and list_mirrors. The next successful run clears the error.

Suspend and resume mirroring¶

You have two options when suspending a Postgres instance that has active mirrors: leave the mirror running, or explicitly suspend it first. The two approaches have different effects on replication continuity and the $changes feed.

Suspend the instance without pausing the mirror¶

If you suspend the Postgres instance without calling SUSPEND_MIRROR, the apply task keeps running and mirrored tables continue to show a status of REPLICATING. Once all buffered changes that were already recorded in cloud storage are merged, no further data is applied until the instance resumes. No action is needed on the mirror side.

When you resume the instance, mirroring picks up from where it left off. No re-snapshot is performed, and the $changes feed retains the full incremental history of inserts, updates, and deletes.

Suspend the mirror before suspending the instance¶

To stop apply task activity entirely while the instance is suspended, call SUSPEND_MIRROR before suspending:

CALL SNOWFLAKE.POSTGRES.SUSPEND_MIRROR('orders_mirror');

After resuming the Postgres instance, restart the mirror:

CALL SNOWFLAKE.POSTGRES.RESTART_MIRROR('orders_mirror');

Troubleshooting¶

The following are known limitations, with workarounds.

Postgres instance needs to be refreshed¶

create_mirror can fail on an existing Postgres instance with an error similar to:

Error from Postgres: ERROR: permission denied to create extension

This happens when the source instance predates the mirroring feature and doesn’t have the Postgres extensions that mirroring depends on.

Workaround: Refresh the instance from the Postgres Manage options in Snowsight to install the latest extensions, then retry create_mirror. See Prepare your Postgres instance for details.

Mirror names are folded to lowercase¶

Mirror names containing uppercase letters are folded to lowercase.

CREATE_MIRROR(mirror_name => 'UpperMirror', ...)

will succeed, but will silently fold the mirror name to uppermirror.

Workaround: Prefer lowercase characters in mirror names. For example, use mymirror instead of MyMirror.

Failed mirror creation can leave a target database behind¶

If create_mirror fails partway through, the target database may already exist on the Snowflake side even though the mirror itself wasn’t created. Retrying with the same target_database then fails because the database is already present.

Workaround: Retry create_mirror with a different target_database name.

Target database names can’t be reused¶

Once a database has been used as a mirror target, the same name can’t be reused for a new mirror, even if the mirror is dropped and the database itself is dropped.

Workaround: Pick a new target_database name when creating a replacement mirror.

Active mirrors break if usage is revoked from the snowflake application¶

Apply runs will fail with error POSTGRES INSTANCE ... does not exist or not authorized if the USAGE grant on the Postgres instance has been revoked from the snowflake application. This can happen in two ways:

• Explicit revoke: REVOKE USAGE ON POSTGRES INSTANCE ... FROM APPLICATION SNOWFLAKE
• Ownership transfer with REVOKE CURRENT GRANTS: Running GRANT OWNERSHIP ON POSTGRES INSTANCE ... TO ROLE ... REVOKE CURRENT GRANTS drops all existing grants on the instance, including the USAGE grant that mirroring depends on.

Workaround: Re-grant the privilege:

GRANT USAGE ON POSTGRES INSTANCE "my_instance" TO APPLICATION SNOWFLAKE;