What did pgEdge automate in the db?
Register each db as a Spock Node.
N1 - ap-southeast-2a
N2 - eu-west-1a
N3 - ca-central-1a
Create a Replication Set in each node named fastly_repset.
In each node, create a subscription to the other two nodes.
sub_n1n2, sub_n1n3
sun_n2n1, sub_n2n3
sub_n3n1, sub_n3n2
Publication- A publication can be defined on any physical replication primary. The node where a publication is defined is referred to as publisher. A publication is a set of changes generated from a table or a group of tables, and might also be described as a change set or replication set. Each publication exists in only one database.
Subscription- A subscription is the downstream side of logical replication. The node where a subscription is defined is referred to as the subscriber. A subscription defines the connection to another database and set of publications (one or more) to which it wants to subscribe.
Slot- Each subscription will receive changes via one replication slot. Additional table synchronization slots are normally transient, created internally to perform initial table synchronization and dropped automatically when they are no longer needed.
Create and load data a small amount of data with pgBench
pgbench_branches
pgbench_tellers
pgbench_accounts
pgbench_history
Update three fields to use Spock Conflict-Free Delta-Apply
ALTER TABLE pgbench_accounts ALTER COLUMN abalance SET (LOG_OLD_VALUE=true);
ALTER TABLE pgbench_branches ALTER COLUMN bbalance SET (LOG_OLD_VALUE=true);
ALTER TABLE pgbench_tellers ALTER COLUMN tbalance SET (LOG_OLD_VALUE=true);
Add the first three tables to the replication set on each node. pgbench_history is not added to this replication set as it does not have a primary key.
Add the replication set to each subscription.
Demo of Conflict Resolution and Conflict Avoidance
you can run pgBench for one minute with the following command on each node:
pgbench -R 100 -T 60 -n demo
Test on each node with:
SELECT SUM(abalance) FROM pgbench_accounts;
Update values within transactions to view conflict resolution capabilities.
SELECT * FROM spock.resolutions LIMIT 1;
Conflict Resolution
In case the node is subscribed to multiple providers, or when local writes happen on a subscriber, conflicts can arise for the incoming changes. These are automatically detected and can be acted on depending on the configuration.
- apply_remote - always apply the change that’s conflicting with local data
- keep_local - keep the local version of the data and ignore the conflicting change that is coming from the remote node
- last_update_wins - the version of data with newest commit timestamp will be kept (this can be either local or remote version)
- first_update_wins - the version of the data with oldest timestamp will be kept (this can be either local or remote version)