OpenTelemetry Psycopg2 Instrumentation

Warnings

• breaking Including SQLCommenter in the `db.statement` span attribute became opt-in from OpenTelemetry Python Contrib version 1.29.0/0.50b0.
  Fix: To include SQLCommenter in the `db.statement` attribute, explicitly set `enable_attribute_commenter=True` when calling `Psycopg2Instrumentor().instrument()`.
• gotcha Enabling SQLCommenter (`enable_commenter=True`) can lead to high cardinality in database span attributes if not managed, potentially increasing monitoring costs and reducing performance in some backends.
  Fix: Evaluate the need for SQLCommenter carefully. If enabled, consider using `commenter_options` to opt out of specific key-value pairs (`Psycopg2Instrumentor().instrument(enable_commenter=True, commenter_options={'db_driver': False})`) to control cardinality.
• gotcha Recursive tracing issues can occur when using `psycopg2.pool.ThreadedConnectionPool` with Python versions earlier than 3.9.
  Fix: Upgrade your Python environment to 3.9 or higher to mitigate this issue. If upgrading is not possible, avoid using `ThreadedConnectionPool` with this instrumentation.
• gotcha From OpenTelemetry Python Contrib version 1.32.0/0.53b0, instrumentors perform lazy-import dependency checks. If the underlying `psycopg2` (or `psycopg2-binary`) package is missing, `instrument()` might raise an `ImportError`.
  Fix: Always ensure `psycopg2` or `psycopg2-binary` is installed alongside `opentelemetry-instrumentation-psycopg2`. In rare cases, `skip_dep_check=True` can be passed to `instrument()` if you manage dependencies manually or encounter unexpected issues with multiple `psycopg` distributions.
• gotcha Using `opentelemetry-instrumentation-psycopg2` alongside other APM or tracing libraries that also instrument `psycopg2` can lead to conflicts, duplicate traces, or prevent database queries from being collected.
  Fix: Avoid using multiple instrumentation libraries for the same underlying component. If you must, ensure to test thoroughly and potentially disable one of the instrumentations.

Install

• pip install opentelemetry-instrumentation-psycopg2 psycopg2-binary Install with psycopg2-binary
• pip install opentelemetry-distro opentelemetry-exporter-otlp Install base OTel packages (if not already)

Imports

• Psycopg2Instrumentor

  from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor

Quickstart

This quickstart demonstrates how to instrument `psycopg2` to automatically generate traces for database interactions. It configures a simple ConsoleSpanExporter to print traces to the console and performs basic PostgreSQL operations. Ensure you have a PostgreSQL instance running, for example, using Docker.

import psycopg2
from opentelemetry import trace
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import ConsoleSpanExporter, SimpleSpanProcessor
from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor
import os

# Configure OpenTelemetry SDK
resource = Resource.create({"service.name": "my-psycopg2-app"})
provider = TracerProvider(resource=resource)
processor = SimpleSpanProcessor(ConsoleSpanExporter())
provider.add_span_processor(processor)
trace.set_tracer_provider(provider)

# Instrument psycopg2
# Pass enable_commenter=True and enable_attribute_commenter=True for SQL Commenter support
# Psycopg2Instrumentor().instrument(enable_commenter=True, enable_attribute_commenter=True, skip_dep_check=True)
Psycopg2Instrumentor().instrument()

# Database connection details (use environment variables or sensible defaults for quickstart)
db_name = os.environ.get('POSTGRES_DB', 'testdb')
db_user = os.environ.get('POSTGRES_USER', 'user')
db_password = os.environ.get('POSTGRES_PASSWORD', 'password')
db_host = os.environ.get('POSTGRES_HOST', 'localhost')
db_port = os.environ.get('POSTGRES_PORT', '5432')

try:
    # Connect to PostgreSQL
    conn = psycopg2.connect(dbname=db_name, user=db_user, password=db_password, host=db_host, port=db_port)
    cursor = conn.cursor()

    # Execute some SQL queries
    with trace.get_tracer(__name__).start_as_current_span("db_operations"):
        cursor.execute("CREATE TABLE IF NOT EXISTS otel_test (id serial PRIMARY KEY, name VARCHAR(255))")
        cursor.execute("INSERT INTO otel_test (name) VALUES (%s)", ("OpenTelemetry",))
        cursor.execute("SELECT * FROM otel_test")
        result = cursor.fetchall()
        print(f"Fetched result: {result}")

    conn.commit()
    cursor.close()
    conn.close()
    print("Database operations completed and traces should be visible.")
except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure a PostgreSQL database is running and accessible (e.g., via Docker):")
    print("docker run --rm --name some-postgres -e POSTGRES_DB=testdb -e POSTGRES_USER=user -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres")