Centralized Log Analytics: Integrating Aiven for PostgreSQL with OpenSearch

Introduction

As PostgreSQL deployments grow, so does the complexity of managing and analyzing database logs. While PostgreSQL’s native logging works well for single-service inspection, it becomes challenging when you need to search across time ranges, correlate events from multiple services, or build compliance dashboards.

This is where Aiven for OpenSearch comes in. In this post, we’ll explore how integrating Aiven for PostgreSQL with OpenSearch transforms log management from a basic inspection task into a powerful analytics capability.

Who should read this? Database administrators, DevOps engineers, and developers managing PostgreSQL in production who need better log visibility, compliance monitoring, or troubleshooting capabilities.

What is Aiven for OpenSearch?

Aiven for OpenSearch is a fully managed OpenSearch service—a distributed search and analytics suite you can deploy on the cloud of your choice. It excels at:

• Log management: Ingest, index, and search large volumes of log data
• Application search: Power full-text search for websites and applications
• Analytics: Perform aggregations and analysis on semi-structured data
• Visualization: Build dashboards using OpenSearch Dashboards       

Integration: PostgreSQL Logs to OpenSearch

How It Works

Aiven provides a native log service integration that automatically streams logs from your PostgreSQL service to OpenSearch. Here’s the flow:

1. PostgreSQL generates logs based on your configuration parameters (including pgAudit for audit logging)
2. Aiven’s infrastructure captures logs from your PostgreSQL service
3. The integration automatically parses PostgreSQL log formats and extracts fields and metadata (service node, cloud region, timestamps)
4. OpenSearch receives and indexes the logs, applying mappings to structure the data
5. Full-text indexing is performed on query text and other fields
6. Visualization layer allows you to use OpenSearch Dashboards to search, filter, and create custom dashboards

PostgreSQL Logs vs. OpenSearch Logs: Key Differences

Aspect	PostgreSQL	OpenSearch
Architecture	Single-node relational database	Distributed search engine
Log Storage	Single unified log file (or rotated by date)	Multiple specialized files (main, slow search, slow indexing, deprecation, audit, GC logs)
Log Format	Plain text	JSON-structured with metadata
Viewing Methods	Direct log file access via console/CLI	Interactive dashboards with custom visualizations
Search Capabilities	Basic grep/text search	Full-text search, filters, aggregations across time ranges
Scalability	Limited by single node	Distributed across cluster nodes

Key Use Cases

1. Powerful Search and Analytics Over Logs

OpenSearch enables full-text search, complex filters, and aggregations over all your PostgreSQL logs, including audit logs. Instead of manually parsing log files, you can:

• Search for specific query patterns across weeks or months of data
• Filter by severity, database, user, or custom fields
• Aggregate slow queries by table or operation type
• Identify trends and patterns that would be invisible in raw logs

Result: Easier troubleshooting across long time ranges and multiple services.

2. Visualization and Dashboards

OpenSearch Dashboards lets you build custom visualizations on top of your logs:

• Time-series graphs of query performance
• Pie charts showing query distribution by database or user
• Heatmaps of peak usage times
• Alert thresholds for abnormal patterns

This transforms reactive log analysis into proactive monitoring.

3. Centralized Logging from Multiple Services

You can send logs from multiple Aiven services—PostgreSQL, Kafka, Redis, OpenSearch itself, and Grafana—into one OpenSearch cluster. This provides:

• A single pane of glass for all your infrastructure logs
• Cross-service correlation (e.g., matching PostgreSQL slow queries with application errors)
• Simplified operational workflows

4. Compliance and Audit Logging with pgAudit

Enabling pgAudit in Aiven for PostgreSQL records detailed audit information:

• Who executed which queries
• DDL and DML operations with parameters
• Row-level access tracking
• Connection and authentication events

These audit logs stream to OpenSearch where you can:

• Build filtered views using AIVEN_AUDIT_FROM = pg
• Create compliance reports for regulatory audits
• Investigate security incidents
• Track schema changes and data modifications

5. Scale and Retention Management

OpenSearch is built to store and query large log volumes efficiently:

• Automatic index rotation (e.g., daily indices)
• Retention policies to delete old data automatically
• Horizontal scaling to handle growing log volumes
• Efficient compression and storage optimization

Setting Up the Integration

While detailed configuration steps are beyond this post’s scope, here’s the high-level process:

• Create an Aiven for OpenSearch service in your desired cloud region
  Enable the log integration in your PostgreSQL service settings
• Select OpenSearch as the destination for your logs
• Configure pgAudit (optional) for detailed audit logging
• Access OpenSearch Dashboards to start building visualizations
• Set up index patterns and create your first dashboard

For detailed instructions, refer to Aiven’s documentation.

Best Practices

• Start with default log levels and adjust based on your observability needs
• Use index lifecycle policies to automatically delete old logs and manage storage costs
• Create role-based dashboards for different teams (DBAs, developers, security)
• Set up alerts for critical patterns (repeated failures, security events, performance degradation)
• Document your retention policies to ensure compliance requirements are met

Conclusion: 

PostgreSQL’s native logs are sufficient for basic, local inspection of a single service. But when you need centralized, scalable log search and visualization, especially across multiple services and longer time periods, Aiven for OpenSearch becomes essential.

The integration provides powerful search capabilities, beautiful dashboards, compliance support through pgAudit, and operational efficiency through Aiven’s fully managed platform. By separating your transactional database from your log analytics layer, you get better performance, better insights, and lower operational overhead.

Ready to Get Started?

• Try it out: Set up a free trial of Aiven for OpenSearch and PostgreSQL
• Learn more: Visit Aiven’s documentation for detailed setup guides
• Need help? Contact Aiven’s support team for assistance with your specific use case