We use cookies or similar technologies to personalize your online experience and tailor marketing to you. Many of our product features require cookies to function properly. Your use of this site and online product constitutes your consent to these personalization technologies. Read our Privacy Policy to find out more.

X
  1. Docs
  2. Get your data in
  3. Other integrations
  4. Databases
  5. PostgreSQL
  6. Self-hosted PostgreSQL

Getting PostgreSQL logs into Honeycomb

Our connector pulls your PostgreSQL logs into Honeycomb for analysis, so you can finally get a quick handle on the database queries triggered by your application logic. It surfaces attributes like:

Honeycomb is unique in its ability to calculate metrics and statistics on the fly, while retaining the full-resolution log lines (and the original query that started it all!).

Note: This document is for folks running PostgreSQL directly. If you’re running PostgreSQL on RDS, check out our RDS connector page to set up your RDS instance instead.

The agent you’ll use to translate logs to events and send them to Honeycomb is called honeytail.

Configure PostgreSQL query logging

Before running honeytail, you’ll want to turn slow query logging on for all queries if possible. To turn on slow query logging, edit your postgresql.conf and set

log_min_duration_statement = 0

Alternatively, you can set this from the psql shell by running

ALTER SYSTEM SET log_min_duration_statement=0;
SELECT pg_reload_conf();

Finally, take note of the value of the log_line_prefix config line. It’ll look something like this:

log_line_prefix = '%t [%p-%l] %q%u@%d '

Install and run Honeytail

On your PostgreSQL host, download and install the latest honeytail by running: 

wget -q https://honeycomb.io/download/honeytail/linux/honeytail_1.762_amd64.deb && \
      echo 'd7bed8a005cbc6a34b232c54f0f84b945f0bb90905c67f85cceaedee9bbbad1e  honeytail_1.762_amd64.deb' | sha256sum -c && \
      sudo dpkg -i honeytail_1.762_amd64.deb

The packages install honeytail, its config file /etc/honeytail/honeytail.conf, and some start scripts. The binary is just honeytail, available if you need it in an unpackaged form or for ad-hoc use.

Make sure you’ve enabled query logging before running honeytail.

To consume the current slow query log from the beginning, run:

honeytail \
    --writekey=YOUR_API_KEY \
    --dataset=postgres-queries --parser=postgresql \
    --postgresql.log_line_prefix=YOUR_LOG_LINE_PREFIX \
    --file=/var/log/postgresql/postgresql-9.5-main.log \
    --tail.read_from=beginning

You are currently logged in to the team, so we have populated the write key here to the first write key for that team.

Troubleshooting

First, check out honeytail Troubleshooting for general debugging tips.

No data is being sent, and --debug doesn’t seem to show anything useful

Take a look at the --file being handed to honeytail and make sure it contains PostgreSQL query statements. An example excerpt from a PostgreSQL log file might look like:

2017-11-10 23:24:01 UTC [1998-1] LOG:  autovacuum launcher started
2017-11-10 23:24:01 UTC [2000-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-11-10 23:24:02 UTC [2003-1] postgres@postgres LOG:  duration: 4.356 ms  statement: SELECT d.datname as "Name",
               pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
               pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
               d.datcollate as "Collate",
               d.datctype as "Ctype",
               pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
        FROM pg_catalog.pg_database d
        ORDER BY 1;

Also check that the value you’re passing in the --postgresql.log_line_prefix flag matches PostgreSQL’s configured value, which you can find using SHOW log_line_prefix at a psql prompt:

# SHOW log_line_prefix;
   log_line_prefix
---------------------
 %t [%p-%l] %q%u@%d

If your log file looks like a normal PostgreSQL output log but honeytail is still failing to send events to Honeycomb, let us know! We’re available to help anytime via email or chat .

Run Honeytail continuously

To run honeytail continuously as a daemon process, first modify the config file /etc/honeytail/honeytail.conf and uncomment and set:

Then start honeytail using upstart or systemd:

$ sudo initctl start honeytail

Backfill archived logs

You may have archived logs that you’d like to import into Honeycomb. If you have a log file located at /var/log/postgresql/postgresql-main.log, you can backfill using this command:

honeytail \
    --writekey=YOUR_API_KEY \
    --dataset=PostgreSQL \
    --parser=postgresql \
    --file=/var/log/postgresql/postgresql-main.log \
    --postgresql.log_line_prefix=YOUR_CONFIGURED_LOG_LINE_PREFIX \
    --backfill

You are currently logged in to the team, so we have populated the write key here to the first write key for that team.

This command can be used at any point to backfill from archived log files. You can read more about honeytail’s backfill behavior here.

Note: honeytail does not unzip log files, so you’ll need to do this before backfilling.

Once you’ve finished backfilling your old logs, we recommend transitioning to the default streaming behavior to stream live logs to Honeycomb.

Scrub personally identifiable information

While we believe strongly in the value of being able to track down the precise query causing a problem, we understand the concerns of exporting log data which may contain sensitive user information.

With that in mind, we recommend using honeytail’s PostgreSQL parser, but adding a --scrub_field=query flag to hash the concrete query value. The normalized_query attribute will still be representative of the shape of the query, and identifying patterns including specific queries will still be possible—but the sensitive information will be completely obscured before leaving your servers.

More information about dropping or scrubbing sensitive fields can be found here.

Open source

Honeytail and our installers are all open source, Apache 2.0 licensed. Their source can be found on GitHub: