We've got this new CLI for EDB Postgres Distributed (PGD) that makes creating clusters of PGD nodes pretty easy. PGD nodes are Postgres instances with PGD metadata, all connected and talking to each other, doing logical replication of DDL (structures) and DML (data). The PGD philosophy is also pretty cloud-native in that if something goes wrong with a node we can just delete it and recreate it and it will resync all data it missed, no problem.
So I want to show you how to create a three-node PGD cluster in AWS, the easy (but still manual) way. This isn't a full production setup but it will help you get started. And then we'll play around a bit with replication and dropping and recreating nodes in the cluster.
First, create three Ubuntu 24.04 t3.micro EC2 instances, with 8GB of disk space each. Put them in the same security group. And after creating the instances, edit the security group for these three instances to allow all inbound TCP connections from within the security group itself.
Setup on each node
Then run the following commands on each node in the cluster.
Log in or register for a free EDB account and grab your subscription token. Export that token:
export EDB_SUBSCRIPTION_TOKEN=whatever-it-isNow set up repositories for PGD and EDB Postgres Extended (EDB's distribution of Postgres):
curl -1sLf "https://downloads.enterprisedb.com/$EDB_SUBSCRIPTION_TOKEN/postgres_distributed/setup.deb.sh" | sudo -E bash
curl -1sLf "https://downloads.enterprisedb.com/$EDB_SUBSCRIPTION_TOKEN/enterprise/setup.deb.sh" | sudo -E bashUpdate apt-get and install the packages for PGD and EDB Postgres Extended.
sudo apt-get update -y
sudo apt-get install -y edb-pgd6-expanded-pgextended17As postgres user
Now switch into the postgres user. All commands from here on in the blog post should be run as the postgres user.
sudo su postgres
cd ~We're going to refer to each node in the cluster by its private IP address. Swap these IP addresses out for the private IP address of the three nodes you create.
export NODE0="172.31.38.10"
export NODE1="172.31.35.26"
export NODE2="172.31.43.94"And to simplify things for us as we go, also export the following variables.
export PGPASSWORD="some password" # you should change this
export PATH=$PATH:/usr/lib/edb-pge/17/bin/Setup on $NODE0
Set up the first PGD node (make sure you're logged into $NODE0 as the postgres user).
pgd node node0 setup \
--dsn "host=$NODE0 dbname=pgddb user=postgres" \
--listen-addr "$NODE0,localhost" \
--initial-node-count 3 \
--pgdata $HOME/pgddb \
--log-file $HOME/postgres.logfile \
--group-name "pgd"Now let's check out the cluster layout.
$ pgd --dsn "host=$NODE0 dbname=pgddb user=postgres" nodes list
Node Name | Group Name | Node Kind | Join State | Node Status
-----------+------------+-----------+------------+-------------
node0 | pgd | data | ACTIVE | UpAnd see what the CLI's health check says.
$ pgd --dsn 'host=localhost dbname=pgddb user=postgres' cluster show
# Summary
Group Name | Parent Group | Group Type | Node Name | Node Kind
------------+--------------+------------+-----------+-----------
pgd | | global | node0 | data
# Health
Check | Status | Details
-------------------+--------+-------------------------------------------------
Connections | Ok | All BDR nodes are accessible
Raft | Ok | Raft Consensus is working correctly
Replication Slots | Ok | All PGD replication slots are working correctly
Clock Drift | Ok | Clock drift is within permissible limit
Versions | Ok | All nodes are running the same PGD version
# Clock Drift
Reference Node | Node Name | Clock Drift
----------------+-----------+-------------Everything looks good! Let's set up the other two nodes.
Setup on $NODE1
Set up the second PGD node (make sure you're logged into $NODE1 as the postgres user).
pgd node "node1" setup \
--dsn "host=$NODE1 dbname=pgddb user=postgres" \
--listen-addr "$NODE1,localhost" \
--pgdata $HOME/pgddb \
--log-file $HOME/postgres.logfile \
--cluster-dsn "host=$NODE0 dbname=pgddb user=postgres" \
--cluster-name "pgd"Now on either $NODE0 or $NODE1 we can see all nodes in the cluster.
$ pgd --dsn "host=$NODE0 dbname=pgddb user=postgres" nodes list
Node Name | Group Name | Node Kind | Join State | Node Status
-----------+------------+-----------+------------+-------------
node0 | pgd | data | ACTIVE | Up
node1 | pgd | data | ACTIVE | UpGreat! Let's add the final node, $NODE2.
Setup on $NODE2
Set up the second PGD node (make sure you're logged into $NODE2 as the postgres user).
pgd node "node2" setup \
--dsn "host=$NODE2 dbname=pgddb user=postgres" \
--listen-addr "$NODE2,localhost" \
--pgdata $HOME/pgddb \
--log-file $HOME/postgres.logfile \
--cluster-dsn "host=$NODE0 dbname=pgddb user=postgres" \
--cluster-name "pgd"Now on any node in the cluster, run the pgd nodes list command and we should see all three!
$ pgd --dsn "host=$NODE0 dbname=pgddb user=postgres" nodes list
Node Name | Group Name | Node Kind | Join State | Node Status
-----------+------------+-----------+------------+-------------
node0 | pgd | data | ACTIVE | Up
node1 | pgd | data | ACTIVE | Up
node2 | pgd | data | ACTIVE | UpReplicate DDL and DML
Now that we've got this cluster set up we can really take PGD for a spin. Let's create an orders table on $NODE0 and insert a million rows into it.
It doesn't matter where you run this, the DSN means it will execute on $NODE0.
$ psql "host=$NODE0 dbname=pgddb user=postgres" -c "
BEGIN;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
customer_id INT,
order_id BIGSERIAL,
amount_pennies BIGINT,
PRIMARY KEY (customer_id, order_id)
);
INSERT INTO orders (customer_id, amount_pennies)
SELECT
random() * 9 + 1,
random() * 100000 + 1
FROM
generate_series(1, 1_000_000);
COMMIT;"
NOTICE: table "orders" does not exist, skipping
BEGIN
DROP TABLE
CREATE TABLE
INSERT 0 1000000
COMMITNow we can query that table on each node in the cluster.
$ psql "host=$NODE0 dbname=pgddb user=postgres" -c "SELECT count(1) FROM orders"
count
---------
1000000
(1 row)
$ psql "host=$NODE1 dbname=pgddb user=postgres" -c "SELECT count(1) FROM orders"
count
---------
1000000
(1 row)
$ psql "host=$NODE2 dbname=pgddb user=postgres" -c "SELECT count(1) FROM orders"
count
---------
1000000
(1 row)The table was created on all nodes and the data was replicated!
Remove and recreate $NODE2
Let's say $NODE2 was having some issues for whatever reason. We want to re-provision it. No problem! First tell the rest of the cluster to remove this node.
$ pgd --dsn 'host=$NODE0 dbname=pgddb user=postgres' node node2 part
Starting a part node operation for node: node2
This may take some time, please wait...
NOTICE: node node2 has been removed from the BDR groupNow on $NODE2 we can just stop Postgres, delete the whole Postgres data directory, and re-run the pgd node setup command from above.
$ pg_ctl -D $HOME/pgddb -l $HOME/postgres.logfile stop
waiting for server to shut down.... done
server stopped
$ rm -rf $HOME/postgres.logfile $HOME/pgddbLet's query the cluster first to see this node is gone.
$ pgd --dsn "host=$NODE0 dbname=pgddb user=postgres" nodes list
Node Name | Group Name | Node Kind | Join State | Node Status
-----------+------------+-----------+------------+-------------
node0 | pgd | data | ACTIVE | Up
node1 | pgd | data | ACTIVE | UpNow let's rebuild $NODE2.
$ pgd node "node2" setup \
--dsn "host=$NODE2 dbname=pgddb user=postgres" \
--listen-addr "$NODE2,localhost" \
--pgdata $HOME/pgddb \
--log-file $HOME/postgres.logfile \
--cluster-dsn "host=$NODE0 dbname=pgddb user=postgres" \
--cluster-name "pgd"And check out the nodes list again.
$ pgd --dsn "host=$NODE0 dbname=pgddb user=postgres" nodes list
Node Name | Group Name | Node Kind | Join State | Node Status
-----------+------------+-----------+------------+-------------
node0 | pgd | data | ACTIVE | Up
node1 | pgd | data | ACTIVE | Up
node2 | pgd | data | ACTIVE | UpAnd we're back! As are all the tables and data that existed in the cluster before we rebuilt $NODE2.
$ psql "host=$NODE2 dbname=pgddb user=postgres" -c "SELECT count(1) FROM orders"
count
---------
1000000
(1 row)By the way, you can also use this rebuild process to do rolling upgrades for the cluster. Drop a node from the cluster, provision a new node with a newer version of Postgres and PGD, and join it to the cluster. Repeat for each node in the cluster until all nodes are running the same newer version of Postgres and PGD.