Oracle Cloud Infrastructure Documentation

Logging for OCI Database with PostgreSQL

Use this information to enable and manage logs for OCI Database with PostgreSQL database systems.

The OCI Database with PostgreSQL service uses the OCI Logging service to manage logging for database systems.

Note

Logging is an option in the OCI Database with PostgreSQL service. Standard limits, restrictions, and rates apply when enabling the logging features. See Logging for more information.

Enabling Logging

OCI Database with PostgreSQL service logs are enabled at the database system resource level. To enable logging, you need to know the PostgreSQL database system name and the name of the compartment that contains the database system.

To enable logging for a database system using the Console, see Enable Logging on the Logs page. When you enable logging, select PostgreSQL as the service and select PostgreSQL Database Logs as the log category.

Tip

The Logging service uses log groups to organize and manage logs in logical containers. For more information, see Logs and Log Groups.

To enable logging for a database system using the API or CLI, see Creating a Log. If you use the CLI to enable logging for OCI Database with PostgreSQL, you can use the --configuration file://config.json option and provide a configuration file such as the following:

{
    "compartment-id" : "<compartment_OCID>",
    "source": {
        "resource" : "<workspace_OCID>",
        "service" : "postgresql",
        "source-type" : "OCISERVICE",
        "category" : "postgresql_database_logs"
    }
}

For more information about logging, see Logging Overview.

Viewing Logs

To view OCI Database with PostgreSQL logs, see Getting a Log's Details.

For details about the contents of a database system log, see Contents of a Database Log and An Example OCI Database with PostgreSQL Log.

Resources

  • database systems

Log Categories

API value (ID): Console (Display Name) Description
postgresql_database_logs PostgreSQL Database Logs PostgreSQL database system logs.

Availability

OCI Database with PostgreSQL logging is available in all the regions of the commercial realms.

Contents of a Database Log

OCI Database with PostgreSQL logs capture detailed information about database queries and parameters. Details appear as values in the data field. This value is a JSON-formatted data with the following fields.

  • dbSystemId
  • dbInstanceId
  • user_name
  • database_name
  • process_id
  • connection_from
  • session_id
  • session_line_num
  • command_tag
  • session_start_time
  • virtual_transaction_id
  • transaction_id
  • error_severity
  • sql_state_code
  • level
  • message
  • detail
  • hint
  • internal_query
  • internal_query_pos
  • conString
  • query
  • query_pos
  • location
  • application_name
  • backend_type
  • leader_pid
  • query_id
Note

For more information on these properties, see Error Reporting and Logging in the PostgreSQL reference manual.

An Example OCI Database with PostgreSQL Log

{ 
"specversion" : "1.0", 
"type" : "com.oraclecloud.postgresql.postgresqlDbSystem.postgresql_database_logs", 
"source" : "DBSYSTEM_NAME", 
"subject" : "dbinstance-ff36735edea9"
"id" : "DBSYSTEMNAME_2e594e66-11e9-4841-bfc6-dc7daf501a86", 
"time" : "2023-06-06T21:02:40.584Z", 
"oracle": { 
    "logid": "ocid1.log.region1...exampleuniqueID",
    "ingestedtime": "2022-05-22T04:17:31.222Z"
    }, 
"data" : {       
    "dbSystemId": "ocid1.postgresqldbsystem.oc1.iad.exampleuniqueID",
    "dbInstanceId": "c8418d0a-93fa-4b3f-ac69-ff36735edea9",      
    "user_name":"oci_metrics",
    "database_name":"postgres",
    "process_id":2113,
    "connection_from":"172.17.0.1:49218",
    "session_id":"648a97e8.841",
    "session_line_num":9,
    "command_tag":"startup",
    "session_start_time":"2023-06-15 04:47:36 GMT",
    "virtual_transaction_id":"3/544",
    "transaction_id":0,
    "error_severity":"LOG",
    "sql_state_code":"00000",
    "level":"LOG",
    "message":"setting \"DateStyle\"=ISO",
    "detail":"",
    "hint":"",
    "internal_query":"",
    "internal_query_pos":null,
    "conString":"",
    "query":"",
    "query_pos":null,
    "location":"",
    "application_name":"postrgesql",
    "backend_type":"client backend",
    "leader_pid":null,
    "query_id":0
   } 
}