Creating an OCI PostgreSQL DB System using the CLI

In the previous blog Getting Started with the OCI PostgreSQL DB System, we covered how to create an OCI PostgreSQL DB System using the web console.  While that method is fairly simple and somewhat intuitive, it has the risk of deployment in-consistencies and missed attributes.

A more consistent approach is to use the OCI CLI. A CLI-based approach increases consistency and efficiency.  And CLI commands can be incorporated into other scripts and provisioning tools such as Ansible.  Provisioning using REST API calls and Terraform are valid alternatives – but can be more complicated to new users and hence are not covered in this article. The semi-programmatic OCI CLI is arguably the sweet spot between complex APIs and inconsistent manual clicking.

To leverage the OCI CLI PostgreSQL-related commands, CLI version 3.62 or greater is needed.  Installing and configuring the OCI CLI is beyond the scope of this article – follow the Oracle documentation quick-start if you need to set up the CLI for the first time.

Interestingly, Oracle calls the new PostgreSQL service API the “PGSQL Control Plane CLI” and uses the oci psql command.  Using “psql” as the main command option is an interesting choice as it conflicts in name with the psql PostgreSQL command line utility.  This leads to some initial confusion.

Requirements & Environment Variables

The overall goal is to have a single command that can be used to create (launch) an “OCI Database with PostgreSQL” DB System.  Such a command can be incorporated into other provisioning scripts and/or environment provisioning workflows and tools.  Or just used by DBAs manually for quick and easy environment builds.

Even in the simplest form, such a command has a number of varying inputs, such as the system’s name, shape, number of nodes etc.
Overall, there are about a dozen such inputs.  All of these items we’ll define in environment variables.  Then just create the OCI PostgreSQL DB System using a single OCI CLI using these variables.  To make the environment variables easy to identify, I’ll prefix them all with the string MY_OCI.  Of course, if you want to use the command in an automation tool or framework (Ansible, Jenkins, etc.) then tool-specific variables would be used instead of OS environment variables.

Some of these, such as the PostgreSQL version number, currently only support a single value (at the time of writing “14” for the PostgreSQL version).  While that could be hard-coded into the provisioning command, using a variable instead provides flexibility and future-proofing.

Overall, the varying command inputs that we’ll define into variables are:

1. DB System name – can be any string and can be changed post-creation
2. Compartment ID (OCID)
3. Desired PostgreSQL version – currently only “14” is supported
4. The OCI PostgreSQL “Configuration” ID (OCID)
5. Desired VM Shape (name)
6. Number of nodes to create (only one will be read-write)
7. The number of OCPUs per node to allocate
8. The subnet ID (OCID)
9. Your specific Availability Domain name
10. The IOPs performance tier – currently only 300K (actual value of 300000) is supported
11. Administrator credential secret ID (OCID)
12. Administrator credential secret version number
13. The Administrator username – arbitrary

Many of these, such as the compartment and subnet OCID you would like to use, you may already have.  Either on file or obtainable from the OCI web console.

About half of these are easy to set as they’re arbitrary values that you can choose (such as the system name or number of nodes) or ones that currently only permit a single value (but we’ll include them in variables nonetheless for future compatibility):

export MY_OCI_DBSYSTEM_NAME="CLI Test 01"
export MY_OCI_PG_VERSION="14"
export MY_OCI_NODES="1"
export MY_OCI_OCPUS="2"
export MY_OCI_IOPS="300000"
export MY_OCI_ADMIN_USER="simon"

Verifying the OCI CLI

A quick way to ensure that the OCI CLI is installed and working is to simply list your tenancy regions. For example:

$ oci -v
3.36.2

$ oci iam region-subscription list --output table
+----------------+------------+--------------+--------+
| is-home-region | region-key | region-name  | status |
+----------------+------------+--------------+--------+
| True           | YYZ        | ca-toronto-1 | READY  |
+----------------+------------+--------------+--------+

$

Determining Other Requisite Values

Some of the other required values, such as the compartment OCID can be obtained interactively through the web console or through exploratory OCI CLI commands (shown here).

If you already have these values, you can skip this section and proceed to the Creating a DB System using the OCI CLI section.  Or use the following steps to determine any required values using the OCI CLI.

First, ensure that you know which compartment in your tenancy you want to use.

Example of listing the available compartments, with JSON output as tabular is very wide:

$ oci iam compartment list --query 'data[].{name:name,description:description,compartment_id:id}'

[
  {
    "compartment_id": "ocid1.compartment.oc1..aaaaaaaattldmgewa6nozjvjv3kcvylqr7rk5xld2p32ahzeawc3udenr6fa",
    "description": "idcs-495600fb8ece4aa4b7448733ce87359f|27913360|XXXXXXXX_XXXXXXXX@XXXXXXXXXXXX.com-714817",
    "name": "ManagedCompartmentForPaaS"
  },
  {
    "compartment_id": "ocid1.compartment.oc1..aaaaaaaaeg4hpnrklbadspokjn4y4is6ajhkl5iwttzbnnqj5swq7l5owxia",
    "description": "test-infrastructure-1",
    "name": "test-infrastructure-1"
  }
]

In my case, I would like to use the “test-infrastructure-1” compartment and I could assign its OCID to my environment variable manually based on cutting and pasting from the output above.

But what’s more likely is that you know the compartment by name (maybe it’s defined elsewhere in some other tooling or source) and then you need to get the OCID from the name using the CLI.  This is simple enough by using the CLI and the jq utility:

$ export MY_OCI_COMP_ID=$(oci iam compartment list --query 'data[?"name"==`test-infrastructure-1`].id' | jq -r '.[]')

$ echo $MY_OCI_COMP_ID

ocid1.compartment.oc1..aaaaaaaaeg4hpnrklbadspokjn4y4is6ajhkl5iwttzbnnqj5swq7l5owxia

$

The DB System can use one of the Oracle-provided default “Configurations” or one of your own custom configurations.  To list your custom Configurations use:

$ oci psql configuration-collection list-configurations --all --compartment-id $MY_OCI_COMP_ID --lifecycle-state "ACTIVE" --query 'data.items[].{"db-version":"db-version","display-name":"display-name","instance-ocpu-count":"instance-ocpu-count","instance-memory-size-in-gbs":"instance-memory-size-in-gbs"}'

[
  {
    "db-version": "14",
    "display-name": "my-2OCPU-custom-configuration",
    "instance-memory-size-in-gbs": 32,
    "instance-ocpu-count": 2
  }
]

If you would like to use one of the default Configurations instead, then use the alternative command:

$ oci psql default-configuration-collection list-default-configurations --all --lifecycle-state "ACTIVE" --query 'data.items[].{"id":"id","db-version":"db-version","display-name":"display-name","instance-ocpu-count":"instance-ocpu-count","instance-memory-size-in-gbs":"instance-memory-size-in-gbs"}'

[
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.4.64GB-14-0_29",
    "instance-memory-size-in-gbs": 64,
    "instance-ocpu-count": 4
  },
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.32.512GB-14-0_29",
    "instance-memory-size-in-gbs": 512,
    "instance-ocpu-count": 32
  },
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.16.256GB-14-0_29",
    "instance-memory-size-in-gbs": 256,
    "instance-ocpu-count": 16
  },
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.8.128GB-14-0_29",
    "instance-memory-size-in-gbs": 128,
    "instance-ocpu-count": 8
  },
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.2.32GB-14-0_29",
    "instance-memory-size-in-gbs": 32,
    "instance-ocpu-count": 2
  },
  {
    "db-version": "14",
    "display-name": "PostgreSQL.VM.Standard.E4.Flex.64.1024GB-14-0_29",
    "instance-memory-size-in-gbs": 1024,
    "instance-ocpu-count": 64
  }
]

$

Again, we could manually assign the Configuration’s OCID to a variable, but if we wanted to do it programmatically, based on name, we can use a command similar to:

$ export MY_OCI_CONFIG_ID=$(oci psql configuration-collection list-configurations --all --compartment-id $MY_OCI_COMP_ID --lifecycle-state "ACTIVE" --query 'data.items[?"display-name"==`my-2OCPU-custom-configuration`].id' | jq -r '.[]')

$ echo $MY_OCI_CONFIG_ID
ocid1.postgresqlconfiguration.oc1.ca-toronto-1.amaaaaaapoxaypyaknaf447d2am6n2d7vxzo2nuei75mqkcl5raausruzueq

$

Listing the available VM shapes is easy – the number of OCPUs and memory are included in the shape names.  To list the available VM shapes use:

$ oci psql shape-summary list-shapes --compartment-id $MY_OCI_COMP_ID --query 'data.items[].id'

[
  "PostgreSQL.VM.Standard.E4.Flex.16.256GB",
  "PostgreSQL.VM.Standard.E4.Flex.2.32GB",
  "PostgreSQL.VM.Standard.E4.Flex.32.512GB",
  "PostgreSQL.VM.Standard.E4.Flex.4.64GB",
  "PostgreSQL.VM.Standard.E4.Flex.64.1024GB",
  "PostgreSQL.VM.Standard.E4.Flex.8.128GB"
]

$

Pick whichever you’d like to use and assign it to an environment variable manually:

export MY_OCI_VM_SHAPE="PostgreSQL.VM.Standard.E4.Flex.2.32GB"

We also need to know what subnet to use.  As of the time of writing, the “OCI Database with PostgreSQL” service only permits DB Systems to be created on private subnets.

To query private subnets:

$ oci network subnet list --compartment-id=$MY_OCI_COMP_ID --query 'data[?"prohibit-internet-ingress"==`true`].{"availability-domain":"availability-domain","cidr-block":"cidr-block","display-name":"display-name","subnet-id":"id","prohibit-internet-ingress":"prohibit-internet-ingress"}'

[
  {
    "availability-domain": null,
    "cidr-block": "10.1.1.0/24",
    "display-name": "private subnet-test-vcn-1",
    "prohibit-internet-ingress": true,
    "subnet-id": "ocid1.subnet.oc1.ca-toronto-1.aaaaaaaai6k2wmoer4xum6vwxa5stfkegkzcwcn24trjqoxvj4kqmg2zsdpq"
  }
]

$

Knowing the desired subnet by name, we can assign it to a variable:

$ export MY_OCI_SUBNET_ID=$(oci network subnet list --compartment-id=$MY_OCI_COMP_ID --query 'data[?"display-name"==`private subnet-test-vcn-1`].id' | jq -r '.[]')

$ echo $MY_OCI_SUBNET_ID
ocid1.subnet.oc1.ca-toronto-1.aaaaaaaai6k2wmoer4xum6vwxa5stfkegkzcwcn24trjqoxvj4kqmg2zsdpq

$

Listing your availability domains is also simple:

$ oci iam availability-domain list --all --compartment-id $MY_OCI_COMP_ID --query 'data[].name'

[
  "XXXX:CA-TORONTO-1-AD-1"
]

$

Choose whichever one you want – in my case, I only have one in my region – and manually assign it to a variable:

export MY_OCI_AD="XXXX:CA-TORONTO-1-AD-1"

Finally, the credential specification must be provided.  For the credential specification, there are two options:

1. A JSON specification which includes a username and password – not recommended as the CLI command would then expose a plain text password.
2. A JSON specification that references an existing OCI Vault secret – recommended as no plain text sensitive information is exposed.

To understand how these options can be specified, list the accepted JSON syntax/formats using:

$ oci psql db-system create --generate-param-json-input credentials

{
  "passwordDetails": [
    "This parameter should actually be a JSON object rather than an array - pick one of the following object variants to use",
    {
      "password": "string",
      "passwordType": "PLAIN_TEXT"
    },
    {
      "passwordType": "VAULT_SECRET",
      "secretId": "string",
      "secretVersion": "string"
    }
  ],
  "username": "string"
}

$

If using an OCI Vault Secret and needing to determine the secret name, list the possible secrets using:

$ oci vault secret list --all --compartment-id $MY_OCI_COMP_ID --query 'data[].{"secret-id":"id","secret-name":"secret-name"}' 

[
  {
    "secret-id": "ocid1.vaultsecret.oc1.ca-toronto-1.amaaaaaapoxaypyauxapstdijfi3ej5m53pvlvztiolpuaisq7j2zkmpz7wq",
    "secret-name": "simon-pg-admin-1"
  }
]

$

With the secret name known, we can extract the secret OCID to a variable as well as get the latest secret version number:

$ export MY_OCI_SECRET_ID=$(oci vault secret list --all --compartment-id $MY_OCI_COMP_ID --query 'data[].id' | jq -r '.[]')

$ echo $MY_OCI_SECRET_ID
ocid1.vaultsecret.oc1.ca-toronto-1.amaaaaaapoxaypyauxapstdijfi3ej5m53pvlvztiolpuaisq7j2zkmpz7wq

$ export MY_OCI_SECRET_VERSION=$(oci vault secret get --secret-id "$MY_OCI_SECRET_ID" --query 'data."current-version-number"')

$ echo -e "$MY_OCI_SECRET_ID\n$MY_OCI_SECRET_VERSION"
ocid1.vaultsecret.oc1.ca-toronto-1.amaaaaaapoxaypyauxapstdijfi3ej5m53pvlvztiolpuaisq7j2zkmpz7wq
2

$

Whether you defined them manually, or using CLI commands similar to what was shown, you should now have all 13 environment variables defined.  To verify:

$ env | grep MY_OCI | sort | column -t -s "="

MY_OCI_ADMIN_USER      simon1
MY_OCI_AD              XXXX:CA-TORONTO-1-AD-1
MY_OCI_COMP_ID         ocid1.compartment.oc1..aaaaaaaaeg4hpnrklbadspokjn4y4is6ajhkl5iwttzbnnqj5swq7l5owxia
MY_OCI_CONFIG_ID       ocid1.postgresqlconfiguration.oc1.ca-toronto-1.amaaaaaapoxaypyaknaf447d2am6n2d7vxzo2nuei75mqkcl5raausruzueq
MY_OCI_DBSYSTEM_NAME   CLI Test 01
MY_OCI_IOPS            300000
MY_OCI_NODES           1
MY_OCI_OCPUS           2
MY_OCI_PG_VERSION      14
MY_OCI_SECRET_ID       ocid1.vaultsecret.oc1.ca-toronto-1.amaaaaaapoxaypyauxapstdijfi3ej5m53pvlvztiolpuaisq7j2zkmpz7wq
MY_OCI_SECRET_VERSION  2
MY_OCI_SUBNET_ID       ocid1.subnet.oc1.ca-toronto-1.aaaaaaaai6k2wmoer4xum6vwxa5stfkegkzcwcn24trjqoxvj4kqmg2zsdpq
MY_OCI_VM_SHAPE        PostgreSQL.VM.Standard.E4.Flex.2.32GB

Creating a DB System using the OCI CLI

Once you have all of the varying input values defined using OS environment variables (or tooling-specific variables), creating the “OCI Database with PostgreSQL” in a single command is simple:

oci psql db-system create \
  --display-name "$MY_OCI_DBSYSTEM_NAME" \
  --compartment-id $MY_OCI_COMP_ID \
  --db-version $MY_OCI_PG_VERSION \
  --config-id $MY_OCI_CONFIG_ID \
  --shape $MY_OCI_VM_SHAPE \
  --instance-count $MY_OCI_NODES \
  --instance-ocpu-count $MY_OCI_OCPUS \
  --credentials '{"passwordDetails": {"passwordType":"VAULT_SECRET", "secretId":"'$MY_OCI_SECRET_ID'","secretVersion": "'$MY_OCI_SECRET_VERSION'"}, "username":"'$MY_OCI_ADMIN_USER'"}' \
  --network-details '{"nsgIds":null, "primaryDbEndpointPrivateIp":null, "subnetId":"'$MY_OCI_SUBNET_ID'"}' \
  --storage-details '{"availabilityDomain":"'$MY_OCI_AD'", "iops":"'$MY_OCI_IOPS'", "isRegionallyDurable":false, "systemType":"OCI_OPTIMIZED_STORAGE"}'

If the command works, JSON output will be returned.  Which will include an "opc-work-request-id" (for tracking) at the bottom.

NOTE: it may take a few minutes for the new DB System to appear in the OCI web console.

Output highlights (excerpt for brevity):

{
  "data": {
    "admin-username": "simon",
    "compartment-id": "ocid1.compartment.oc1..aaaaaaaaeg4hpnrklbadspokjn4y4is6ajhkl5iwttzbnnqj5swq7l5owxia",
    "config-id": "ocid1.postgresqlconfiguration.oc1.ca-toronto-1.amaaaaaapoxaypyaknaf447d2am6n2d7vxzo2nuei75mqkcl5raausruzueq",
    "db-version": "14",
...
    "display-name": "CLI Test 01",
    "freeform-tags": {},
    "id": "ocid1.postgresqldbsystem.oc1.ca-toronto-1.amaaaaaapoxaypyal54jaz4boijmyhratihybj5rvanufillvw6tucwefpga",
    "instance-count": 1,
    "instance-memory-size-in-gbs": 32,
    "instance-ocpu-count": 2,
...
    "lifecycle-details": "CREATING",
    "lifecycle-state": "CREATING",
...
  "opc-work-request-id": "ocid1.postgresqlworkrequest.oc1.ca-toronto-1.amaaaaaapoxaypyax2cbisrbxm7px2a37hyzw6q2a2s5y7bm4hik4kewfgwq"
}

If the command doesn’t succeed, debugging, particularly of input values and security/permissions will be required.

If you need to check on the status of the work request (using the CLI as of course it can also be viewed through the work console), use:

$ oci psql work-request get --work-request-id "ocid1.postgresqlworkrequest.oc1.ca-toronto-1.amaaaaaapoxaypyax2cbisrbxm7px2a37hyzw6q2a2s5y7bm4hik4kewfgwq"

{
  "data": {
    "compartment-id": "ocid1.compartment.oc1..aaaaaaaaeg4hpnrklbadspokjn4y4is6ajhkl5iwttzbnnqj5swq7l5owxia",
    "id": "ocid1.postgresqlworkrequest.oc1.ca-toronto-1.amaaaaaapoxaypyax2cbisrbxm7px2a37hyzw6q2a2s5y7bm4hik4kewfgwq",
    "operation-type": "CREATE_POSTGRESQL_DB_SYSTEM",
    "percent-complete": 10.0,
    "resources": [
      {
        "action-type": "IN_PROGRESS",
        "entity-type": "dbsystem",
        "entity-uri": "/dbsystems/ocid1.postgresqldbsystem.oc1.ca-toronto-1.amaaaaaapoxaypyamdrthjapdcjp7z7wioogqoxnieg2wpawj5jltio3m5vq",
        "identifier": "ocid1.postgresqldbsystem.oc1.ca-toronto-1.amaaaaaapoxaypyal54jaz4boijmyhratihybj5rvanufillvw6tucwefpga",
        "metadata": {
          "display_name": "CLI Test 01",
          "is_dry_run": "false"
        }
      }
    ],
    "status": "IN_PROGRESS",
    "time-accepted": "2023-11-23T22:14:01.577000+00:00",
    "time-finished": null,
    "time-started": "2023-11-23T22:14:09.297000+00:00"
  },
  "etag": "d11d588c62efddd06878c4a9337712adb58c366885e1083d9878e00ec42c01f4--gzip"
}

Some of the inputs (command line argument values) in the oci psql db-system create command are fairly straightforward: literal (simple) strings or numerics.  Others (the last three specifically) are complex JSON-formatted inputs.  These can be provided in-line as shown or via references to separate JSON files.  The latter may be more applicable when integrating with other provisioning tooling.

Regardless, properly formatting the JSON is often necessary for troubleshooting.  A pro tip to ensure that your JSON syntax is correct is to manually echo them into the jq utility.  For example:

echo '{"passwordDetails": {"passwordType":"VAULT_SECRET", "secretId":"'$MY_OCI_SECRET_ID'","secretVersion": "1"}, "username":"simon"}' | jq -s '.'

The jq utility will either show properly formatted (and intended) JSON output or an error.

Conclusion

Provisioning using the OCI CLI is usually easier and more reliable (for consistency in deployments) than doing it manually through the web console.

Depending on your specific circumstances you may know most or all of the requisite command inputs already and depending on the exact tooling you choose to use, you may already have them defined as variables which can be passed into the oci psql db-system create command.  But if you don’t, or if you need to translate object names into their associated OCID values, then steps similar to those shown in this article can be used.