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.