Using PGD CLI v6.1.0

Suggest edits

The PGD CLI is a powerful command line interface for managing your PGD cluster. It can be used to perform a variety of tasks, including:

  • Checking the health of the cluster
  • Listing the nodes in the cluster
  • Listing the groups in the cluster
  • Setting group options
  • Switching the write leader

If you have used the installation guide to install PGD, you will have already installed PGD CLI and used it to create the cluster.

Using PGD CLI

The PGD CLI command uses a configuration file to work out the 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 was already 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-pgd6-cli
      • Debian, Ubuntu, and derivatives: sudo apt-get install edb-pgd6-cli
  • Create a configuration file.
    • This is a YAML file that specifies the cluster and endpoints for PGD CLI to use.
  • Install the configuration file.
    • Copy the YAML configuration file to a default config directory /etc/edb/pgd-cli/ as pgd-cli-config.yml.
    • Repeat this process 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 cluster show --health command.
  • Show the nodes in the cluster with the nodes list command.
  • Show the groups in the cluster with the groups list command.
  • Set a group option with the group set-option command.
  • Switch write leader with the group set-leader command.

For more details about these commands, see the worked example that follows.

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, you configure and use PGD CLI on host-1, where you've already installed Postgres and PGD. You don't need to install PGD CLI again.

(Optionally) Create a configuration file

The PGD CLI configuration file is a YAML file that 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-1 dbname=pgddb port=5444
    - host=host-2 dbname=pgddb port=5444
    - host=host-3 dbname=pgddb 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, you can omit this.

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 this example, you can run this on host-1 to create the file:

cat <<EOF | sudo tee /etc/edb/pgd-cli/pgd-cli-config.yml
cluster:
  name: pgd
  endpoints:
    - host=host-1 dbname=pgddb port=5444
    - host=host-2 dbname=pgddb port=5444
    - host=host-3 dbname=pgddb port=5444
EOF

You can repeat this process on any system where you need to use PGD CLI.

Running PGD CLI

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

pgd nodes list
Output
Node Name Group Name   Node Kind Join State Node Status
---------  ----------- --------- ---------- -----------
node-1     group-1         data      ACTIVE     Up
node-2     group-1         data      ACTIVE     Up
node-3     group-1         data      ACTIVE     Up

Using PGD CLI to explore the cluster

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

Check the health of the cluster

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

pgd cluster show --health
Output
Check             Status Details
----------------- ------ -----------------------------------------------
Connections       Ok     All BDR nodes are accessible
Raft              Ok     Raft Consensus is working correctly
Replication Slots Ok     All PGD replication slots are working correctly
Clock Skew        Ok     Clock drift is within permissible limit
Versions          Ok     All nodes are running the same PGD version

Show the nodes in the cluster

As previously seen, the nodes list command lists the nodes in the cluster:

pgd nodes list
Output
Node Name  Group Name  Node Kind Join State Node Status
---------- ----------- --------- ---------- -----------
node-1     group-1         data      ACTIVE     Up
node-2     group-1         data      ACTIVE     Up
node-3     group-1         data      ACTIVE     Up

This view shows the 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 nodes list --versions:

pgd nodes list --versions
Output
Node Name  BDR Version                  Postgres Version
---------- ---------------------------  --------------------------------
node-1     5.7.0 (snapshot e2534db6d)   16.6 (Debian 16.6-1EDB.bullseye)
node-2     5.7.0 (snapshot e2534db6d)   16.6 (Debian 16.6-1EDB.bullseye)
node-3     5.7.0 (snapshot e2534db6d)   16.6 (Debian 16.6-1EDB.bullseye)

Show the groups in the cluster

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

pgd groups list
Output
Group Name   Parent Group Name Group Type Nodes
------------ ----------------- ---------- -----
pgd                            global     0
group-1      pgd               data       3

This command shows:

  • The groups
  • Their types
  • Their parent group
  • The number of nodes in each group

Set a group option

You can set group options using PGD CLI, too, using the group set-option command. If you wanted to set the group-1 group's location to London, you would run:

pgd group group-1 set-option location London
Output
Status Message
------ -----------------------------
OK     Command executed successfully

You can verity the new location using the group get-option command:

pgd group group-1 get-option location
Output
Option Name Option Value
----------- ------------
location    London

Set the write leader

If you need to change write leader in a group, to enable maintenance on a host, PGD CLI offers the group set-leader command. You enter a group name after group and the name of the node you want to switch to after set leader:

pgd group group-1 set-leader node-2
Output
Status Message
------ -----------------------------
OK     Command executed successfully

You can verify the write leader using the group show command with the --summary option:

pgd group group-1 show --summary
Output
Group Property    Value
----------------- ------------
Group Name        group-1
Parent Group Name pgd
Group Type        data
Write Leader      node-2
Commit Scope

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

PGD Essential constraints

Verify cluster architecture

The pgd cluster verify command will display a Warning if the cluster does not meet the below listed PGD Essential constraints.

  • A PGD Essential cluster must have at most three data nodes.
  • A PGD Essential cluster must have routing enabled for global group only.
pgd cluster verify --arch
Output
 Check                       | Status  | Groups
-----------------------------+---------+--------
 Cluster has data nodes      | Ok      |
 Max data nodes in a cluster | Warning | dc-1
 Witness nodes per group     | Ok      |
 Witness-only groups         | Ok      |
 Data nodes per group        | Ok      |
 Routing enabled groups      | Warning | dc-1
 Empty groups                | Ok      |
 Nodes have node kind set    | Ok      |

PGD compatibility assessment

The pgd assess command will not perform the below listed assessments for a Postgres server for PGD Essential compatibility.

  • Tables with Multiple Unique Indexes
  • Row Level Lock Usage
  • Lock Table Usage
  • Listen Notify Usage

The command output for a PGD Essential cluster will look like below.

pgd assess --dsn "host=pgd-a2 port=5432 dbname=pgddb user=postgres "
Output
 Assessment                   | Result                     | Details
------------------------------+----------------------------+-------------------------------------------------------
 Multiple Databases           | Compatible                 | Found only one user database
 Materialized Views           | Compatible                 | No materialized views found
 EPAS Queue Tables            | Compatible                 | No EPAS Queue Tables found
 DDL Command Usage            | Requires workload analysis | Cannot be checked automatically at this time
 Advisory Lock Usage          | Potentially compatible     | No advisory lock commands found in pg_stat_statements
 Large Objects                | Compatible                 | No large objects found
 Trigger/Reference Privileges | Compatible                 | No triggers with incompatible privileges found

Commit Scope management commands

The pgd commit-scope create, pgd commit-scope update, and pgd commit-scope drop commands are not supported in a PGD Essential cluster. The command will exit with the following advice:

Operation not supported for PGD Essential version.
HINT: This limit doesn't exist in the PGD Expanded version

Set group options

The pgd group set-option command will not allow update to the group options enable_raft, enable_routing, enable_wal_decoder, and streaming_mode for the PGD Essential cluster. The command will exit with below advice.

Operation not supported for PGD Essential version.
HINT: This limit doesn't exist in the PGD Expanded version

PGD node setup

The pgd node setup command will enable routing for global group and disable the same for the subgroup(s).

← Prev

Connections

↑ Up

Essential How-To

Next →

Durability in PGD Essential