GuidesAPI ReferenceEmail Support

PostgreSQL CLI Tool Installation & Use

Suggest Edits

See how to leverage command line interface (CLI) functionality for our hosting for PostgreSQL service. This ScaleGrid tool allows you to perform the same actions you can through the console from the command line.
The CLI tool is written in Python and published as a Python package, and the package simply needs to be installed into your machine from the Package Index. The CLI currently supports generic, Mongo-specific, Redis-specific and PostgreSQL-specific commands, and support for MySQL will be added in the future versions.

ScaleGrid CLI Tool for PostgreSQL Installation

  1. This package requires Python>=3.6, and you can follow these steps to install Python.
  2. Installing the package is straightforward – run pip install
1386
  1. You must log in to your ScaleGrid account before you can perform any of the actions. This can be done by running sg-cli login from the CLI.
  2. By default the CLI will attempt to connect to scalegrid.io. If you are running your own ScaleGrid Controller (E.g. On-premise VMWare install) you can specify the controller server using the “--controller-ip” option on sg-cli. Use “sg-cli -h” for more information.
  3. You will be prompted to enter your email address, password and 2FA code. Upon successful authentication, a cookie will be set up for the session and you will be logged in.
529

You will now be able to perform actions on your PostgreSQL clusters from the CLI!

PostgreSQL CLI Examples

The following actions can be performed from the command line:

Help Command

Get the Help menu for PostgreSQL

sg-cli postgresql --help

PostgreSQL Help Menu

    Usage:
        sg-cli postgresql <command> [<args>...]

    Options:
        -v, --verbose  Increase verbosity
        -h, --help     Show this menu
        -V --version   Show version

    Commands:
        create-cloud-profile
        get-available-db-versions
        create-cluster
        list-clusters
        get-cluster-credentials
        reset-credentials
        pause-cluster
        resume-cluster
        refresh-cluster
        delete-cluster
        update-firewall-rules
        get-firewall-rules    
        scale-up
        patch-os
        upgrade-agent
        list-backups
        get-backup-schedule
        set-backup-schedule
        peek-at-backup
        restore-backup
        start-backup
        delete-backup
        create-alert-rule
        list-alert-rules
        delete-alert-rule
        get-active-alerts
        resolve-alerts
        setup-follower
        sync-follower
        get-follower-status
        stop-following
        set-pgbouncer
        get-pgbouncer-config
        modify-pgbouncer-config

create-cloud-profile

Create a cloud profile for PostgreSQL deployments

sg-cli postgresql create-cloud-profile -h

sg-cli postgresql create-cloud-profile --aws --cloud-profile-name AWSPgProfile --region uswest1 --access-key <> --secret-key <> --vpc-id vpc-e0359578 --vpc-cidr 172.31.0.0/16 --subnet-id subnet-3254d7d1 --subnet-cidr 172.31.0.0/20 --security-group-name aws-private-sg --security-group-id sg-987r5l26
 --enable-ssh

sg-cli postgresql create-cloud-profile create-cloud-profile --azure --cloud-profile-name AzurePgProfile --region eastus --subscription-id <> --subnet-name subnet1-eastus --vnet-name vnet-aksd2497 --vnet-resource-group eastustest --security-group-name azure-private-sg --enable-ssh

New cloud profile ID: 3738. Keep this ID and use it to perform commands on your cloud profile once it is created.
To get more information about your cloud profile once it is created, run the list-cloud-profiles command.
Action ID: 1385126
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 1385126

get-available-db-versions

Get the PostgreSQL versions available on the given Cloud

sg-cli postgresql get-available-db-versions -h

sg-cli postgresql get-available-db-versions --cloud-type AWS

V1302 (v13.2)
V1206 (v12.6)
V1111 (v11.11)
V1014 (v10.14)

create-cluster

Create the cluster according to the parameters provided

sg-cli postgresql create-cluster -h

sg-cli postgresql create-cluster --cluster-name testingPG --shard-count 1 --replica-count 1 --size micro --version V1302 --cloud-profile-list AWSPgProfile --enable-pgBouncer  --pool-size 50 --pool-mode statement --encrypt-disk  --enable-ssl

sg-cli postgresql create-cluster --cluster-name testingPGMS --shard-count 1 --replica-count 2 --size small --version V1302 --cloud-profile-list "AWSPgProfile,AWSPgProfile,AWSPgProfile" --enable-ssl --encrypt-disk --replication-type SYNC --sync-commit-type REMOTE_WRITE --enable-pgBouncer --pool-mode statement --pool-size 50 --max-client-connections 100 --max-user-connections 100 --max-db-connections 100

New cluster ID: 21. Keep this ID and use it to perform commands on your cluster once it is created.
To get more information about your cluster once it is created, run the list-clusters command.
Action ID: 5051
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5051

list-clusters

Get basic info about all or a specific PostgreSQL cluster

sg-cli postgresql list-clusters -h

sg-cli postgresql list-clusters

{
    "name": "test2",
    "id": 22,
    "clusterType": "Standalone",
    "planType": "BYOC",
    "status": "Running",
    "size": "Micro",
    "versionStr": "v13.2",
    "diskSizeGB": 10,
    "usedDiskSizeGB": 1,
    "shards": [
        {
            "name": "Shard-0",
            "postgreSQLServers": [
                {
                    "addressableName": "SG-test2-68.devservers.scalegrid.io",
                    "cloudProfileName": "postprof",
                    "status": "Running",
                    "master": true,
                    "standby": false,
                    "arbiter": false,
                    "readReplica": false
                }
            ]
        }
    ],
    "sslEnabled": false,
    "encryptionEnabled": false,
    "isPgBouncerEnabled": true,
    "pgBouncerParams": {
        "pgBouncerPort": 6432,
        "settings": {
            "max_client_connections": "1000",
            "pool_mode": "transaction",
            "pool_size": "80"
        }
    }
}
{
    "name": "test1",
    "id": 21,
    "clusterType": "Standalone",
    "planType": "BYOC",
    "status": "Refreshing",
    "size": "Micro",
    "versionStr": "v13.2",
    "diskSizeGB": 0,
    "usedDiskSizeGB": 0,
    "shards": [
        {
            "name": "Shard-0",
            "postgreSQLServers": [
                {
                    "addressableName": "SG-test1-67.devservers.scalegrid.io",
                    "cloudProfileName": "postprof",
                    "status": "Running",
                    "master": true,
                    "standby": false,
                    "arbiter": false,
                    "readReplica": false
                }
            ]
        }
    ],
    "sslEnabled": false,
    "encryptionEnabled": false,
    "isPgBouncerEnabled": true,
    "pgBouncerParams": {
        "pgBouncerPort": 6432,
        "settings": {
            "max_client_connections": "1000",
            "pool_mode": null,
            "pool_size": "80"
        }
    }
}

sg-cli postgresql list-clusters --cluster-name test1

{
    "name": "test1",
    "id": 21,
    "clusterType": "Standalone",
    "planType": "BYOC",
    "status": "Failed",
    "size": "Micro",
    "versionStr": "v13.2",
    "diskSizeGB": 0,
    "usedDiskSizeGB": 0,
    "shards": [
        {
            "name": "Shard-0",
            "postgreSQLServers": [
                {
                    "addressableName": "SG-test1-67.devservers.scalegrid.io",
                    "cloudProfileName": "postprof",
                    "status": "Running",
                    "master": true,
                    "standby": false,
                    "arbiter": false,
                    "readReplica": false
                }
            ]
        }
    ],
    "sslEnabled": false,
    "encryptionEnabled": false,
    "isPgBouncerEnabled": true,
    "pgBouncerParams": {
        "pgBouncerPort": 6432,
        "settings": {
            "max_client_connections": "1000",
            "pool_mode": null,
            "pool_size": "80"
        }
    }
}

get-cluster-credentials

Get the root-user credentials for your deployed cluster

sg-cli postgresql get-cluster-credentials -h

sg-cli postgresql get-cluster-credentials --cluster-name test2

Username: sgpostgres
Password: SVIM7A$UlGtJ1r1T

Connection Strings:
For JDBC: jdbc:postgresql://SG-test2-36-pgsql-master.devservers.scalegrid.io:5432/<your-database-name>
For ODBC: DRIVER={PostgreSQL UNICODE}; Server=SG-test2-36-pgsql-master.devservers.scalegrid.io; Port=5432; Uid=<user>; Pwd=<password>; Database=<your_database_name>;
For PHP: $dbconn = pg_connect ("host=SG-test2-36-pgsql-master.devservers.scalegrid.io user=<user> password=<password> dbname=<your-database-name> port=5432);
For PYTHON: cnx = psycopg2.connect(host="SG-test2-36-pgsql-master.devservers.scalegrid.io" user="<user>" password="<password>" dbname="<your-database-name>" port=5432)
For RUBY: con = PG::Connection.open(user: "<user>", password: "<password>", database: "<your-database-name>", host: "SG-test2-36-pgsql-master.devservers.scalegrid.io", port: 5432)
For Node.js: var client = new pg.Client('postgresql://SG-test2-36-pgsql-master.devservers.scalegrid.io:5432/<your-database-name>');

Command Line Syntax:
psql -W -U sgpostgres -p 5432 -h SG-test2-36-pgsql-master.devservers.scalegrid.io -d postgres

reset-credentials

Reset the root-user credentials for your deployed cluster

sg-cli postgresql reset-credentials -h

sg-cli postgresql reset-credentials --cluster-name test2

Credentials rotated successfully

Action ID: 5054
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5054
To view the new login credentials run:
   sg-cli postgresql get-cluster-credentials --cluster-name test2

pause-cluster

Pause your deployed cluster

sg-cli postgresql pause-cluster -h

sg-cli postgresql pause-cluster --cluster-name test2

Pause started successfully
Action ID: 5061
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5061

resume-cluster

Resume your deployed cluster

sg-cli postgresql resume-cluster -h

sg-cli postgresql resume-cluster --cluster-name test2

Resume started successfully
Action ID: 5062
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

refresh-cluster

Refresh your deployed cluster

sg-cli postgresql refresh-cluster -h

sg-cli postgresql refresh-cluster --cluster-name test2

Refresh started successfully
Action ID: 5062
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

delete-cluster

Refresh your deployed cluster

sg-cli postgresql delete-cluster -h

sg-cli postgresql delete-cluster --cluster-name test2

Cluster delete started successfully
Action ID: 5065
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

set-firewall-rules

Set firewall rules on your deployed cluster

sg-cli postgresql set-firewall-rules -h

sg-cli postgresql set-firewall-rules --cluster-name test2 --cidr-list "10.0.0.0/16,172.16.0.0/16,52.13.23.41/32"

Firewall rules set successfully

get-firewall-rules

Get firewall rules for your deployed cluster

sg-cli postgresql get-firewall-rules -h

sg-cli postgresql get-firewall-rules --cluster-name test2

{
    "cidrList": [
        "10.0.0.0/16",
        "172.16.0.0/16",
        "52.13.23.41/32"
    ]
}

get-config

Get all database parameter configurations for your deployment

sg-cli postgresql get-config -h

sg-cli postgresql get-config --cluster-name test2

{
    "param_name": "statement_timeout",
    "current_val": "0",
    "unit": "ms",
    "valid_vals": null,
    "editable": true
}
{
    "param_name": "xmlbinary",
    "current_val": "'base64'",
    "unit": "",
    "valid_vals": [
        "base64",
        "hex"
    ],
    "editable": false
}

update-config

Update database parameter configurations for your deployment

sg-cli postgresql update-config -h

Dry-run(check if server restart is required:
sg-cli postgresql update-config --cluster-name test2 —-param-key-value “max_logical_replication_workers:4” –-dry-run

Apply-config:
sg-cli postgresql update-config --cluster-name test2 —-param-key-value “max_logical_replication_workers:4, max_wal_size:4000”

Dry-run:
The following configuration changes require PostgreSQL server to be restarted. For replication setup, this will be done as rolling job.
If you wish to proceed, remove the '--dry-run' flag and run again.

Apply-config:
Config Update in progress..
Action ID: 3906176
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 3906176

scale-up

Scale your deployed cluster up to your specified size

sg-cli postgresql scale-up -h

sg-cli postgresql scale-up --cluster-name test2 --size xlarge

Scale up started successfully
Action ID: 5062
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

patch-os

Start Patch-os activity on your deployed cluster 

sg-cli postgresql patch-os -h

sg-cli postgresql patch-os --cluster-name test2

OS Patch started successfully
Action ID: 5069
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5069

upgrade-agent

Upgrade ScaleGrid Agent on your deployed cluster 

sg-cli postgresql upgrade-agent -h

sg-cli postgresql upgrade-agent --cluster-name test2

Upgrading Agent
Action ID: 5072
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5072

list-backups

List the backups taken on your deployed cluster 

sg-cli postgresql list-backups -h

sg-cli postgresql list-backups --cluster-name test2 --backup-name test2backup

sg-cli postgresql list-backups --cluster-name test2

{
    "name": "test2backup",
    "id": 24,
    "object_id": 24,
    "created": "2022-01-24 08:16:23",
    "cloudType": "ONDEMAND",
    "comment": null
}

get-backup-schedule

Get the backup-schedule for your deployed cluster 

sg-cli postgresql get-backup-schedule -h

sg-cli postgresql get-backup-schedule --cluster-name test2

{
    "backupHour": 23,
    "backupIntervalInHours": 8,
    "backupScheduledBackupLimit": 10,
    "target": "MASTER"
}

set-backup-schedule

Set the backup schedule for your deployed cluster 

sg-cli postgresql set-backup-schedule -h

sg-cli postgresql set-backup-schedule --cluster-name test2 --interval 8 --hour 23 --limit 10

Backup schedule set successfully

sg-cli postgresql set-backup-schedule --cluster-name test2 --disabled

Scheduled backups disabled

peek-at-backup

Create a new standalone cluster from a past backup 

sg-cli postgresql peek-at-backup -h

sg-cli postgresql peek-at-backup --source-cluster test2 --backup-name test2backup --cluster-name NewPeekedCluster

Peek started successfully
Action ID: 5062
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

restore-backup

Restore a backup

sg-cli postgresql restore-backup -h

sg-cli postgresql restore-backup --cluster-name test2 --backup-name test2backup

test2backup restore started successfully
Action ID: 5062
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5062

start-backup

Create a backup of the cluster

sg-cli postgresql start-backup -h

sg-cli postgresql start-backup --cluster-name test2 --backup-name test2backup

test2 backup started successfully
Action ID: 5075
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5075

delete-backup

Delete a backup of the cluster

sg-cli postgresql delete-backup -h

sg-cli postgresql delete-backup --cluster-name test2 --backup-name test2backup

Backup Delete started successfully
Action ID: 5082
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5082

create-alert-rule

Create an alert-rule for the cluster

sg-cli postgresql create-alert-rule -h

sg-cli postgresql create-alert-rule --cluster-name test2 --type metric --operator gt --threshold 80 --notifications email --metric cpu_total --duration six

{
    "alertRuleDescription": "CPU - Total (%) greater than 80%",
    "averageType": "SIX",
    "clusterId": 24,
    "created": "2022-01-24 08:21:00.092000",
    "databaseType": "POSTGRESQL",
    "enabled": true,
    "id": 1,
    "metric": "CPU_TOTAL",
    "notifications": [
        "EMAIL"
    ],
    "operator": "GT",
    "ruleMetricName": "CPU - Total (%)",
    "threshold": 80.0,
    "cloudType": "METRIC"
}
Alert rule created successfully
Alert Rule ID: 1

list-alert-rule

List all the alert-rules for the cluster

sg-cli postgresql list-alert-rules -h

sg-cli postgresql list-alert-rules --cluster-name test2

{
    "alertRuleDescription": "CPU - Total (%) greater than 80%",
    "averageType": "SIX",
    "clusterId": 24,
    "created": "2022-01-24 08:21:00",
    "databaseType": "POSTGRESQL",
    "enabled": true,
    "id": 1,
    "metric": "CPU_TOTAL",
    "notifications": [
        "EMAIL"
    ],
    "operator": "GT",
    "ruleMetricName": "CPU - Total (%)",
    "threshold": 80.0,
    "cloudType": "METRIC"
}

delete-alert-rule

Delete a cluster-alert-rule 

sg-cli postgresql delete-alert-rule -h

sg-cli delete-alert-rule --alert-rule-id 1

Alert rule deleted successfully

get-active-alerts

Get all active alerts on a particular cluster

sg-cli postgresql get-active-alerts -h

sg-cli postgresql get-active-alerts --cluster-name test2

{
    "alertDescription": "CPU - Total (%) went below 80%",
    "clusterID": 24,
    "created": "2022-01-24 08:30:10",
    "dismissComment": null,
    "dismissedDate": null,
    "id": 1,
    "machineName": "SG-test2-70.devservers.scalegrid.io",
    "state": "ACTIVE",
    "userAlertRuleId": 3
}

resolve-alerts

Dismiss alerts for a particular cluster

sg-cli postgresql resolve-alerts -h

sg-cli postgresql resolve-alerts --cluster-name test2 --alert-id-list 1

Alerts resolved successfully

setup-follower

Setup follower relationship between two clusters such that the target-cluster syncs from the source-cluster periodically. If a relationship already exists, you can use this function to change the import schedule.

sg-cli postgresql setup-follower -h

sg-cli postgresql setup-follower --source-cluster test2 --target-cluster follower --start-hour 23 --interval 2

Follower relationship between clusters follower and test2 created successfully

sync-follower

Syncs a follower cluster on-demand, from its source. This operation will overwrite all data on the target cluster with the latest backup from the source cluster.

sg-cli postgresql sync-follower -h

sg-cli postgresql sync-follower --target-cluster follower

Follower cluster sync started successfully
Action ID: 5082
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5082

get-follower-status

To get the details of the follower relationship of the target cluster

sg-cli postgresql get-follower-status -h

sg-cli postgresql get-follower-status --target-cluster follower

{
    "destinationCluster": {
        "name": "follower",
        "id": 25,
        "status": "Refreshing",
        "size": "Micro",
        "clusterType": "Standalone",
        "diskSizeGB": 10,
        "usedDiskSizeGB": 1,
        "encryptionEnabled": false,
        "sslEnabled": false
    },
    "sourceCluster": {
        "name": "test",
        "id": 24,
        "status": "Updating",
        "size": "Micro",
        "clusterType": "Standalone",
        "diskSizeGB": 10,
        "usedDiskSizeGB": 1,
        "encryptionEnabled": false,
        "sslEnabled": false
    },
    "syncSchedule": {
        "intervalInHours": 2,
        "jobType": "PostgreSQLSyncFollowerWithSourceCluster",
        "nextRuntime": 1643054400000
    }
}

stop-following

To get the details of the follower relationship of the target cluster

sg-cli postgresql stop-following -h

sg-cli postgresql stop-following --target-cluster follower

Follower relationship broken successfully

set-pgbouncer

To enable/disable connection pooling by PgBouncer on your PostgreSQL cluster.

sg-cli postgresql set-pgbouncer -h

sg-cli postgresql set-pgbouncer --cluster-name test2 --pool-mode statement --pool-size 70 --max-client-connections 100 --max-db-connections 100  --max-user-connections 200

Enabling PgBouncer
Action ID: 5101
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5101

sg-cli postgresql set-pgbouncer --cluster-name test2 --disable

Disabling PgBouncer
Use your action ID to monitor the job status.
Action ID: 5102

get-pgbouncer-config

To get the current pgBouncer config details 

sg-cli postgresql get-pgbouncer-config -h

sg-cli postgresql get-pgbouncer-config --cluster-name test2

{
    "settings": {
        "max_db_connections": "0",
        "max_user_connections": "0",
        "max_client_connections": "100",
        "pool_mode": "session",
        "pool_size": "20"
    }
}

modify-pgbouncer-config

To modify the current PgBouncer config details 

sg-cli postgresql modify-pgbouncer-config -h

sg-cli postgresql modify-pgbouncer-config --cluster-name test --pool-mode statement --pool-size 30 --max-client-connections 150 --max-db-connections 200  --max-user-connections 300

Modifying PgBouncer Configuration
Action ID: 5102
To track whether your action has completed, run:
  sg-cli wait-until-job-done --action-id 5102

check-job-status

Check the job status using action-id 

sg-cli check-job-status -h

sg-cli check-job-status --action-id 5134

{
    "name": "Patch operating system",
    "object_name": "test",
    "object_type": "PostgreSQLCluster",
    "cancelled": false,
    "progress": 100,
    "status": "Completed",
    "run_by": "[email protected]"
}

wait-until-job-done

Wait until a job is done using action-id

sg-cli wait-until-job-done -h

sg-cli wait-until-job-done --action-id 5135

Waiting...
Done

Updated over 1 year ago