Working with SQL and the PGD cluster

Working with SQL and the PGD cluster#

The first step in working with your PGD cluster is to connect to it using SQL. Connect using the psql command-line interface or any other SQL client that supports PostgreSQL.

The psql command is already installed and configured. Log in to the host-1 container to run commands within the cluster:

docker compose exec host-1 bash

The command gives you a shell inside the host-1 container where you can run PGD and SQL commands against the PGD cluster.

Connecting within the PGD Cluster#

Unless you’re performing maintenance tasks, you usually connect to the cluster using Connection Manager, which runs on TCP port 6432 of all the hosts in the cluster.

  1. Connect to the write leader node in the cluster:

psql -h <host> -p 6432 -U <username> <database>

  1. As the cluster has no users apart from the postgres superuser and only one replicated database (pgddb ), connect to the cluster using the following command:

psql -h host-1 -p 6432 -U postgres pgddb

This command connects to Connection Manager running on the host-1 container on port 6432, which is then routed to the write leader node in the cluster. Replace host-1 with the name of any host in the cluster, as they all run Connection Manager.

  1. Verify which node you’re connected to in the cluster:

select node_name from bdr.local_node_summary;
__OUTPUT__
node_name
-----------
node-1

  1. Exit psql , and reconnect with different settings:

psql -h host-2 -p 6432 -U postgres pgddb

  1. Verify you’re connected to the node-1 node in the cluster:

select node_name from bdr.local_node_summary;
node_name
-----------
node-1

  1. Connection Manager is routing the connection to the write leader node in the cluster, which is node-1 . Confirm the changes:

\! pgd group group-1 show --summary
__OUTPUT__
Group Property    | Value
-------------------+---------
Group Name        | group-1
Parent Group Name | pgd
Group Type        | data
Write Leader      | node-1
Commit Scope      |

Tip

Use the ! command in psql to run shell commands directly from within the psql session. !!!

Working with SQL#

Once that you’re connected to the cluster, start working with SQL commands. Create tables, insert data, and run queries like you would in a regular PostgreSQL database.

  1. Create a table and insert some data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);
INSERT INTO users (name, email) VALUES
(Alice, alice@example.com),
(Bob, bob@example.com);

  1. Query the data:

SELECT * FROM users;
__OUTPUT__
id |  name  |        email
----+--------+---------------------
  2 | Alice  |      alice@example.com
  3 | Bob    |      bob@example.com
(2 rows)

You can also run more complex queries, join tables, and use all the features of PostgreSQL. Refer to the PostgreSQL documentation for more information on SQL syntax and commands.

Replicating data across the cluster#

What’s important about PGD is that those SQL commands are replicated across the cluster. PGD takes care of the replication for you. For example, that serial key is automatically converted to a globally unique key across the cluster, so you can insert data on any node in the cluster and it’s replicated to all other nodes without conflicts or duplicates.

Next Steps#

Contents