Get PostgreSQL Logs Into Honeycomb | Honeycomb

Get 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:

  • The normalized query shape
  • Time spent executing the query
  • Transaction ID
  • Client information
  • … and more!

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 running PostgreSQL directly. If running PostgreSQL on RDS, Honeycomb offers support for ingesting RDS PostgreSQL logs via CloudWatch Logs with the option to convert these unstructured logs into structured logs.

The agent used to translate logs to events and send them to Honeycomb is called honeytail.

Configure PostgreSQL Query Logging 

Before running honeytail, 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
log_statement='none'
Note
log_statement indicates which types of queries are logged, but is superseded when setting log_min_duration_statement to 0, as this effectively logs all queries. Setting log_statement to any other value will change the format of the query logs in a way that is not currently supported by the Honeycomb PostgreSQL parser.

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

ALTER SYSTEM SET log_min_duration_statement=0;
ALTER SYSTEM SET log_statement='none';
SELECT pg_reload_conf();

Finally, take note of the value of the log_line_prefix configuration line. It will 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:

Download the honeytail_1.8.2_amd64.deb package.

wget -q https://honeycomb.io/download/honeytail/v1.8.2/honeytail_1.8.2_amd64.deb

Verify the package.

echo 'c9101060b782c34045f359bfe7b7a0a95c6a54eb97ed51777b053a769afa823b  honeytail_1.8.2_amd64.deb' | sha256sum -c

Install the package.

sudo dpkg -i honeytail_1.8.2_amd64.deb

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

Download the honeytail_1.8.2_arm64.deb package.

wget -q https://honeycomb.io/download/honeytail/v1.8.2/honeytail_1.8.2_arm64.deb

Verify the package.

echo 'c5a03a2ef4b850be35fb3809173efd3d6e8b16c52b770f61691355b7d39505b0  honeytail_1.8.2_arm64.deb' | sha256sum -c

Install the package.

sudo dpkg -i honeytail_1.8.2_arm64.deb

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

Download the honeytail-1.8.2-1.x86_64.rpm package.

wget -q https://honeycomb.io/download/honeytail/v1.8.2/honeytail-1.8.2-1.x86_64.rpm

Verify the package.

echo 'bccdedc9aa3776b827899cc0003b9f23f3a9c3b366ce2c8de9d2d943a474d306  honeytail-1.8.2-1.x86_64.rpm' | sha256sum -c

Install the package.

sudo rpm -i honeytail-1.8.2-1.x86_64.rpm

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

Download the 1.8.2 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.8.2/honeytail-linux-amd64

Verify the binary.

echo 'd774112265ee8e98c6221232461cf36c35faf844005cc98b43b55bb375761766  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Download the 1.8.2 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.8.2/honeytail-linux-arm64

Verify the binary.

echo 'c5a57a729b0ccf4ca0f2287c862538812604f5fd67d102372e91215701afdbe1  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Download the 1.8.2 binary.

wget -q -O honeytail https://honeycomb.io/download/honeytail/v1.8.2/honeytail-darwin-amd64

Verify the binary.

echo '49c976e9b98fa238c265ec6afce35d41767a3f8d67defa547bff933485f40a83  honeytail' | shasum -a 256 -c

Set the permissions to allow execution.

chmod 755 ./honeytail

Clone the Honeytail repository.

git clone https://github.com/honeycombio/honeytail

Install from source.

cd honeytail; go install

Make sure you have 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

Troubleshooting 

First, check out honeytail Troubleshooting for general debugging tips.

No data is being sent, and --debug does not 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 are 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! Join our Pollinators Community Slack to ask questions and learn more.

Run Honeytail Continuously 

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

  • ParserName to postgresql
  • WriteKey to your API key, available from the account page
  • LogFiles to the path for your PostgreSQL log file.
  • Dataset to the name of the dataset you wish to create with this log file.

Then start honeytail using upstart or systemd:

sudo initctl start honeytail
sudo systemctl start honeytail

Backfill Archived Logs 

You may have archived logs that you would 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

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 will need to do this before backfilling.

Once you have 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 is open source and Apache 2.0 licensed.