Log to PostgreSQL

Install

Grab the code with pip:

$ pip install logtopg

But you also have to install the ltree contrib module into your database:

$ sudo -u postgres psql -c "create extension ltree;"

Try it out

The code in docs/example.py shows how to set up your logging configs with this handler.

Contribute to logtopg

Get a copy of the code:

$ git clone --origin github https://github.com/216software/logtopg.git

Install it like this:

$ cd logtopg
$ pip install -e .

Create test user and test database:

$ sudo -u postgres createuser logtopg
$ sudo -u postgres createdb --owner logtopg logtopg_tests
$ sudo -u postgres psql -c "create extension ltree;" -d logtopg_tests

Then run the tests like this:

$ python setup.py --quiet test
.....
----------------------------------------------------------------------
Ran 5 tests in 0.379s

OK

Hopefully it works!

Stuff to do

• Fill out classifiers in setup.py.
• Somehow block updates to the table. Maybe a trigger is the right way. Maybe there's a much simpler trick that I'm not aware of.
• Create a few views for typical queries.

• Test performance with many connected processes and tons of logging messages. Make sure that logging doesn't compete with real application work for database resources. Is there a way to say something like

  "Hey postgresql, take your time with this stuff, and deal with other stuff first!"

  In other words, a "nice" command for queries.

• Allow people to easily write their own SQL to create the logging table and to insert records to it. The queries could be returned from properties, so people would just need to subclass the PGHandler and then redefine those properties.
• Write some documentation:
  • installation
  • typical queries
  • tweak log table columns or indexes
  • discuss performance issues
• Set up a readthedocs page for logtopg for that documentation.
• Experiment with what happens when the emit(...) function call takes a long time. For example, say somebody is logging to a PG server across the internet, will calls to log.debug(...) slow down the local app? I imagine so.

• I just found out that the ltree column type (that I use for logger names) can not handle logger names like "dazzle.insert-stuff". That dash in there is invalid syntax.

  I hope there is a way to raise an exception as soon as somebody uses an invalid logger name.

  Or, maybe I need to convert the invalid name to a valid name, by maybe substituting any of a set of characters with something else.

• Set up table partitioning so that when there are millions or logs, they are dealt with sanely.

  This is a query that shows logs by day and log level:

  select to_char(date_trunc('day', inserted), 'YYYY-MM-DD'),
  log_level, count(*)
  
  from dazzlelogs
  
  group by 1, 2
  
  order by 1, 2;