Operator CLI

The command line tool, pgo, is used to interact with the Postgres Operator.

Most users will work with the Operator using the pgo CLI tool. That tool is downloaded from the GitHub Releases page for the Operator (https://github.com/crunchydata/postgres-operator/releases).

The pgo client is provided in Mac, Windows, and Linux binary formats, download the appropriate client to your local laptop or workstation to work with a remote Operator.

Syntax

Use the following syntax to run pgo commands from your terminal window:

pgo [command] ([TYPE] [NAME]) [flags]

Where command is a verb like: - show - get - create - delete

And type is a resource type like: - cluster - policy - user

And name is the name of the resource type like: - mycluster - somesqlpolicy - john

To get detailed help information and command flag descriptions on each pgo command, enter:

pgo [command] -h

Operations

The following table shows the pgo operations currently implemented:

Operation Syntax Description
apply pgo apply mypolicy –selector=name=mycluster Apply a SQL policy on a Postgres cluster(s)
backup pgo backup mycluster Perform a backup on a Postgres cluster(s)
create pgo create cluster mycluster Create an Operator resource type (e.g. cluster, policy, schedule, user)
delete pgo delete cluster mycluster Delete an Operator resource type (e.g. cluster, policy, user, schedule)
df pgo df mycluster Display the disk status/capacity of a Postgres cluster.
failover pgo failover mycluster Perform a manual failover of a Postgres cluster.
help pgo help Display general pgo help information.
label pgo label mycluster –label=environment=prod Create a metadata label for a Postgres cluster(s).
load pgo load –load-config=load.json –selector=name=mycluster Perform a data load into a Postgres cluster(s).
reload pgo reload mycluster Perform a pg_ctl reload command on a Postgres cluster(s).
restore pgo restore mycluster Perform a pgbackrest restore on a Postgres cluster.
scale pgo scale mycluster Create a Postgres replica(s) for a given Postgres cluster.
scaledown pgo scaledown mycluster –query Delete a replica from a Postgres cluster.
show pgo show cluster mycluster Display Operator resource information (e.g. cluster, user, policy, schedule).
status pgo status Display Operator status.
test pgo test mycluster Perform a SQL test on a Postgres cluster(s).
update pgo update cluster –label=autofail=false Update a Postgres cluster(s).
upgrade pgo upgrade mycluster Perform a minor upgrade to a Postgres cluster(s).
user pgo user –selector=name=mycluster –update-passwords Perform Postgres user maintenance on a Postgres cluster(s).
version pgo version Display Operator version information.

Common Operations

Cluster Operations

Create Cluster With a Primary Only

pgo create cluster mycluster

Create Cluster With a Primary and a Replica

pgo create cluster mycluster --replica-count=1

Scale a Cluster with Additional Replicas

pgo scale cluster mycluster

Create a Cluster with pgbackrest Configured

pgo create cluster mycluster --pgbackrest

Scaledown a Cluster

pgo scaledown cluster mycluster --query
pgo scaledown cluster mycluster --target=sometarget

Delete a Cluster

pgo delete cluster mycluster

Delete a Cluster and It’s Persistent Volume Claims

pgo delete cluster mycluster --delete-data

Test a Cluster

pgo test mycluster

View Disk Utilization

pgo df mycluster

Label Operations

Apply a Label to a Cluster

pgo label mycluster --label=environment=prod

Appy a Label to a Set of Clusters

pgo label --selector=clustertypes=research --label=environment=prod

Show Clusters by Label

pgo show cluster --selector=environment=prod

Policy Operations

Create a Policy

pgo create policy mypolicy --in-file=mypolicy.sql

View Policies

pgo show policy all

Apply a Policy

pgo apply mypolicy --selector=environment=prod
pgo apply mypolicy --selector=name=mycluster

Operator Status

Show Operator Version

pgo version

Show Operator Status

pgo status

Show Operator Configuration

pgo show config

Backup and Restore

Perform a pgbasebackup

pgo backup mycluster

Perform a pgbackrest backup

pgo backup mycluster --backup-type=pgbackrest
pgo backup mycluster --backup-type=pgbackrest --backup-opts="--type=diff"

The last example passes in pgbackrest flags to the backup command. See pgbackrest.org for command flag descriptions.

Perform a pgbackrest restore

pgo restore mycluster

Or perform a restore based on a point in time:

pgo restore mycluster --pitr-target="2019-01-14 00:02:14.921404+00" --backup-opts="--type=time"

Here are some steps to test PITR:

  • pgo create cluster mycluster –pgbackrest
  • create a table on the new cluster called beforebackup
  • pgo backup mycluster –backup-type=pgbackrest
  • create a table on the cluster called afterbackup
  • execute select now() on the database to get the time, use this timestamp minus a couple of minutes when you perform the restore
  • pgo restore mycluster –pitr-target=“2019-01-14 00:02:14.921404+00” –backup-opts=“–type=time –log-level-console=info”
  • wait for the database to be restored
  • execute \d in the database and you should see the database state prior to where the afterbackup table was created

See the Design section of the Operator documentation for things to consider before you do a restore.

Restore from pgbasebackup

pgo create cluster restoredcluster --backup-path=/somebackup/path --backup-pvc=somebackuppvc --secret-from=mycluster

Fail-over Operations

Perform a Manual Fail-over

pgo failover mycluster --query
pgo failover mycluster --target=sometarget

Create a Cluster with Auto-fail Enabled

pgo create cluster mycluster --autofail

Add-On Operations

Create a Cluster with pgbouncer

pgo create cluster mycluster --pgbouncer

Create a Cluster with pgpool

pgo create cluster mycluster --pgpool

Add pgbouncer to a Cluster

pgo create pgbouncer mycluster

Add pgpool to a Cluster

pgo create pgpool mycluster

Remove pgbouncer from a Cluster

pgo delete pgbouncer mycluster

Remove pgpool from a Cluster

pgo delete pgpool mycluster

Create a Cluster with pgbadger

pgo create cluster mycluster --pgbadger

Create a Cluster with Metrics Collection

pgo create cluster mycluster --metrics

Scheduled Tasks

Automated full pgBackRest backups every Sunday at 1 am

pgo create schedule mycluster --schedule="0 1 * * SUN" \
    --schedule-type=pgbackrest --pgbackrest-backup-type=full

Automated diff pgBackRest backups every Monday-Saturday at 1 am

pgo create schedule mycluster --schedule="0 1 * * MON-SAT" \
    --schedule-type=pgbackrest --pgbackrest-backup-type=diff

Automated pgBaseBackup backups every day at 1 am

In order to have a backup PVC created, users should run the pgo backup command against the target cluster prior to creating this schedule.

pgo create schedule mycluster --schedule="0 1 * * *" \
    --schedule-type=pgbasebackup --pvc-name=mycluster-backup

Automated Policy every day at 1 am

pgo create schedule --selector=pg-cluster=mycluster --schedule="0 1 * * *" \
     --schedule-type=policy --policy=mypolicy --database=userdb \
     --secret=mycluster-testuser-secret

Complex Deployments

Create a Cluster using Specific Storage

pgo create cluster mycluster --storage-config=somestorageconfig

Create a Cluster using a Preferred Node

pgo create cluster mycluster --node-label=speed=superfast

Create a Replica using Specific Storage

pgo scale mycluster --storage-config=someslowerstorage

Create a Replica using a Preferred Node

pgo scale mycluster --node-label=speed=slowerthannormal

Create a Cluster with LoadBalancer ServiceType

pgo create cluster mycluster --service-type=LoadBalancer

pgo Global Flags

pgo global command flags include:

Flag Description
apiserver-url URL of the Operator REST API service, override with CO_APISERVER_URL environment variable
debug enable debug messages
pgo-ca-cert The CA Certificate file path for authenticating to the PostgreSQL Operator apiserver. Override with PGO_CA_CERT environment variable
pgo-client-cert The Client Certificate file path for authenticating to the PostgreSQL Operator apiserver. Override with PGO_CLIENT_CERT environment variable
pgo-client-key The Client Key file path for authenticating to the PostgreSQL Operator apiserver. Override with PGO_CLIENT_KEY environment variable