Step 6 - Checking the cluster v4

Suggest edits

Checking the cluster

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

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

  • Preparation
    • Ensure the cluster is ready.
      • Log into the database on host-one/node-one.
      • Run select bdr.wait_slot_confirm_lsn(NULL, NULL);.
      • When the query returns, the cluster is ready.
  • Create data The simplest way to test the cluster is replicating is to log into one node, create a table, and populate it.
    • On node-one, create a table:
      CREATE TABLE quicktest ( id SERIAL PRIMARY KEY, value INT );
    • On node-one, populate the table:
      INSERT INTO quicktest (value) SELECT random()*10000 FROM generate_series(1,10000);
    • On node-one, monitor performance:
      select * from bdr.node_replication_rates;
    • On node-one, get a sum of the value column (for checking):
      select COUNT(*),SUM(value) from quicktest;
  • Check data
    • Log into node-two. Log into the database on host-two/node-two.
    • On node-two, get a sum of the value column (for checking):
      select COUNT(*),SUM(value) from quicktest;
    • Compare with the result from node-one.
    • Log into node-three. Log into the database on host-three/node-three.
    • On node-three, get a sum of the value column (for checking):
      select COUNT(*),SUM(value) from quicktest;
    • Compare with the result from node-one and node-two.

Worked example

Preparation

Log into the host-one Postgres server:

ssh admin@host-one
sudo -iu enterprisedb psql bdrdb

This is your connection to PGD's node-one.

Ensure the cluster is ready

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

select bdr.wait_slot_confirm_lsn(NULL, NULL)

This query will block while the cluster is busy initializing and return when the cluster is ready.

In another window, log into the host-two Postgres server:

ssh admin@host-two
sudo -iu enterprisedb psql bdrdb

Create data

On node-one create a table

Run:

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

On node-one populate the table

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

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

On node-one monitor performance

As soon as possible, run:

select * from bdr.node_replication_rates;

This command returns statistics on how quickly that data was replicated to the other two nodes:

bdrdb=# select * from bdr.node_replication_rates;
 peer_node_id | target_name | sent_lsn  | replay_lsn | replay_lag | replay_lag_bytes | replay_lag_size | apply_rate | catchup_interv
al
--------------+-------------+-----------+------------+------------+------------------+-----------------+------------+---------------
---
   1954860017 | node-three  | 0/DDAA908 | 0/DDAA908  | 00:00:00   |                0 | 0 bytes         |      13682 | 00:00:00
   2299992455 | node-two    | 0/DDAA908 | 0/DDAA908  | 00:00:00   |                0 | 0 bytes         |      13763 | 00:00:00
(2 rows)

And it's already replicated.

On node-one get a checksum

To get some values from the generated data, run:

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

This command returns:

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

Check data

Log into host-two's Postgres server.

ssh admin@host-two
sudo -iu enterprisedb psql bdrdb

This is your connection to PGD's node-two.

On node-two get a checksum

To get node-two's values for the generated data, run:

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

This command returns:

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

Compare with the result from node-one

When you compare with the result from node-one, the values will be identical.

You can repeat the process with node-three or generate new data on any node and see it replicate to the other nodes.

Log into host-three's Postgres server

ssh admin@host-two
sudo -iu enterprisedb psql bdrdb

This is your connection to PGD's node-three.

On node-three get a checksum

To get the node-three values for the generated data, run:

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

This command returns:

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

Compare with the result from node-one and node-two

When you compare the results, the values will be identical.

← Prev

Step 5 - Creating the PGD cluster

↑ Up

Deploying PGD 4 manually

Next →

Step 7 - Using PGD CLI