SQL Server to PostgreSQL
Step 1: CREATE SQL Server and PostgreSQL Peers
Step 2: Create the table on the destination (postgres) PEER
CREATE TABLE source_table (watermark_column...)
Step 3: Kick off MIRROR with 8 threads and batch size of 10 seconds
CREATE MIRROR postgres_to_sqlserver_tutorial
FROM sqlserver_peer TO postgres_peer FOR
$$SELECT * FROM source_table WHERE <watermark_column> BETWEEN {{.start}} AND {{.end}}$$
WITH (
watermark_column = '<watermark-column>',
watermark_table_name = '<watermark_table_name>',
mode = 'append',
parallelism = 8,
refresh_interval = 10,
destination_table_name = 'public.pgbench_history',
num_rows_per_partition = 10
);
Step 4: 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 5: Validate the MIRROR
In 1-2 minutes the MIRROR should complete syncing data. Now validate the data on both Postgres and SQL Server peers. Number of rows should match for both sides.
Step 5: DROP MIRROR
To make it easy in your development and test environments, PeerDB also introduces the DROP MIRROR command. DROP MIRROR drops all the underlying objects that CREATE MIRROR generates. More details are available in this PR.
-- drop the mirror
DROP MIRROR <mirror_name>;