Troubleshoot mirrors¶

The following are known issues and 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.

Solution: 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', ...)

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

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

Target database names can’t be reused¶

After 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.

Solution: 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 (see Roles and permissions):

• 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.

Solution: 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 (see DDL updates in $changes). 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>".

Solution: Build a new column in Postgres and migrate that data to the new type, instead of changing the column type.

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 (see DDL updates in $changes). 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.

Solution: 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, which defaults to 10, CREATE MIRROR fails with:

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

Solution: Increase max_replication_slots and max_wal_senders together (the latter should be set to at least the same value) using server settings, 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 might 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.

Solution: 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.

Solution: 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.

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

Mirror re-snapshots all tables after a WAL-lag spike¶

If the Snowflake apply procedure falls behind and the write-ahead log (WAL) backlog grows beyond max_slot_wal_keep_size, Postgres invalidates the replication slot to prevent the instance disk from filling. Mirroring detects the invalidated slot and automatically re-snapshots all tables 10 minutes later.

Symptoms:

• All mirrored tables revert to SNAPSHOTTING status without an explicit RESTART_MIRROR call.
• The $changes feed is rebuilt from a fresh snapshot baseline, losing incremental change history.

Solutions:

• Increase disk size. max_slot_wal_keep_size defaults to one-tenth of the allocated disk. A larger disk raises the effective WAL retention limit without changing any parameter. See Modify an instance.
• Increase max_slot_wal_keep_size directly. Adjust the value using server settings. Set it high enough to cover the longest expected apply lag for your workload.