Oracle Cloud Infrastructure Optimized PostgreSQL 14.9
Tags: oci, oracle, postgres, cloud
Published: 2023-11-23 07:43:48

PostgreSQL-compatible databases are gaining popularity, and many cloud providers now offer one or more managed services. The most well-known is AWS, with [RDS PostgreSQL and Amazon Aurora](https://dev.to/aws-heroes/postgresql-in-aws-clearing-the-doubts-44ep). Google Cloud has it in [Cloud SQL](https://cloud.google.com/sql/docs/postgres/) and adds a few PostgreSQL features into [Spanner](https://cloud.google.com/blog/products/databases/understanding-spanners-postgresql-interface). Microsoft Azure was late, which initially had a small PostgreSQL [running on Windows](https://www.dbi-services.com/blog/azure-database-for-postgresql/) before acquiring Citus and integrating it [into CosmosDB](https://dev.to/yugabyte/is-cosmosdb-a-new-sql-database-is-citusdb-a-distributed-sql-did-hyperscale-vanished-in-the-hyperspace-472d). [Oracle Cloud](https://blogs.oracle.com/cloud-infrastructure/post/first-principles-optimizing-postgresql-for-the-cloud) is the latest entrant in the race, obviously facing internal competition with Oracle Database and MySQL-managed services.

---

**(*)** I use "**PostgreSQL-compatible**" to refer to **managed services** that are based on the community PostgreSQL. These services may not offer 100% of the features available when you compile the postgresql sources or use the official binaries, and must provide a security frontier such as disallowing host access. Therefore, the original PostgreSQL is forked to add some security features. Additionally, these services often improve the storage layer, for better availability and performance.

---

In this blog post, I'm looking at the **Oracle Cloud PostgreSQL** service which was announced at Oracle Cloud World and is now available. I'm testing it on a [Free Trial](https://signup.cloud.oracle.com/) where I have 300$ credits for 30 days. If you do the same, choose one one of the few regions that provide more than one Availability Domains: UK South (London), US East (Ashburn), US Midwest (Chicago), US West (Phoenix)

I am used to AWS, where almost all regions have three availability zones. I forgot this and used a single AD region, which means that I cannot test the performance in a multi-AZ environment, a requirement for high availability.

---

## Provision an new PostgreSQL DB System

"DB System" is the term Oracle uses for a configuration with multiple replicas. I think this term was introduced with Oracle Enterprise Manager for Oracle RAC and Data Guard.

There's a lot of manual prerequisites listed when you try  to create a managed PostgreSQL on OCI:
![Prerequisites](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g7cfr2et86edv972rmbd.png)
I didn't follow this or read the [documentation](https://docs.oracle.com/en-us/iaas/Content/postgresql/home.htm). This lab examines a new service, which I don't think is production ready.

The PostgreSQL instance(s) cannot be deployed on a public subnet. Rather than using the [VCN Wizard](https://cloud.oracle.com/networking/solutions/vcn) I'll first create a Compute instance in a public subnet (I'll use it as a bastion to access my database) and will add a private subnet later.

---

## Public subnet with compute instance

As I may continue to use it after the 30-day trial, I created a 4 vCPU Arm machine with 24GB RAM (VM.Standard.A1.Flex) with Oracle Linux 8

![Create compute instance](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qoj1brhg3v7sty1mvsbb.png)

![Image and shape](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bgo0m4pjwxka9l0ji4ts.png)

I define a VCN `pg` with a public subnet `pub`

![Primary VNIC information](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6czgchcur98bkkzyvkig.png)

This is straightforward. I have a public IP and hostname which I can `ssh` into.

I add it in my SSH `config` file, in `.ssh` for fast access and will include port forwarding when I'll have the PostgreSQL endpoint. When I will have the postgres endoints, it will look like this: 
```sh
Host opg
  HostName 141.79.76.73
  User opc
  LocalForward 15432 10.0.1.3:5432
  LocalForward 15442 10.0.1.4:5432
  LocalForward 15452 10.0.1.5:5432
```

I can `ssh opg` and install the latest PostgreSQL client (PostgreSQL 16). I've no idea why, but I needed to disable GPG key checking:
```sh
sudo dnf --nogpgcheck install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf --nogpgcheck update -y
sudo dnf --nogpgcheck -qy module disable postgresql
sudo dnf --nogpgcheck install -y postgresql16
```

My bastion is ready to connect to PostgreSQL. To be able to create the PostgreSQL service, I need to add a private subnet to my VCN.

---

## Private Subnet in same VCN

Creating a new subnet:
![Create Subnet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c9ispj1x7za1pkng9k04.png)

Setting private access:
![Subnet Access](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k30rm7zk9a2jdlvxgith.png)

Adding ingress rule to allow port 5432, the default PostgreSQL port:
![Edit Ingress Rule](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n6t8vtih6lyf7uvkkjj0.png)

---

## PostgreSQL DB System

In OCI there is the Oracle Database and the others. You will find PostgreSQL in "Databases":

![Databases](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/strcemmq2lof4w4arkl2.png)

It is important to create the VCN and private subnet beforehand. With a public subnet, the creation will fail with:
```
Invalid subnetId: ocid1.subnet.oc1.eu-paris-1.aaaaaaaad3hi5ebgxlcgv6objwojajzowu4vqdhvpjbicwhqwctvu27slrxa is not a private subnet
```

Ready to create a PostgreSQL DB System:
![Overview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/asnl9d7mfd30xqrd99w6.png)

Choose a new one:
![Create PostgreSQL DBSystem](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4iy7astoqk9gxo5f0c1r.png)

Choose the version (only one is availaable currently) and the number of nodes. Only one node will be the primary where you can connect your application
![Database system](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cbf5svh7c5qw93m6dko5.png)

I set 4 OCPU nodes which is 8 vCPU (VM.Standard.E4.Flex is AMD processor with hyperthreading)
![Hardware configuration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/72bz1cb9ve41hdquivzl.png)

I set it in my private subnet created before:
![Network configuration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pjk52vz9wh5x0zcnd91d.png)

I define the admin username as `admin` and a password to remember (it can be changed later with Reset administrator credentials)
![DBSystem administrator credentials](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/33b9kpqr0xf20d8gp38c.png)

Interestingly, the last character of the password is displayed
![DBSystem administrator credentials](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5ntsamoxbt2gfjwea57p.png)

It takes a few minutes to create the service:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1hbkc45lkvhqma6h4dei.png)

I can already see my 3 nodes creating:
![DBSystem nodes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9430rodwbijty5fk4x11.png)

This took 8 minutes in total:
![Work request details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zdr457erh1mp5641iy26.png)

---

The endpoint for read-write connections is displayed in the DB System details
![Connection details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3e7kagqi3c03mg8sfljr.png)

---

## Connect

I connect to my compute instance in the public subnet (`ssh opc@141.79.76.73`) and can connect to the PostgreSQL database:
```sh
PGHOST=10.0.1.3
PGUSER=admin
PGPASSWORD=bsky-social-6ry3x-jclea
PGDATABASE=postgres
export PGHOST PGUSER PGPASSWORD PGDATABASE
psql
```

You can also use a client-side certificate with `PGSSLMODE=verify-full` and set PGSSLROOTCERT to the file you have downloaded the `CA certificate` mentioned under the connection details.

```sql
psql (16.1, server 14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

postgres=> select version();
                version
---------------------------------------
 OCI Optimized PostgreSQL 14.9, 64-bit
(1 row)

postgres=> select pg_is_in_recovery ();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=> show transaction_read_only;
 transaction_read_only
-----------------------
 off
(1 row)

postgres=> select * from pg_replication_slots;
              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1225684 |              | 0/AC7C548   | 0/AC7C5F8           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1225684 |              | 0/AC7C548   | 0/AC7C5F8           | reserved   |               | f
(2 rows)

```

There are two replication slots for the physical standbys (as I've created a 3 nodes DB System).

---

## Replication

I run some work, updating rows in a loop:
```sql
postgres=> drop table if exists demo ; create table demo as select 1 as n; update demo set n=n+1 returning pg_current_wal_insert_lsn() \; select * from pg_replication_slots 
\watch c=10

SELECT 1
  xmin
---------
 1241623
(1 row)

UPDATE 1
                                                                                      Tue 21 Nov 2023 08:43:14 AM GMT (every 2s)

              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1241622 |              | 0/AC80D68   | 0/AE97908           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1241621 |              | 0/AC80D68   | 0/AE97908           | reserved   |               | f
(2 rows)

                                                                                      Tue 21 Nov 2023 08:43:16 AM GMT (every 2s)

              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1241622 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1241621 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
(2 rows)

                                                                                      Tue 21 Nov 2023 08:43:18 AM GMT (every 2s)

              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1241622 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1241624 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
(2 rows)

                                                                                      Tue 21 Nov 2023 08:43:20 AM GMT (every 2s)

              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1241622 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1241624 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
(2 rows)

                                                                                      Tue 21 Nov 2023 08:43:22 AM GMT (every 2s)

              slot_name               | plugin | slot_type | datoid | database | temporary | active | active_pid |  xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
--------------------------------------+--------+-----------+--------+----------+-----------+--------+------------+---------+--------------+-------------+---------------------+------------+---------------+-----------
 f2d72f07-4392-40a3-98c8-e1311694e968 |        | physical  |        |          | f         | t      |         86 | 1241624 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
 2badc337-efbd-42be-87d3-3ea5f0df2517 |        | physical  |        |          | f         | t      |         90 | 1241624 |              | 0/AC80D68   | 0/AE97980           | reserved   |               | f
(2 rows)
```

I insert rows with the current timestamp:
```
postgres=> alter table demo add ts timestamptz default clock_timestamp();
ALTER TABLE

postgres=> insert into demo(n) values (1) returning *
\watch 0.001

Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

 n |              ts
---+-------------------------------
 1 | 2023-11-21 09:07:00.029231+00
(1 row)

INSERT 0 1
Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

 n |              ts
---+-------------------------------
 1 | 2023-11-21 09:07:00.030221+00
(1 row)

INSERT 0 1
Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

 n |              ts
---+-------------------------------
 1 | 2023-11-21 09:07:00.031214+00
(1 row)

INSERT 0 1
```

While it is running, I connect to a read replica.
The connection info is in DBSystem nodes -> View Details

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9xa8yhyso0bmhv66aejz.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s7i37n5700t1gqxbohr4.png)

```sql
postgres=> select max(ts),now()-max(ts) as gap
 ,pg_is_in_recovery(),pg_is_wal_replay_paused(), 
 pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), 
 pg_last_xact_replay_timestamp() from demo
\watch

                                                                                                                                      Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

              max              |      gap        | pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------------------+-----------------+-------------------+-------------------------+-------------------------+------------------------+-------------------------------
 2023-11-21 09:06:59.994215+00 | -00:00:01.38154 | t                 | f                       | 0/C24E2B0               | 0/C24E2B0              | 2023-11-21 09:07:00.006238+00
(1 row)

                                                                  Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

              max              |     ?column?     | pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------------------+------------------+-------------------+-------------------------+-------------------------+------------------------+-------------------------------
 2023-11-21 09:07:00.006224+00 | -00:00:01.381578 | t                 | f                       | 0/C24E850               | 0/C24E850              | 2023-11-21 09:07:00.0183+00
(1 row)

                                                                  Tue 21 Nov 2023 09:07:00 AM GMT (every 0.001s)

              max              |     ?column?     | pg_is_in_recovery | pg_is_wal_replay_paused | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------------------+------------------+-------------------+-------------------------+-------------------------+------------------------+-------------------------------
 2023-11-21 09:07:00.026213+00 | -00:00:01.381645 | t                 | f                       | 0/C24F6F0               | 0/C24EE68              | 2023-11-21 09:07:00.031227+00
(1 row)

```

Based on these observations, it appears that the replication being used is an asynchronous physical standby replication within the same zone (Availability Domain) with an average gap of one second.

It's important to note that synchronizing storage across Availability Domains or Failure Zones does not necessarily mean that you can read from it. Read Replicas function like other instances in a monolithic database. They read first from their in-memory buffer cache and use WAL streaming to keep the cache updated with primary database writes. They can read as of the last LSN (Log Sequence Number) received. In the event of a cache miss, they will read from the copy-on-write disk as of the same point in time. With asynchronous replication, read replicas are eventually consistent.

When compared to AWS services, the Oracle PostgreSQL managed service falls somewhere between RDS PostgreSQL and Amazon Aurora. The storage replication is similar to that of RDS PostgreSQL Multi-AZ, but WAL streaming is used to invalidate the cache, similar to Aurora. The difference between Aurora and storage replication is in their handling of checkpoints: Aurora applies the Write-Ahead Log (WAL) within the storage cells, while storage replication uses PostgreSQL checkpointing and replicate all writes.