Skip to main content
Below is a 5-minute tutorial to test Streaming Query Replication from Postgres to Snowflake

​
Step 1: CREATE Postgres and Snowflake Peers

  1. CREATE Postgres PEER
  2. CREATE Snowflake PEER

​
Step 2: Create and populate tables on the Postgres PEER

Run the following SQL on your PostgreSQL peer to create and populate pgbench_history with dummy data: 
DROP TABLE IF EXISTS pgbench_history;

CREATE TABLE pgbench_history (
    tid integer,
    bid integer,
    aid integer,
    delta integer,
    mtime timestamp without time zone,
    filler character(22)
);

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime, filler)
SELECT
    (random() * 10)::int,
    (random() * 10)::int,
    (random() * 100000)::int,
    (random() * 10000 - 5000)::int,
    now() - (random() * interval '30 days'),
    lpad('', 22)
FROM generate_series(1, 10000);

CREATE INDEX pgbench_history_mtime_idx ON pgbench_history(mtime);

​
Step 3: Create pgbench_history table on the destination (snowflake) PEER

CREATE TABLE pgbench_history (tid integer, bid integer,
aid integer, delta integer, mtime timestampntz, filler text);

​
Step 4: Kick off MIRROR with 8 threads and 50000 rows per partition

CREATE MIRROR postgres_to_snowflake_tutorial
FROM postgres_peer TO snowflake_peer FOR
$$SELECT * FROM public.pgbench_history WHERE mtime BETWEEN {{.start}} AND {{.end}}$$
WITH (
	watermark_column = 'mtime',
	watermark_table_name = 'pgbench_history',
	mode = 'append',
	parallelism = 8,
	refresh_interval = 10,
	num_rows_per_partition = 50000,
	destination_table_name = 'public.pgbench_history'
);

​
Step 5: Monitor the MIRROR

You can connect to localhost:8085 to gain full visibility into the different jobs and steps that PeerDB performs under the hood to manage the MIRROR.

​
Step 6: Validate the MIRROR

In 1-2 minutes the MIRROR should complete syncing data. Now validate the data on both postgres and snowflake peers. Number of rows should match 
SELECT count(*) FROM postgres_peer.pgbench_history;
SELECT count(*) FROM snowflake_peer.public.pgbench_history;