Step 8 - Using PGD CLI v5

Suggest edits

Using PGD CLI

The PGD CLI command uses a configuration file to work out which hosts to connect to. There are options that allow you to override this to use alternative configuration files or explicitly point at a server, but by default PGD CLI looks for a configuration file in preset locations.

The connection to the database is authenticated in the same way as other command line utilities, like the psql command, are authenticated.

Unlike other commands, PGD CLI doesn't interactively prompt for your password. Therefore, You must pass your password using one of the following methods:

  • Adding an entry to your .pgpass password file, which includes the host, port, database name, user name, and password.
  • Setting the password in the PGPASSWORD environment variable.
  • Including the password in the connection string.

We recommend the first option, as the other options don't scale well with multiple database clusters, or they compromise password confidentiality.

Configuring and connecting PGD CLI

  • Ensure PGD-CLI is installed
    • If PGD CLI has already been installed move to the next step.
    • For any system, repeat the configure repositories step on that system.
    • Then run the package installation command appropriate for that platform.
      • RHEL and derivatives: sudo dnf install edb-pgd5-cli
      • Debian, Ubuntu and derivatives: sudo apt-get install edb-pgd5-cli
  • Create a configuration file
    • YAML file which specifies the cluster and endpoints the PGD CLI application should use.
  • Install the configuration file.
    • Copy the YAML configuraiton file to a default config directory /etc/edb/pgd-cli/ as pgd-cli-config.yml.
    • Repeat this on any system where you want to run PGD CLI.
  • Run pgd-cli.

Use PGD CLI to explore the cluster

  • Check the health of the cluster with the check-health command.
  • Show the nodes in the cluster with the show-nodes command.
  • Show the proxies in the cluster with the show-proxies command.
  • Show the groups in the cluster with the show-groups command.
  • Set a group option with the set-group-options command.
  • Swith write leader with the switchover command

We go into more details of these command in the worked example below.

Also consult the PGD CLI documentation for details of other configuration options and a full command reference.

Worked example

Ensure PGD CLI is installed

In this worked example, we will be configuring and using PGD CLI on host-one, where we've already installed Postgres and PGD. There is no need to install PGD CLI again.

Create a configuration file

The PGD CLI configuration file is similar to the PGD proxy configuration filer. It is a YAML file which contains a cluster object. This has two properties:

The name of the PGD cluster's top-level group (as name). An array of endpoints of databases (as endpoints).

cluster:
  name: pgd
  endpoints:
    - host=host-one dbname=bdrdb port=5444
    - host=host-two dbname=bdrdb port=5444
    - host=host-three dbname=bdrdb port=5444

Note that the endpoints in this example specify port=5444. This is necessary for EDB Postgres Advanced Server instances. For EDB Postgres Extended and Community PostgreSQL, this can be omitted.

Install the configuration file

Create the PGD CLI configuration directory.

sudo mkdir -p /etc/edb/pgd-cli

Then write the configuration to the pgd-cli-config.yml file in the /etc/edb/pgd-cli directory.

For our example, this could be run on host-one to create the file.

cat <<EOF | sudo tee /etc/edb/pgd-cli/pgd-cli-config.yml
cluster:
  name: pgd
  endpoints:
    - host=host-one dbname=bdrdb port=5444
    - host=host-two dbname=bdrdb port=5444
    - host=host-three dbname=bdrdb port=5444
EOF

This process can be repeated on any system where you need to use PGD CLI.

Run PGD CLI

With the configuration file in place, and logged is as the enterprisedb system user, we can run pgd-cli. For example, we can use the show-nodes command to list the nodes in our cluster and their status.

pgd show-nodes
Output
Node       Node ID    Group Type Current State Target State Status Seq ID
----       -------    ----- ---- ------------- ------------ ------ ------
node-one   2824718320 dc1   data ACTIVE        ACTIVE       Up     1
node-three 1954860017 dc1   data ACTIVE        ACTIVE       Up     3
node-two   2299992455 dc1   data ACTIVE        ACTIVE       Up     2

Using PGD CLI to explore the cluster

Once configured, you can use PGD CLI to get PGD level views of the cluster.

Check the health of the cluster

The check-health command provides a quick way to view the health of the cluster:

pgd check-health
Output
Check      Status Message
-----      ------ -------
ClockSkew  Ok     All BDR node pairs have clockskew within permissible limit
Connection Ok     All BDR nodes are accessible
Raft       Ok     Raft Consensus is working correctly
Replslots  Ok     All BDR replication slots are working correctly
Version    Ok     All nodes are running same BDR versions

Show the nodes in the cluster

As previously seen, the show-nodes command lists the nodes in the cluster:

pgd show-nodes
Output
Node       Node ID    Group Type Current State Target State Status Seq ID
----       -------    ----- ---- ------------- ------------ ------ ------
node-one   2824718320 dc1   data ACTIVE        ACTIVE       Up     1
node-three 1954860017 dc1   data ACTIVE        ACTIVE       Up     3
node-two   2299992455 dc1   data ACTIVE        ACTIVE       Up     2

Note that this view shows which group the node is a member of and its current status. To find out what versions of PGD and Postgres are running on the nodes, use show-version:

pgd show-version
Output
Node       BDR Version Postgres Version
----       ----------- ----------------
node-one   5.3.0       16.1.0
node-three 5.3.0       16.1.0
node-two   5.3.0       16.1.0

Show the proxies in the cluster

You can view the configured proxies, with their groups and ports using show-proxies:

pgd show-proxies
Output
Proxy           Group Listen Addresses Listen Port
-----           ----- ---------------- -----------
pgd-proxy-one   dc1   [0.0.0.0]        6432
pgd-proxy-three dc1   [0.0.0.0]        6432
pgd-proxy-two   dc1   [0.0.0.0]        6432

Show the groups in the cluster

Finally, the show-groups command for PGD CLI shows which groups are configured, and more:

pgd show-node_groups
Output
Group Group ID   Type   Parent Group Location Raft Routing Write Leader
----- --------   ----   ------------ -------- ---- ------- ------------
pgd   1850374637 global                       true false
dc1   4269540889 data   pgd                   true true    node-one

This shows:

  • the groups
  • their types
  • their parent group
  • the groups location
  • whether Raft consensus is enabled
  • whether the group is routing connections
  • and if it is, which node is write leader for that

The location is descriptive metadata, and so far we haven't set it. Let's use PGD CLI to rememdy that.

Set a group option

We can set group options using PGD CLI too, using the set-group-options command. This requires a --group-name flag to set the group this change should affect and a --option flag with the setting to change. If we wanted to set the dc1 group's location to London, we would run:

pgd set-group-options --group-name dc1 --option "location=London"
Output
group options updated successfully

And we can verify that with show-groups

pgd show-node_groups
Output
Group Group ID   Type   Parent Group Location Raft Routing Write Leader
----- --------   ----   ------------ -------- ---- ------- ------------
pgd   1850374637 global                       true false
dc1   4269540889 data   pgd          London   true true    node-one

Switching write leader

If we need to change write leader in as group, to enable maintence on a host, PGD CLI offers the switchover command. It takes a --group-name flag with the group the node exists in and a --node-name flag with the name of the node to swith to. We can then run:

pgd switchover --group-name dc1 --node-name node-two
Output
switchover is complete

And we can verify that with show-groups:

pgd show-groups
Output
Group Group ID   Type   Parent Group Location Raft Routing Write Leader
----- --------   ----   ------------ -------- ---- ------- ------------
pgd   1850374637 global                       true false
dc1   4269540889 data   pgd          London   true true    node-two

More details on the available commands in PGD CLI are available in the PGD CLI command reference.

← Prev

Step 7 - Configure proxies