Introduction

This repository holds several Fluentd plugins to parse PostgreSQL CSV log files and extract slow log information:

  • MultilineCSVParser: Parses CSV files that span multiple lines
  • PostgreSQLSlowLog: Extracts slow log entries into duration_s and statement fields
  • PostgreSQLRedactor: Normalizes the SQL query and redacts sensitive information
  • Marginalia: Parses Marginalia comments into key-value pairs and stores them
  • PgStatStatementsInput: polls the pg_stat_statements postgres plugin and emits fluentd events.
  • PgStatActivityInput: polls the postges activity monitor and emits fluentd events.

Installation

Use RubyGems:

fluent-gem install fluent-plugin-postgresql-csvlog --no-document

Configuration

PostgreSQL configuration

log_destination = 'csvlog'
log_collector = on

Fluentd conf

The configuration below shows how you might use these filters to ingest and parse PostgreSQL CSV logs:

<source>
  @type tail
  tag postgres.postgres_csv
  path /var/log/postgresql/*.csv
  pos_file /var/log/td-agent/postgres_csv.log.pos
  <parse>
    @type multiline_csv
    format_firstline /^\d{4}-\d{1,2}-\d{1,2} \d{2}:\d{2}:\d{2}/
    keys time,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,message,detail,hint,internal_query,internal_query_pos,context,query,query_pos,location,application_name
    time_key time
    time_format %Y-%m-%d %H:%M:%S.%N %Z
    keep_time_key true
    parser_type normal
  </parse>
</source>

<filter postgres.postgres_csv>
  @type postgresql_slowlog
  output_key query
</filter>

<filter postgres.postgres_csv>
  @type postgresql_redactor
  input_key query
  output_key sql
  fingerprint_key fingerprint
</filter>

<filter postgres.postgres_csv>
  @type marginalia
  key sql
  strip_comment true
</filter>

# Output resulting JSON file to a directory in /tmp
<match postgres.*>
  @type file
  path /tmp/fluent-test
  <format>
    @type json
  </format>
</match>

Developing fluent-plugin-postgresql-csvlog

To develop and debug locally, there is a Dockerfile and docker-compose.yml that will setup a local environment, complete with Postgres, suitable for testing purposes.

  1. docker compose build - build the current configuration
  2. docker compose run --rm verifier - test the current configuration
  3. docker compose up

Releasing a new version

  1. Update the version in fluent-plugin-postgresql-csvlog.gemspec.
  2. Create a merge request and merge the changes to master.
  3. Run bundle exec rake release.