Create a mirror¶

Follow these steps to create a mirror from Snowflake Postgres to a Snowflake analytics database.

Step 1: Prepare your Postgres instance¶

Mirroring requires the latest Postgres extensions on the source instance. Follow the path that applies to you.

Create a new instance¶

Create a new Snowflake Postgres instance from Snowsight or SQL. See Create a Snowflake Postgres instance for detailed instructions. New instances include the required extensions automatically, so no refresh is needed.

Refresh an existing instance¶

If you have an existing instance that was created before the mirroring feature was available, refresh the instance from the Postgres Manage options in Snowsight before creating a mirror. Refreshing picks up the latest Snowflake Postgres software patch, which is required for mirroring.

Sizing your instance¶

Size your Postgres instance for your workload plus mirroring. Mirroring continuously captures changes and temporarily retains them until they’re copied out. This uses some additional compute and storage alongside your normal traffic. On smaller instances this matters most, so for sustained high write throughput use STANDARD_L or larger with extra disk (for example, >250 GB) to absorb load spikes without the mirror falling behind.

Source table requirements¶

To be mirrored, a source table must meet these requirements:

• Ordinary table or partitioned table. Views, materialized views, foreign tables, sequences, and other relation kinds aren’t replicable. Temporary tables are never replicated because Postgres logical replication excludes them.
• Logged (not UNLOGGED). Unlogged tables generate no WAL and can’t participate in logical replication.
• Not owned by an extension. Extension-owned tables (including the CDC metalog itself) are filtered out.
• Unique table names. The case-insensitive schema.table must be unique among mirrored tables, and the table name can’t end in $changes or $live (reserved for companion objects).

Primary key and UPDATE/DELETE support¶

Tables without a primary key are mirrored as insert-only: the CDC extension sets REPLICA IDENTITY NOTHING on them, which causes Postgres to reject UPDATE and DELETE on the source. Add a primary key before mirroring if the table needs to support UPDATE or DELETE.

Step 2: Grant permissions¶

In Snowflake, grant the postgres_mirror_admin application role to the role that will manage mirrors, and grant USAGE on the Postgres instance to the snowflake application:

GRANT APPLICATION ROLE snowflake.postgres_mirror_admin TO ROLE "my_role";
GRANT USAGE ON POSTGRES INSTANCE "my_instance" TO APPLICATION snowflake;

Replace "my_instance" with the name of your Postgres instance, and replace "my_role" with the name of the role that will manage mirrors.

Step 3: Create the mirror¶

You can create a mirror using SQL or the Snowflake UI.

Using SQL:

Use the snowflake.postgres.create_mirror procedure to specify which Postgres tables to replicate and how often to sync:

CALL SNOWFLAKE.POSTGRES.CREATE_MIRROR(
    mirror_name         => 'orders_mirror',
    postgres_instance   => 'mirror-test-sql',
    postgres_database   => 'postgres',
    target_database     => 'POSTGRESMIRRORTOSNOWFLAKE',
    postgres_tables     => ['public.devices', 'public.sensors', 'public.readings'],
    postgres_schemas    => NULL,
    refresh_interval    => '1 minute'
);

You must specify either postgres_tables or postgres_schemas, but not both. The refresh_interval defaults to '10 minutes' and accepts values like '30 seconds', '1 minute', '1 hour', or '1 day'. For full parameter details, see create_mirror.

Using the Snowflake UI:

Navigate to your Postgres instance in Snowsight and select Manage to configure mirroring. Enter a mirror name, select the source database, provide a destination database name, and set the refresh frequency.

Step 4: Verify data in Snowflake¶

Once the mirror is created and the initial sync completes, verify that the data has arrived in Snowflake:

SELECT COUNT(*) FROM POSTGRESMIRRORTOSNOWFLAKE.PUBLIC.READINGS;

The count should match the source table in Postgres. You can also check the $live view, which includes any changes that haven’t been merged yet:

SELECT COUNT(*) FROM POSTGRESMIRRORTOSNOWFLAKE.PUBLIC.READINGS$live;

Use list_mirrored_tables to check the replication state of each table. Tables show as SNAPSHOTTING during the initial copy and REPLICATING once caught up:

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

From this point on, any inserts, updates, or deletes in Postgres automatically replicate to Snowflake on the configured refresh interval. A Mirroring tab on the Postgres landing page in Snowsight shows your existing mirrors. This tab may take a few minutes to populate after your first mirror creation.

Next steps¶

For a richer end-to-end walkthrough with example data, see the Snowflake Postgres mirroring quickstart.

Cloud and region availability¶