Oracle Cloud Infrastructure Documentation

OCI Database with PostgreSQL Troubleshooting

Use troubleshooting information to identify and address common issues that can occur while working with OCI Database with PostgreSQL.

Extension Creation Fails with "Not Allowed" Error

When creating an extension in an OCI Database with PostgreSQL environment, you receive an error that the extension is not allowed.

Most extensions supported by OCI Database with PostgreSQL are available for immediate use, but some require an administrator to enable the extension through a custom configuration. For example, the pg_stat_statements extension might be listed as supported, but isn't immediately usable.

If you try to create the pg_stat_statements extension and it's not enabled by the administrator, you might see an error. For example:

postgres=> CREATE EXTENSION pg_stat_statements;
ERROR:  Extension : pg_stat_statements is not allowed

To check if an extension such as pg_stat_statements is available, you can see the pg_available_extensions catalog, which shows the latest extensions and their version information. For example:

postgres=> SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

To see which extensions are enabled for your tenancy by administrators, use oci.admin_enabled_extensions. For example:

postgres=> SHOW oci.admin_enabled_extensions;
oci.admin_enabled_extensions 
------------------------------
postgres_fdw
(1 row)

If pg_stat_statements isn't listed, it means it's not enabled yet.

To enable these extensions, see Enabling Extensions for an OCI Database with PostgreSQL Database System.

Memory Utilization Remains High While the Database System is Idle

OCI Database with PostgreSQL metrics show high memory utilization, even when the database system is idle.

OCI Database with PostgreSQL does its own memory management using a custom page cache. OCI Database with PostgreSQL pre-allocates a fixed amount of memory for this custom page cache and doesn't use it for any other purposes.

With default settings, about 75% of the total memory of the database system is always allocated for oci.pagecache and shared_buffers. OCI Database with PostgreSQL Metrics show this amount as used, but this memory is used for PostgreSQL data buffers as intended and doesn't result in any negative effects.

For more information, see oci.pagecache_size.

Database System Provisioning Fails Because of Insufficient Host Capacity

When you create an OCI Database with PostgreSQL database system, the work request can fail because OCI doesn't have enough compute host capacity for the selected shape in the requested location. The error can identify the selected shape and, when applicable, the availability domain.

This failure means that OCI couldn't place the compute host resources required for the selected database system shape at the time of provisioning. It doesn't necessarily indicate a problem with the database system configuration. Capacity changes over time, so a later retry might succeed.

To resolve the issue, try the following:
  1. Retry after a short wait if the same shape and location are required.
  2. If the workload allows it, try a different availability domain in the same region.
  3. If the workload doesn't require a specific availability domain, omit the availability domain and let regional placement select an available location.
  4. If changing shapes is acceptable, try a different supported PostgreSQL shape or a smaller OCPU and memory configuration.

Before selecting a different shape, create an OCI Compute capacity report and verify that the candidate shape has sufficient availability. A capacity report generates availability-domain host capacity information that can help determine whether enough capacity is available before you create an instance or change an instance shape. For complete command syntax and examples, see oci compute compute-capacity-report create.

You must also have permission to create compute capacity reports. If you don't have this permission, ask an administrator who manages IAM policies for your OCI tenancy to grant it with a policy such as the following: 

Allow group <group-name> to manage compute-capacity-reports in tenancy

Create the shape-availabilities.json file for the candidate PostgreSQL compute shape and OCPU, and memory configuration. For example:

[
  {
    "instanceShape": "VM.Standard.E4.Flex",
    "instanceShapeConfig": {
      "memoryInGBs": 32.0,
      "ocpus": 2.0
    }
  }
]

Run the capacity report for the availability domain that you plan to use. The compartment OCID must be the root compartment OCID.

oci compute compute-capacity-report create \
  --availability-domain <availability-domain> \
  --compartment-id <root-compartment-ocid> \
  --shape-availabilities file://shape-availabilities.json
Review the report before selecting the candidate shape:
  • Verify that availability-status is AVAILABLE.
  • Verify that available-count is greater than or equal to the number of database system nodes that you need to provision.
  • If availability-status is OUT_OF_HOST_CAPACITY, don't select that shape for the checked availability domain. Check another availability domain or another supported PostgreSQL shape.
  • If availability-status is HARDWARE_NOT_SUPPORTED, the shape isn't supported in that location. Select a different supported shape or location.

A capacity report is a point-in-time signal and doesn't reserve capacity. After confirming that the candidate shape has enough availability, create the database system promptly.

If capacity remains unavailable after retries and alternative shape or availability domain checks, contact Oracle Support and provide the failed work request OCID, region, availability domain, original shape, candidate shapes checked, capacity-report results, and approximate retry times.