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. Google Cloud has it in Cloud SQL and adds a few PostgreSQL features into Spanner. Microsoft Azure was late, which initially had a small PostgreSQL running on Windows before acquiring Citus and integrating it into CosmosDB. Oracle 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 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:
I didn't follow this or read the documentation. 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 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
I define a VCN pg with a public subnet pub
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:
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:
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:
Setting private access:
Adding ingress rule to allow port 5432, the default PostgreSQL port:
PostgreSQL DB System
In OCI there is the Oracle Database and the others. You will find PostgreSQL in "Databases":
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:
Choose a new one:
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
I set 4 OCPU nodes which is 8 vCPU (VM.Standard.E4.Flex is AMD processor with hyperthreading)
I set it in my private subnet created before:
I define the admin username as admin and a password to remember (it can be changed later with Reset administrator credentials)
Interestingly, the last character of the password is displayed
It takes a few minutes to create the service:
I can already see my 3 nodes creating:
This took 8 minutes in total:
The endpoint for read-write connections is displayed in the DB System details
Connect
I connect to my compute instance in the public subnet (ssh opc@141.79.76.73) and can connect to the PostgreSQL database:
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.
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:
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
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.
Top comments (0)