Viewing PostgreSQL Logs from the OCI PostgreSQL Service

Background

After setting up a DB System in the new “OCI Database with PostgreSQL” service, one of the things DBAs will immediately look for is the PostgreSQL log.  Since this is an OCI-managed service, direct OS access, and consequently direct access to the log files, is not possible.  As of the time of writing, there are no obvious indications, documentation, or links with details on where to find the “OCI Database with PostgreSQL” logs from the “OCI Database with PostgreSQL” pages or online documentation at all.

Although it does not explicitly say it, the trick is to know that the PostgreSQL log files are exposed through the main, non-PostgreSQL specific, OCI Logging service.  But only after manually adding the PostgreSQL DB System to the Logging service – this setup is not done automatically when provisioning the DB System.

The OCI Logging Service

The OCI Logging service is a centralized location for ingesting and analyzing log files from any number of other OCI services, or even from custom and external applications and services.  The Logging service is unrelated to the new “OCI Database with PostgreSQL” service.  But it can ingest and manage the PostgreSQL database logs.

A summary of how the Logging service works is best summarized from the Oracle online documentation (https://docs.oracle.com/en-us/iaas/Content/Logging/Concepts/loggingoverview.htm):

* Screenshot date: 2023/11/23

The Log service is accessed from the OCI web console under “Observability & Management”.

Creating the Log Group & Enabling the Service Log

When enabling the Log Service for a PostgreSQL DB System, the log must be added to a “Log Group”.  One Log Group can hold logs from multiple services including multiple PostgreSQL DB Systems.

From the main Log Service page, click on Log Groups on the left-side menu, and then press the Create Log Group button if you need to create a new custom group:

TIP: Remember to check that you are working in the desired OCI Compartment!

Then give it a descriptive and purpose-built name:

After creating the new group, you can click on it by name to see its details.  Click on Logs under the Resources listing on the left -side, and then the Enable service log (light grey) button:

Alternatively, from the Logs option on the left-side menu, choose theo Enable service logs button:

Then choose PostgreSQL from the Service pick-list, and choose the name of your DB System from the Resource pick-list:

NOTE: If you have just recently provisioned your PostgreSQL DB System, it may take a few minutes for the system to be listed in the Resource pick-list.

Further down PostgreSQL Database Logs is the only available category in the Log Category list but you can give it a custom and sensible Log Name:

Clicking on, and expanding the Show Advanced Options link is recommended as it allows you to customize the Log Retention, and if required, adjust the Log Group:

When done customizing, press the Enable Log button at the bottom.  Wait a few minutes…. You can then start to see your DB System’s PostgreSQL log entries start to appear:

Searching and Exporting the PostgreSQL Logs

The ability to manipulate the data shown on the Logs page, in the Explore Logs section, is minimal.  For more functionality, you will need to use the Log Search screen.  This is accessed most easily by clicking on the Actions pull-down on the right side of the screen and selecting Explore with Log Search:

From the resulting Search screen, make whatever customizations are required.  For example, adjust the Filter by time search range.

Then if you wanted to export the log data for long-term retention, analysis outside of OCI, or even for ingestion into another logging service, choose the Export log data option from the Actions pull-down on the far right-side:

Unfortunately, this download uses minification meaning the downloaded JSON is a single line with minimal whitespace – not human readable at all.

This is easily converted into properly padded and human-readable JSON using the jq utility.  A command as simple as:

jq -s '.' Logs_2023-11-24_01_28_to_2023-11-24_01_43_UTC.json

Clean-up

When you remove your PostgreSQL DB System, the associated Log service is not automatically purged.  Which might make sense as you may have the need to review log data even after the database is gone.

If you do want to also remove the log data, remember to navigate to the Logging service and manually delete the Log for the removed PostgreSQL DB System.

Summary

For those new to the new “OCI Database with PostgreSQL” service, and those not overly experienced with OCI’s Logging service, how to view the PostgreSQL log file is fairly non-intuitive.  But the key things to know are:

1. The logs are not displayed on any of the PostgreSQL DB Service screens or pages.
2. The logs can be added to, and viewed from, the non-PostgreSQL- specific OCI Logging service.
3. The PostgreSQL logs are not imported into this service by default – they must be manually added.

Once these basic principles are understood, enabling and viewing the logs isn’t difficult at all.