Exploring conflict handling with PGD v5
In a multi-master architecture like PGD, conflicts happen. PGD is built to handle them.
A conflict can occur when one database node has an update from an application to a row and another node has a different update to the same row. This type of conflict is called a row-level conflict. Conflicts aren't errors. Resolving them effectively is core to how Postgres Distributed maintains consistency.
The best way to handle conflicts is to prevent them! Use PGD's Always-on architecture with proxies to ensure that your applications write to the same server in the cluster.
When conflicts occur, though, it's useful to know how PGD resolves them, how you can control that resolution, and how you can find out that they're happening. Row insertion and row updates are two actions that can cause conflicts.
To see how it works, you need to open a command line view of all the servers.
Your quick start configuration
This exploration assumes that you created your PGD cluster using the quick start for Docker, the quick start for AWS, or the quick start for Linux hosts.
At the end of each quick start, you'll have a cluster with four nodes and these roles:
Host name | Host role |
---|---|
kaboom | PGD data node and pgd-proxy co-host |
kaftan | PGD data node and pgd-proxy co-host |
kaolin | PGD data node and pgd-proxy co-host |
kapok | Barman backup node |
You'll use these hostnames throughout this exercise.
Installing xpanes
Xpanes optional
We recommend the xpanes
utility for this exercise. It allows you to easily switch between multiple terminal sessions. If you prefer to use multiple terminals, tmux or another terminal multiplexer, you can do so. Just make sure you can easily switch between multiple terminal sessions.
You'll use xpanes
, a utility that allows you to quickly create multiple terminal sessions that you can easily switch between. It isn't installed by default, so you'll have to install it. Start by connecting to the kaboom node with ssh:
If you're running the quick start on Docker, you'll be using Rocky Linux, a Red Hat derivative. To perform the xpanes install, run:
If you're running the quick start on AWS, you'll be using Debian Linux. To perform the xpanes install, run:
Connecting to four servers
You need to be logged in as the enterprisedb user to allow authentication to work:
Then, run the following command to connect to three database servers and a proxy server:
xpanes takes the command after -c
and uses the values in the arguments that follow to create a command to run. That means that, after you run it, there will be four panes. Three panes will be connected to the database nodes kaboom, kaftan, and kaolin on port 5444. One will be connected to the pgd-proxy running on kaboom on port 6432. Each one will be logged into the database as enterprisedb.
Press Control-b followed by q to briefly display the numeric values for each pane.
To switch the focus between the panes, you can use Control-b and the cursor keys to navigate between them. Or you can use Control-b followed by q and the number of the pane you want to focus on. We'll show both ways.
Move to the bottom-left pane using Control-b ↓ Control-b → or Control-b q 3**.
Preparing for conflicts
To make a conflict, you first need a simple table. In the pane that currently has focus, enter:
Monitoring conflicts
In the pane that currently has focus, enter:
The select
command displays the conflict history for the cluster. The \watch 1
command is a psql command that reruns the preceding command every second.
You are now ready to generate a conflict.
Creating a conflict
The most basic form of conflict is when an insert happens to a table on two different nodes and both have the same primary key. You can now create that scenario and observe it.
Move to the top-left pane using Control-b ↑ Control-b ← or Control-b q 0. This pane is the kaboom node. Start a transaction there, and insert a row:
Next, move to the top-right pane using Control-b → or Control-b q 1. This pane is the kaftan node. Here, you'll also start a transaction and insert into the same row with different data:
You now have two transactions open on different servers, with an insert operation already performed successfully. You need to commit both transactions at this point:
- Use Control-b ← or Control-b q 0, and then enter
commit;
. - Use Control-b