Manage and monitor mirrors¶

Use the procedures in the snowflake.postgres schema to alter, drop, inspect, and monitor mirrors after they’re created. These procedures take a mirror name but not an instance name; mirror names are unique across all Postgres instances in the account, and the instance association is stored when the mirror is created. To see all mirrors visible to the current role, or to find which instance a mirror belongs to, call list_mirrors without arguments:

SELECT * FROM TABLE(SNOWFLAKE.POSTGRES.LIST_MIRRORS());

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¶

drop_mirror suspends the apply task and waits for any in-flight run to finish before removing the mirror. It then removes all associated infrastructure: the CDC publication on the source Postgres instance, the apply task, all mirrored target tables, the <table>$changes Iceberg change feeds, and the <MIRROR_NAME>_CATALOG_INTEGRATION catalog integration that was auto-created when the mirror was set up.

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

The target database is left as an empty orphan. It can’t be reassigned to a new mirror (see Target database names can’t be reused). 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(mirror_name => 'orders_mirror', drop_target_database => TRUE);

Drop is blocked by user-created Iceberg tables¶

If another role has created Iceberg tables in a different database that reference the mirror’s <MIRROR_NAME>_CATALOG_INTEGRATION, drop_mirror fails:

[4145] Catalog Integration <MIRROR_NAME>_CATALOG_INTEGRATION cannot be dropped because it has active table(s) using it. Some of those tables are not accessible by the current role.

The “not accessible by the current role” wording can be misleading: the blocking tables may be in a database outside your current role’s privileges. To find them, run:

SHOW ICEBERG TABLES IN ACCOUNT
  ->> SELECT "database_name", "schema_name", "name", "owner"
      FROM $1 WHERE "catalog_name" = '<MIRROR_NAME>_CATALOG_INTEGRATION';

Drop all tables that reference the catalog integration, then retry drop_mirror.

Teardown order when dropping an instance¶

Each mirror’s <MIRROR_NAME>_CATALOG_INTEGRATION references the Postgres instance, so all mirrors must be dropped before dropping the instance. Attempting to drop the instance first fails:

Cannot drop Postgres instance <instance_name> because catalog integration(s) <MIRROR_NAME>_CATALOG_INTEGRATION still reference it.

Drop in this order:

-- 1. Identify and drop any user-created Iceberg tables that reference the mirror's
--    catalog integration (see diagnostic query above)

-- 2. Drop each mirror (removes target tables, $changes feeds, and catalog integration)
CALL SNOWFLAKE.POSTGRES.DROP_MIRROR('<mirror_name>');

-- 3. Drop the Postgres instance
DROP POSTGRES INSTANCE "<instance_name>";

-- 4. Optional: drop the orphaned target database
DROP DATABASE <target_database>;

Automatic cleanup of orphaned mirrors¶

If a Postgres instance is removed while mirrors still reference it, those mirrors are detected and removed automatically. The target database is not removed automatically; drop it manually when it’s no longer needed.

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 mirrors on a specific Postgres instance, or call it without arguments to list all mirrors visible to the current role:

-- All mirrors on one instance:
CALL SNOWFLAKE.POSTGRES.LIST_MIRRORS('my_instance');

-- All mirrors in the account visible to the current role:
CALL SNOWFLAKE.POSTGRES.LIST_MIRRORS();

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

Mirror auto-suspend after repeated failures¶

Snowflake automatically suspends a mirror’s apply task if it has been failing continuously for approximately 96 hours. The 96-hour window is designed to surface a silently failing mirror before unapplied change records age out on the source side (which happens at 7 days), and covers a Friday-to-Monday outage with time left for investigation.

When a mirror is auto-suspended, describe_mirror shows the mirror as suspended and includes the error from the most recent failed apply run.

Once you’ve resolved the underlying problem, restart the mirror:

-- Soft restart (default): re-snapshots all tables; preserves any queued operations in the source metalog
CALL SNOWFLAKE.POSTGRES.RESTART_MIRROR('orders_mirror');

-- Hard restart: re-snapshots all tables AND truncates the source-side metalog first.
-- Use when the apply state has diverged significantly from the source.
CALL SNOWFLAKE.POSTGRES.RESTART_MIRROR('orders_mirror', truncate_feed => TRUE);

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.

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;

Changing a column type on a mirrored table fails¶

ALTER TABLE ... ALTER COLUMN ... TYPE is not supported on tables tracked by a mirror. Postgres rejects the statement with an error:

ERROR:  cannot alter column type on table "<table_name>"
DETAIL:  Table is tracked by snowflake_cdc publication "<mirror_name>".

Adding a virtual generated column to a mirrored table fails¶

ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS ... is not supported on tables tracked by a mirror. Postgres rejects the statement with an error:

ERROR:  cannot add generated column on table "<table_name>"
DETAIL:  Table "<table_name>" is tracked by a snowflake_cdc publication. ALTER TABLE ADD COLUMN does not populate generated columns on the mirror via WAL, so the mirror would diverge from the source.

To use a computed value alongside a mirrored table, create the equivalent expression as a view in Snowflake against the target table rather than as a generated column in Postgres.

CREATE MIRROR fails if max_replication_slots is too low¶

Each mirror uses one PostgreSQL replication slot. When the instance is close to the max_replication_slots limit (default: 10), CREATE MIRROR fails with:

snowflake_cdc: cannot create publication "<mirror_name>" because the cluster is close to max_replication_slots

Workaround: Increase max_replication_slots and max_wal_senders together (the latter should be set to at least the same value), then restart the instance:

ALTER POSTGRES INSTANCE "my_instance"
    SET POSTGRES_SETTINGS = (
        'max_replication_slots' = '20',
        'max_wal_senders' = '20'
    );

Mirroring setup fails on newly created Snowflake accounts¶

On newly created Snowflake accounts, the snowflake application may not be fully provisioned immediately. Attempting to grant the postgres_mirror_admin role too soon fails with:

SQL compilation error: Application role 'SNOWFLAKE.POSTGRES_MIRROR_ADMIN' does not exist or not authorized.

Workaround: Wait up to one hour after account creation before setting up mirroring.

Dropping the source Postgres database fails while mirrors are active¶

Postgres blocks dropping a database that has active snowflake_cdc replication slots:

ERROR:  cannot drop database "<database_name>" because it has snowflake_cdc replication slots
HINT:  Drop the snowflake_cdc mirrors and publications in database "<database_name>" first.

Workaround: Drop all mirrors on the database using drop_mirror, then drop the database.

Renaming the source database causes mirrors to fail¶

Renaming the source Postgres database on a mirrored instance is not supported. Doing so will cause existing mirrors to fail.

Workaround: If you need to rename the database, drop existing mirrors first, rename the database, then create new mirrors.