Step 1: CREATE Two Postgres Peers

There is a guide available for creating a PostgreSQL peer here: CREATE Postgres PEER

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

Below script helps creates and populate pgbench_history with dummy data on your PostgreSQL peer.

curl -O
chmod +x
# "postgres://user:password@hostname:5432/dbname"
# is the connection string of your source PostgreSQL peer.
./ "postgres://user:password@hostname:5432/dbname"

Step 3: Create pgbench_history table on the destination 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 batch size of 10 seconds

CREATE MIRROR postgres_to_postgres_tutorial
FROM postgres_peer_source TO postgres_peer_destination FOR
$$SELECT * FROM public.pgbench_history WHERE mtime BETWEEN {{.start}} AND {{.end}}$$
	watermark_column = 'mtime',
	watermark_table_name = 'pgbench_history',
	mode = 'append',
	parallelism = 8,
	refresh_interval = 10,
	destination_table_name = 'public.pgbench_history',
  num_rows_per_partition = 10000

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 peers. Number of rows should match on both sides.

SELECT count(*) FROM postgres_peer_1.pgbench_history;
SELECT count(*) FROM postgres_peer_2.pgbench_history;


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