Exploring replication with PGD v5.6

Suggest edits

Explore replication with PGD

With the cluster up and running, it's useful to run some basic checks to see how effectively it's replicating.

The following example shows one quick way to do this, but you must ensure that any testing you perform is appropriate for your use case.

Preparation

Ensure your cluster is ready to perform replication. If you haven't installed a cluster yet, use one of the quick start guides to get going.

  1. Log in to the database on the first host.
  2. Run select bdr.wait_slot_confirm_lsn(NULL, NULL);.

When the query returns, the cluster is ready.

Create data

The simplest way to test that the cluster is replicating is to log in to a node, create a table, populate it, and see the data you populated appear on a second node. On the first node:

  1. Create a table:
    CREATE TABLE quicktest ( id SERIAL PRIMARY KEY, value INT );
  2. Populate the table:
    INSERT INTO quicktest (value) SELECT random()*10000
FROM generate_series(1,10000);
  3. Monitor replication performance:
    select * from bdr.node_replication_rates;
  4. Get a sum of the value column (for checking):
    select COUNT(*),SUM(value) from quicktest;

Check data

  1. To confirm the data was successfully replicated, log in to a second node.
    1. Get a sum of the value column (for checking):
      select COUNT(*),SUM(value) from quicktest;
    2. Compare with the result from the first node.
  2. Log in to a third node.
    1. Get a sum of the value column (for checking):
      select COUNT(*),SUM(value) from quicktest;
    2. Compare with the result from the first and second nodes.

Worked example

Preparation

The cluster in this example has three data nodes: kaboom, kaftan, and kaolin. The example uses kaboom as the first node. Log in to kaboom and then into kaboom's Postgres server:

cd democluster
ssh -F ssh_config kaboom
sudo -iu enterprisedb psql bdrdb

Ensure the cluster is ready

To ensure that the cluster is ready to go, run: 

select bdr.wait_slot_confirm_lsn(NULL, NULL);
Output
 wait_slot_confirm_lsn
-----------------------

(1 row)

If the cluster is busy initializing, this query waits and returns when the cluster is ready.

Create data

On the first node (kaboom), create a table

Run:

CREATE TABLE quicktest ( id SERIAL PRIMARY KEY, value INT );
Output
CREATE TABLE

On kaboom, populate the table

This command generates a table of 10000 rows of random values: 

INSERT INTO quicktest (value) SELECT random()*10000 FROM generate_series(1,10000);
Output
INSERT 0 10000

On kaboom, monitor performance

As soon as possible, run the following command. It shows statistics about how quickly that data was replicated to the other two nodes.

select * from bdr.node_replication_rates;
Output
 peer_node_id | target_name | sent_lsn  | replay_lsn | replay_lag | replay_lag_bytes | replay_lag_size | apply_rate | catchup_interval
--------------+-------------+-----------+------------+------------+------------------+-----------------+------------+------------------
   3490219809 | kaftan      | 0/F57D120 | 0/F57D120  | 00:00:00   |                0 | 0 bytes         |       9158 | 00:00:00
   2111777360 | kaolin      | 0/F57D120 | 0/F57D120  | 00:00:00   |                0 | 0 bytes         |       9293 | 00:00:00
(2 rows)

The replay_lag values are 0, showing no lag. The LSN values are in sync, meaning the data is already replicated.

On kaboom get a checksum

Run:

select COUNT(*),SUM(value) from quicktest;

This command calculates a sum of the values from the generated data:

bdrdb=# select COUNT(*),SUM(value) from quicktest;
Output
 count  |    sum
--------+-----------
 100000 | 498884606
(1 row)

Your sum will be different because the values in the table are random numbers, but the count will be 100000.

Check data

The second host is kaftan. In another window or session, log in to kaftan's Postgres server:

cd democluster
ssh -F ssh_config kaftan
sudo -iu enterprisedb psql bdrdb

On the second node (kaftan), get a checksum

Run:

select COUNT(*),SUM(value) from quicktest;

This command gets the second node's values for the generated data:

bdrdb=# select COUNT(*),SUM(value) from quicktest;
Output
 count  |    sum
--------+-----------
 100000 | 498884606
(1 row)

Compare with the result from the first node (kaboom)

The values are identical.

You can repeat the process with the third node (kaolin), or generate new data on any node and see it replicate to the other nodes.

Log in to the third node (kaolin)

The third and last node is kaolin. In another window or session, log in to kaolin and then to kaolin's Postgres server:

cd democluster
ssh -F ssh_config kaolin
sudo -iu enterprisedb psql bdrdb

On kaolin, get a checksum

Run:

select COUNT(*),SUM(value) from quicktest;

This command gets kaolin's values for the generated data:

bdrdb=# select COUNT(*),SUM(value) from quicktest;
Output
 count  |    sum
--------+-----------
 100000 | 498884606
(1 row)

Compare the results

Compare the result from the first and second nodes (kaboom and kaftan) with the result from kaolin. The values are identical on all three nodes.

← Prev

Connecting to your database

↑ Up

Introducing PGD quick starts

Next →

Exploring failover handling with PGD