‘OCI Database with PostgreSQL’ cheat sheet
OCI has introduced a new service for PostgreSQL that broadens the OCI open-source database offerings portfolio which include OCI Cache with Redis, OCI Search with OpenSearch, and MySQL HeatWave.
OCI Postgres service is different from conventional open-source Postgres.
Currently, achieving zero RPO in open-source Postgres would be challenging with its out-of-box capability. Further, manageability and creation of read replicas are expensive and time-consuming affair.
OCI Postgres service addresses these challenges using Database Optimized Storage (DBOS). In OCI, DBOS solves problem by using shared-storage architecture which can be accessed by both primary node and replica node. DBOS uses quorum-based replication to replicate the data blocks between multiple availability domains. In case of failover, newly promoted primary instance leverage replicated DBOS layer to sustain with no data loss.
To know more about DBOS you can refer to the OCI blog.
Now, here is an architecture showing the differences of OCI Postgres service with open source Postgres database.
Like me, if you are equally exited to explore this service, here is the activity guide for you!
Prerequisites:
- An Oracle cloud account.
- Latest OCICLI installed in a VM
Activity 1# Provisioning of a multi node OCI PostgreSQL database
We will generate the json input from CLI to create the db system
oci psql db-system create --generate-full-command-json-input > createdbpsql.jsonUpdate the json file with necessary input. Here is a sample json file for reference
{
"compartmentId": "ocid1.compartment.oc1..a*************ro3kgzoi4b7vpfeyxq",
"displayName": "pgtest02",
"credentials": {
"username": "admin",
"passwordDetails": {
"passwordType": "VAULT_SECRET",
"secretId": "ocid1.vaultsecret.oc1.ap-mumbai-1.amaaaaaa**********j3cwlvniywlfrjcijlnrlpxs57wnqnlty5a",
"secretVersion": "1"
}
},
"networkDetails": {
"subnetId": "ocid1.subnet.oc1.ap-mumbai-1.aaaaaaa************5q3zex37oyjacr2pr2el77a7v4glv3ko2q"
},
"instanceCount": 2,
"shape": "PostgreSQL.VM.Standard.E4.Flex.2.32GB",
"dbVersion": "14",
"storageDetails": {
"iops": 300000,
"systemType": "OCI_OPTIMIZED_STORAGE",
"isRegionallyDurable": false,
"availabilityDomain": "ZVCk:AP-MUMBAI-1-AD-1"
},
"managementPolicy": {
"backupPolicy": {
"kind": "DAILY",
"retentionDays": 7,
"backupStart": "02:00"
}
}
}Though a password input can be used for DB, I have used the OCI vault secret to provision it. This will make the deployment secure.
Let’s provision the service now …
oci psql db-system create --from-json file://createdbpsql.jsonOnce provisioning is finished, we can list the service …
oci psql db-system-collection list-db-systems -c "ocid1.compartment.oc1..aaaa*******feyxq"
Output:
{
"data": {
"items": [
{
"compartment-id": "ocid1.compartment.oc1..aaa**********xq",
"config-id": "ocid1.postgresqldefaultconfiguration.oc1.ap-mumbai-1.amaaaaaayn42exyadjziu*********egjpnjdwakxfu72jq",
"db-version": "14.9",
"defined-tags": {
"Oracle-Tags": {
"CreatedBy": "oracleidentitycloudservice/*******@oracle.com",
"CreatedOn": "2023-11-16T08:41:58.487Z",
"CreatorType": "user",
"TimeToLiveDays": "14"
}
},
"display-name": "pgtest02",
"freeform-tags": {},
"id": "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaap77*******giyr23ixeebq",
"instance-count": 2,
"instance-memory-size-in-gbs": 32,
"instance-ocpu-count": 2,
"lifecycle-details": "DB system is active",
"lifecycle-state": "ACTIVE",
"shape": null,
"system-tags": {},
"system-type": "OCI_OPTIMIZED_STORAGE",
"time-created": "2023-11-16T08:42:01.121000+00:00",
"time-updated": "2023-11-16T08:50:15.092000+00:00"
}
]
},
"opc-next-page": "AA"
}Activity 2# Fetch the details of newly provisioned db system
oci psql db-system get --db-system-id "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaa********ixeebq"
output:
{
"data": {
"admin-username": "admin",
"compartment-id": "ocid1.compartment.oc1..aaaaaaaaxrmt*****************oi4b7vpfeyxq",
"config-id": "ocid1.postgresqldefaultconfiguration.oc1.ap-mumbai-1.amaaa*************jpnjdwakxfu72jq",
"db-version": "14.9",
"defined-tags": {
"Oracle-Tags": {
"CreatedBy": "oracleidentitycloudservice/*********@oracle.com",
"CreatedOn": "2023-11-16T08:41:58.487Z",
"CreatorType": "user",
"TimeToLiveDays": "14"
}
},
"description": null,
"display-name": "pgtest02",
"freeform-tags": {},
"id": "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaa*******ihnrgt2m4xqgfy4hdrcc3kfgiyr23ixeebq",
"instance-count": 1,
"instance-memory-size-in-gbs": 32,
"instance-ocpu-count": 2,
"instances": [
{
"availability-domain": "ZVCk:AP-MUMBAI-1-AD-1",
"description": null,
"display-name": "dbinstance-*****42add3",
"id": "e5fdaf76-0f2******65-3f870642add3",
"lifecycle-details": "DB instance is active",
"lifecycle-state": "ACTIVE",
"time-created": "2023-11-16T08:42:01.121000+00:00",
"time-updated": "2023-11-16T08:50:14.308000+00:00"
}
],
"lifecycle-details": "DB system is active",
"lifecycle-state": "ACTIVE",
"management-policy": {
"backup-policy": null,
"maintenance-window-start": "WED 02:00"
},
"network-details": {
"nsg-ids": null,
"primary-db-endpoint-private-ip": "10.0.20.87",
"subnet-id": "ocid1.subnet.oc1.ap-mumbai-1.aaaaaaaap******zex37oyjacr2pr2el77a7v4glv3ko2q"
},
"shape": "VM.Standard.E4.Flex",
"source": null,
"storage-details": {
"availability-domain": "ZVCk:AP-MUMBAI-1-AD-1",
"iops": 300000,
"is-regionally-durable": false,
"system-type": "OCI_OPTIMIZED_STORAGE"
},
"system-tags": {},
"system-type": "OCI_OPTIMIZED_STORAGE",
"time-created": "2023-11-16T08:42:01.121000+00:00",
"time-updated": "2023-11-16T08:50:15.092000+00:00"
},
"etag": "a4def2be5075b9958257e03d97429eded9******708013e2--gzip"
}Oh! I forgot to set the backup policy!
Let’s update it!
Activity 3# Update the DB system with new backup policy
Generate a json input using command prompt to set the custom management-policy
oci psql db-system create --generate-param-json-input management-policy > management-policy.jsonA sample json file to update the backup policy
{
"maintenanceWindowStart": "SUN 02:00",
"backupPolicy": {
"kind": "DAILY",
"retentionDays": 7,
"backupStart": "00:00"
}Now, update the db-system …
oci psql db-system update --db-system-id "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaap********cc3kfgiyr23ixeebq" --management-policy file://management-policy.jsonActivity 4# Connect to database
We will connect to the database using PGADMIN installed in local system via SSH tunnel…
We can leverage the postgreSQL client to connect the db as well.
psql "sslmode=require host=10.0.20.228 dbname=postgres
> user=admin password=********"
psql (14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------------+----------+-------------+-------------+---------------------------------
postgres | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser +
| | | | | oci_superuser=CTc/oci_superuser
template1 | oci_superuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser +
| | | | | oci_superuser=CTc/oci_superuser
(3 rows)Activity 5# Add a new node/ Add a replica
Let’s add a replica to primary node…
We will generate a json input to add a new node in existing db system
oci psql db-system patch --generate-full-command-json-input > patch.jsonUpdate the patch.json file with necessary input. Here is a sample json file for reference
{
"dbSystemId": "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaa*******xqgfy4hdrcc3kfgiyr23ixeebq",
"items": [
{
"operation": "INSERT",
"selection": "instances",
"value": {
"displayName": "testnode"
}
}
]
}Now add the node…
oci psql db-system patch --from-json file://patch.jsonWe can see a new node named testnode added in existing db system now.
Activity 6# Failover Testing
Let’s test the failover…
Let’s promote the replica with replica instance id…
oci psql db-system failover --db-system-id "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaa********dzc2tni3b2b57g5q" --db-instance-id "750509e6-2873-4a66-95e7-26cd9e4a12af"It just took few seconds…
Let’s check the primary db instance details to check if failover is successful.
oci psql db-system get-primary-db-instance --db-system-id "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaap*********unitsyt7xtkb227q2rylg72dzc2tni3b2b57g5q"
output:
{
"data": {
"db-instance-id": "750509e6-2873-4a66-95e7-26cd9e4a12af"
}
}It’s reflecting same instance id which we have just promoted as primary.
Activity 7# Delete a node/replica
We can leverage the same json file, patch.json to remove the replica node.
Let’s update the json file with the instance id of replica node, we want to delete.
A sample json file to remove the node.
{
"dbSystemId": "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaa******t7xtkb227q2rylg72dzc2tni3b2b57g5q",
"items": [
{
"operation": "REMOVE",
"selection": "instances[?id == 'ba5dd***-b0e7-43b549cd0fc9']"
}
]
}Command to remove…
oci psql db-system patch --from-json file://remove_node.jsonActivity 8# Delete db system
Don’t forget to delete the db system to avoid the unnecessary billing.
oci psql db-system delete --db-system-id "ocid1.postgresqldbsystem.oc1.ap-mumbai-1.amaaaaaa*******fgvyg3ja"Happy exploring!
Some useful links:
