Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here’s what I did.
First I built and installed redislog and redis_fdw. Then I added redislog to my server’s shared_preload_libraries, set log_min_duration_statement to 0 and restarted.
Then I created a database called logger and did this in it:
create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table redis_postgres_log( log_entry json ) server localredis options (tabletype 'list', singleton_key 'postgres', database '0'); create type log_type as ( user_name text, database_name text, process_id int, remote_host text, session_id text, session_line_num int, command_tag text, session_start_time timestamptz, virtual_transaction_id text, transaction_id text, error_severity text, sql_state_code text, detail_log text, detail text, hint text, internal_query text, internal_query_pos text, context text, query text, query_pos text, file_location text, application_name text, message text, "@timestamp" timestamptz); create view postgres_log as select (x).* from redis_postgres_log r, json_populate_record(NULL::log_type, r.log_entry) as x; alter table postgres_log rename "@timestamp" to log_timestamp;
After that I could select a random row from the view:
logger=# select * from public.postgres_log offset 10000 limit 1; -[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------- user_name | andrew database_name | pgb3 process_id | 27513 remote_host | [local] session_id | 590b44c4.6b79 session_line_num | 1936 command_tag | UPDATE session_start_time | 2017-05-04 11:12:04-04 virtual_transaction_id | 3/2498 transaction_id | 3216 error_severity | LOG sql_state_code | detail_log | detail | hint | internal_query | internal_query_pos | context | query | query_pos | file_location | application_name | pgbench message | duration: 0.235 ms statement: UPDATE pgbench_tellers SET tbalance = tbalance + -4736 WHERE tid = 8; log_timestamp | 2017-05-04 11:12:09.593-04
There’s a bunch of work to do to make this more scalable, for example by partitioning the log. It might also be that we need to enhance redislog and/or redis_fdw to make this work better. But in principle this is a pretty nice result, a painless way of getting up to the second log entries as a postgres table.