Create PostgreSQL Custom OCI Monitoring Alarms and Dashboard using Container Instances

Introduction

This tutorial provides step-by-step instructions to deploy a custom monitoring solution for PostgreSQL using a Docker based Java application on Oracle Cloud Infrastructure (OCI). We will demonstrate how to create custom alerts and dashboards for inactive replication slots and replication lag in OCI using a virtual machine (VM) based container. While the current setup focuses on monitoring PostgreSQL replication lag and inactive replication slots, the design is flexible and can be easily extended to track additional database metrics by modifying the application code.

Objectives

Task 1: Download and Set Up the Monitoring Service

  1. Download the Postgres-monitoring-services.zip file from here: Postgres-monitoring-services.zip.

  2. Unzip and follow the instructions of README.md file to build and run the application.

  3. Docker build and deployment instructions are included in the repo.

    Note: Instructions for building and pushing the Docker image are already available in the project repository’s README.md.

Task 2: Create a Dynamic Group

Dynamic groups allow your container VM to access OCI services like OCI Monitoring, OCI Vault, and OCI Registry without using hardcoded credentials and without storing credentials in your image.

Use the following information:

Note: compartment.id is compartment OCID where the container instance will be created.

Task 3: Configure the Network (VCN and Service Gateway)

To ensure seamless communication, place your container VM in the same private subnet as your PostgreSQL database system. Enable access to OCI services using a service gateway.

Security List Rules:

Ensure the following rules are added to your private subnet’s security list, if they are not already present.

Type Source CIDR Protocol Port Purpose
Ingress private-subnet-CIDR TCP 5432 PostgreSQL Access
Egress 0.0.0.0/0 All All Access OCI Services

Task 4: Use OCI Vault for Secure Password Storage

Use OCI Vault for secure password storage to avoid hardcoding database credentials.

  1. Create a Vault and add a Secret containing the PostgreSQL password.

  2. Record the Vault Secret OCID.

  3. Reuse the secret if it already exists.

Task 5: Define Oracle Cloud Infrastructure Identity and Access Management Policies

Attach these OCI IAM policies.

Allow dynamic-group psql_monitoring_with_container_vm_dg to read vaults in compartment <your-compartment-id>
Allow dynamic-group psql_monitoring_with_container_vm_dg to read secret-bundles in compartment <your-compartment-id>
Allow dynamic-group psql_monitoring_with_container_vm_dg to use metrics in compartment <your-compartment-id>
Allow dynamic-group psql_monitoring_with_container_vm_dg to read repos in compartment <your-tenancy-id>

Task 6: Launch the Container Instances

Go to the OCI Console, navigate to Developer Services, Container Artifact, Container Instances, Create Instance and enter the following information:

Note: Access is granted without credentials through OCI IAM dynamic group.

image

Task 7: Check Logs in the OCI Console

Go to the OCI Console, navigate to Developer Services, Container Artifact, Container Instances, select your instance, and click the Containers tab to inspect logs and runtime status.

Task 8: View Metrics in Metrics Explorer

Go to the OCI Console, navigate to Observability & Management, Metrics Explorer, and enter the following information:

Example Query:

replication_lag_metric[1m]{DBSystemId = "<your-db-system-ocid>"}.mean()

Task 9: Set Up an OCI Notifications Topic and Subscriptions

  1. Go to the OCI Console, navigate to Application Integration and click Notifications.

  2. Create a Topic. For example, PostgresCustomMonitoringAlerts.

  3. Add Subscriptions (email, webhook, and so on).

  4. Link this topic to your alarms for real-time alerting.

Task 10: Configure OCI Monitoring Alarms for Key Metrics

image

image

Task 11: Create a Custom Dashboard

  1. Go to the OCI Console, navigate to Observability & Management and click Dashboards.

  2. Click New Dashboard.

  3. Add widgets using Metric Chart or Query Builder.

  4. Enter the following information.

    • Namespace: Enter postgres_monitor_with_container_vm.
    • Metrics: Select replication_lag_metric and inactive_slot_metric.

  5. Customize chart types, filters, and time range.

image

Summary Table

Component Purpose
Dynamic Group Grants OCI access to container VM
OCI Vault Secure database credential storage
OCI Registry Host Docker image repository
Monitoring Agent Collects custom PostgreSQL metrics
Alarms Alerts on lag, slots, missing data
Container VM Runs the Java-based monitoring tool
Dashboard Visualizes metrics in real-time

Serverless and Resilient Architecture Highlights

Troubleshooting and Issues

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.