Working with SQL and the PGD cluster v6.5.0

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:

   → WrapCopy

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

2. 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:

   → WrapCopy

   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.

3. Verify which node you're connected to in the cluster:

   → WrapCopy

   select node_name from bdr.local_node_summary;

   Output

   node_name
   -----------
   node-1

4. Exit psql, and reconnect with different settings:

   → WrapCopy

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

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

   → WrapCopy

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

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

   → WrapCopy

   \! 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:

   → WrapCopy

   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');

2. Query the data:

   → WrapCopy

   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

• Loading Data into your PGD Cluster to learn how to import data from external sources.
• Using PGD CLI to manage your PGD cluster from the command line.