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:

.. code:: bash

   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:

.. code:: bash

     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:

.. code:: bash

     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:

.. code:: sql

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

1. Exit ``psql`` , and reconnect with different settings:

.. code:: bash

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

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

.. code:: sql

     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:

.. code:: sql

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

.. code:: sql

     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:

.. code:: sql

     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 <https://www.postgresql.org/docs/current/sql.html>`_  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
----------

- :ref:`Loading data into your PGD Cluster <Loading data into your PGD Cluster>`  to learn how to import data from external sources.

- :ref:`Using PGD CLI <Using PGD CLI>`  to manage your PGD cluster from the command line.