Exporting PostgreSQL Logs to Object Storage

Prerequisites 🔗

Before you can configure a database system to export PostgreSQL logs to an Object Storage bucket, the bucket must exist and you must grant OCI Database with PostgreSQL resources access to upload to the bucket. If you need to create a new bucket, see Creating an Object Storage Bucket. To create policies that allow resources access to the bucket, see Required IAM Policy.

Required IAM Policy 🔗

OCI Database with PostgreSQL uses resource principals to authenticate and access other Oracle Cloud Infrastructure resources, such as Object Storage buckets. You can grant a database system, a specific set of database systems, or all database systems access to the Object Storage bucket.

To grant a specific database system access to read buckets and write objects to a bucket, use the following policies:

allow any-user to read buckets in compartment <compartment-name> where ALL {request.principal.id = '<dbsystem_OCID>', target.bucket.name = '<bucket_name>'}
allow any-user to manage objects in compartment <compartment-name> where ALL {request.principal.id = '<dbsystem_OCID>', target.bucket.name = '<bucket_name>'}

To grant all database systems access to a read buckets and write objects to a bucket, use the following policies:

allow any-user to read buckets in compartment <compartment-name> where ALL {request.resource.type = 'postgresqldbsystem', target.bucket.name = '<bucket name>'}
allow any-user to manage objects in compartment <compartment-name> where ALL {request.resource.type = 'postgresqldbsystem', target.bucket.name = '<bucket name>'}

Dynamic groups let you group OCI Database with PostgreSQL database systems as principal actors, similar to user groups. First, the database systems which need access must be put into a dynamic group, and then the dynamic group is granted access to upload objects to the bucket.

Membership in the dynamic group depends on a set of criteria you define, called matching rules. The following example shows a matching rule including all database systems in a defined compartment:

all { resource.type='postgresqldbsystem', resource.compartment.id = 'ocid1.compartment.oc1..exampleuniqueID'}

You can also use defined tags when creating dynamic groups for more specific access control, for example:

all { resource.type = 'postgresqldbsystem', tag.<tagnamespace>.<tagkey>.value='<tagvalue>'}

For more information, see Managing Dynamic Groups and Creating a Dynamic Group.

To grant dynamic groups access to read buckets and write objects to a bucket, use a policy such as the following:

Allow dynamic-group <PSQL_DynamicGroup> to read buckets in compartment <compartment-name> where ALL {target.bucket.name = '<bucket name>'}
Allow dynamic-group <PSQL_DynamicGroup> to manage objects in compartment <compartment-name> where ALL {target.bucket.name = '<bucket name>'}

Configuring a Database System for Log Export 🔗

After setting up resource principal access, configure the OCI Database with PostgreSQL database system to export logs to Object Storage by defining custom configuration parameters. You might need to create or copy a configuration to specify the following configuration parameters:

• oci.log_destination: oci_object_storage
• oci.log_destination_os_namespace: The Object Storage namespace for the tenancy
• oci.log_destination_os_bucket_name: The Object Storage bucket name to which the logs are exported

After you have a configuration that has log export details specified, apply that configuration to the database system. For more information, see Changing the Configuration Applied to a Database System.

Object Details 🔗

Logs are uploaded to the specified Object Storage bucket using the following naming convention:

<DBSYSTEM_ID>/<DB_INSTANCEID>/postgresql-<DATE>_######.csv.gz

• <DBSYSTEM_ID>: The ID of the OCI Database with PostgreSQL database system.
• <DB_INSTANCEID>: The instance ID of the PostgreSQL database.
• <DATE>: The date when the logs were generated, in YYYY-MM-DD format.
• ######: The sequence number.

For example:

mydbsystem/instance123/postgresql-2025-01-27_000001.csv.gz