Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.peerdb.io/llms.txt

Use this file to discover all available pages before exploring further.

​
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

Run the following SQL on your source 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 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}}$$
WITH (
	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;

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