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:

$ LOGTOPG_TEST_HOST=localhost \
$ LOGTOPG_TEST_PORT=5432 \
$ LOGTOPG_TEST_USER=logtopg \
$ LOGTOPG_TEST_PASSWORD=l0gt0pg \
$ LOGTOPG_TEST_DATABASE=logtopg_tests \
$ python -m unittest discover -s logtopg/tests -v
test_1 (test_logtopg.Test1.test_1)
Verify we only read sql files once each. ... ok
...
----------------------------------------------------------------------
Ran 7 tests in 0.735s

OK

The tests connect to a real postgresql database. Each test logs into the database specified by the LOGTOPG_TEST_* environment variables above. The database needs to have the ltree extension installed, and the host needs the psql command line client, because logtopg uses psql to run its multi-statement SQL scripts.

A convenient way to get a test database is with the provided Dockerfile:

$ docker build -t logtopg-postgres .
$ docker run --name logtopg-postgres -p 5433:5432 -d logtopg-postgres

Then point the tests at it:

$ LOGTOPG_TEST_HOST=localhost \
$ LOGTOPG_TEST_PORT=5433 \
$ LOGTOPG_TEST_USER=logtopg \
$ LOGTOPG_TEST_PASSWORD=l0gt0pg \
$ LOGTOPG_TEST_DATABASE=logtopg_tests \
$ python -m unittest discover -s logtopg/tests -v

Hopefully it works!

Stuff to do / Next Steps / Requests for help

• 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;
  

---

Update to the most recent version of psycopg (psycopg 3.3.4 right now).

---

Explore PROs and CONs of using an autocommitting connection, given we are talking about logs.

---

Turn up postgresql-side query logging and see if I'm doing anything silly, like checking stuff every single time that I could do once, when making a connection, or something like that.

Deploying new versions

You need a $HOME/.pypirc file that has a token in it. To get that, go to here, log in, and make one:

https://pypi.org/manage/account/token/

After that, this is how it is supposed to work:

$ vim setup.py # set a new version.
$ python setup.py sdist
$ twine upload --repository logtopg dist/logtopg-1.0.3.tar.gz

Change the last line to whatever the new version is.