Tutorial: Deploying pgEdge Distributed Postgres with Docker

pgEdge Distributed Postgres supports deploying Postgres 15, 16, and 17 in any modern container platform including:

• Docker Desktop (for Linux, MacOS, and Windows)
• Docker Engine (for Linux)
• Kubernetes

After installing your container environment, you can use the following command to download a yaml file that creates a replicating multi-master cluster with two containers on your localhost, each running a Postgres instance:

curl -O https://downloads.pgedge.com/platform/examples/compose/docker-compose.yaml

Then, deploy the containers:

docker compose up -d

Demonstrating Replication and Exercising the Database

To perform the following exercises, we recommend opening two terminal sessions, each connected to a different container and node with the psql client.

• Node 1 is named postgres-n1 and listens on port 6432.
• Node 2 is named postgres-n2 and listens on port 6433.
• The initialized Postgres database is named example_db
• The database superuser is named admin

In the commands that follow, we'll be referring to the nodes as node 1 and node 2. First, open a psql session with node 1 in one Terminal window with the command:

docker compose exec postgres-n1 psql -U admin example_db

psql (17.6)
Type "help" for help.
 
example_db=>

Open a psql session with node 2 in the other Terminal window with the command:

docker compose exec postgres-n2 psql -U admin example_db

psql (17.6)
Type "help" for help.
 
example_db=> 

On node 1, create a table on the first node with the command:

CREATE TABLE example (id int primary key, data text);

INFO:  DDL statement replicated.
CREATE TABLE
example_db=> 

On node 2, add a row to the table:

INSERT INTO example (id, data) values (1, 'Hello');

INSERT 0 1
example_db=> 

Query node 1 to see that the new row has replicated back to node 1:

SELECT * FROM example;

example_db=> SELECT * FROM example;
 id | data  
----+-------
  1 | Hello
(1 row)

Adding the Northwind Data Samples

You can download and install the northwind schema and tables to experiment with replication on a larger dataset. First, exit psql:

example_db=> quit

Then, invoke the following command to download and install the northwind schema:

curl https://downloads.pgedge.com/platform/examples/northwind/northwind.sql | docker compose exec -T postgres-n1 psql -U admin example_db

On node 2, query the northwind.database table:

docker compose exec postgres-n2 psql -U admin example_db -c "SELECT * FROM northwind.shippers"

As each table is downloaded and installed, the progress is echoed to the screen:

100  224k  100  224k    0     0   209k      0  0:00:01  0:00:01 --:--:--  209k
INSERT 0 830
INSERT 0 29
INSERT 0 77
INSERT 0 2155
INSERT 0 51
INFO:  DDL statement replicated.
ALTER TABLE
INFO:  DDL statement replicated.
ALTER TABLE
...

The data added on node 1 is replicated and made available via node 2. You can reconnect to the database on node 1:

docker compose exec postgres-n1 psql -U admin example_db
psql (17.6)
Type "help" for help.
 
example_db=>\dt northwind.*
                 List of relations
  Schema   |          Name          | Type  | Owner 
-----------+------------------------+-------+-------
 northwind | categories             | table | admin
 northwind | customer_customer_demo | table | admin
 northwind | customer_demographics  | table | admin
 northwind | customers              | table | admin
 northwind | employee_territories   | table | admin
 northwind | employees              | table | admin
 northwind | order_details          | table | admin
 northwind | orders                 | table | admin
 northwind | products               | table | admin
 northwind | region                 | table | admin
 northwind | shippers               | table | admin
 northwind | suppliers              | table | admin
 northwind | territories            | table | admin
 northwind | us_states              | table | admin
(14 rows)
 

Query node 2 to find the schema replicated:

\dt northwind.*
                 List of relations
  Schema   |          Name          | Type  | Owner 
-----------+------------------------+-------+-------
 northwind | categories             | table | admin
 northwind | customer_customer_demo | table | admin
 northwind | customer_demographics  | table | admin
 northwind | customers              | table | admin
 northwind | employee_territories   | table | admin
 northwind | employees              | table | admin
 northwind | order_details          | table | admin
 northwind | orders                 | table | admin
 northwind | products               | table | admin
 northwind | region                 | table | admin
 northwind | shippers               | table | admin
 northwind | suppliers              | table | admin
 northwind | territories            | table | admin
 northwind | us_states              | table | admin
(14 rows)

Connecting with a Postgres Client

You can use your choice of Postgres client (like psql or pgAdmin) to connect and query the Docker containers; each container is accessed with a libpg-style connection string:

Node 1: host=localhost port=6432 user=admin password=password dbname=example_db

Node 2: host=localhost port=6433 user=admin password=password dbname=example_db

Cleaning Up

To find the names of your containers, use the following command:

docker ps -a

After finding the container names (in the NAMES column), you can stop and then delete a container with the commands:

docker stop container_name

docker rm container_name

These commands leave your environment available for another container deployment.