Table of Contents

Introduction

On 14th of November Oracle released new database-related cloud service: OCI Database with PostgreSQL. Thanks to this our customers can use one of the most advanced open source RDBMS in the cloud deployment model. In this article I would like to shortly present this new service.

Service Overview

OCI Database with PostgreSQL service page is accessible from the list of all database-related services:

OCI Database with PostgreSQL

It is a fully managed service, offering maximum level of automation, currently supporting PostgreSQL 14.9 and providing the following features and functionalities:

  1. High Availability architecture: it is possible to provision a system consisting of a maximum of 8 nodes, where 1 of them works as a primary (in RW mode) while the rest of them are secondary ones, working in RO mode. In the multi-node configuration Oracle guarantees 99.99% of availability, RTO less than 2 minutes. Of course traditional backups (automatic as well as manual) of protecting the database against failure are also available, offering RPO of 0 and no data loss. In case when a multinode system is being provisioned the customer can choose one of the following data placement modes:
    • regional, where nodes will be located in different availability zones
    • availability-domain specific, where nodes will be located in different fault domains withing a single one availability domain
  2. Long list of supported extensions, like, for example:
    • plpgsql for working with stored PgPL/SQL programs,
    • cube for working in multidimensional cubes
    • and many more…
  3. Multiple shapes: from 2 OCPUs with 32GB of RAM up to 64 OCPUs with 1TB of RAM
  4. Database-optimized storage that scales independently of allocated compute resources, which guarantees up to 300k IOPS
  5. Stored configurations for easy provisioning of complex ecosystems consisting of multiple identically configured database systems.

Provisioning

Provisioning procedure consists of three steps/pages. 

  1. In the first step we need to choose creation type:
    • Create a new db system –  to create a totally new and empty database or 
    • Create db system from backup – to create a database from an existing backup.
  2. In the second step we need to provide more details about the newly created system
    • DB System Name
    • Number of nodes
    • Data placement model
    • Shape – in “Hardware Configuration” section
    • Backup configuration: manual or automatic
      • in the second case we can also provide the frequency of backups: daily, weekly or monthly
    • Database administrator details: username, method of authentication
      • OCI Vault (default) or
      • password
    • Network configuration section allows for providing information about: VCN, private subnet and (optionally) private IP.
      • Note: Currently OCI Database with PostgreSQL does not support public subnets. In case when we try to use such subnet we will receive the following error message during the service provision request submition:

      • Error message

  3. And finally the summary, which allows for review all the details we provided previously and submitting the request.

Using the service

As the only type of subnet, which can be assigned to an OCI PostgreSQL Database system is the private one, then the system is not accessible from Internet. However – it is fully accessible from within its subnet. So to using a command line tool, like, for example psql, we need to provision a bastion compute instance and install the tool we want to use on this machine. After we do this, the database can be accessed, connecting to its RW endpoint:

Endpoint.

or directly to a node (for example to read-only replica to query the data):

ro node

[opc@ws-bastion-host ~]$ psql -h 10.0.1.224 -d postgres -U admin
Password for user admin:
psql (10.23, server 14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> create database nice_try;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction

postgres=> \l
                                         List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+---------------+----------+-------------+-------------+---------------------------------
 demo      | admin         | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 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
(4 rows)

The service page allows for  (additionally to reviewing basic details of the database system, of course) 

  • performing manual backups
  • viewing different metrics/performance statistics

Summary

PostgreSQL DBMS is widely used by our customers and is also very often considered as the most advanced open source database management system. The service described in this article allows our customers to use this database in Oracle Cloud in some simpler cases also simplifying significantly its usage. 

Resources