Fluming your PostgreSQL logs

With a default configuration of PostgreSQL, the information in the PostgreSQL logs is generally pretty small with some warnings and the occasional typo in psql. However, once you start tweaking the configuration parameters a bit and you start logging more things like query times, the logs get a lot bigger. Having PostgreSQL generate over 10GB of logs per day is not unheard of if you set log_min_duration_statement low enough on a busy server. You can analyze logs of that size with pgBadger pretty easily, but to get the real value, you want to look at the logs of the course of time. Things like how does the query load look compared to the same time last week, last month or last year. If you save the daily reports from pgBadger, you can look at them over time, but that’s a manaul process. Another option is to put your logs into Hadoop and use MapReduce to analyze the information over time.

The Hadoop project has a service that is built for exactly that purpose called Flume. It lets you setup a Flume agent that will listen for the logs and then push them into Hadoop. Setting everything up is pretty simple.

First, you’ll want to setup PostgreSQL to send the logs to syslog. In postgresql.conf, you’ll set:

log_destination = 'syslog'

Then you want to setup your syslog server to forward messages to Flume. Depending on your syslog server, you’ll setup a rule like this in rsyslog.conf

*.* @127.0.0.1:5140

Finally, you’ll need to start Flume where it will listen for syslog messages and push the messages into an HFDS sink.

# pglogs.conf: A single-node Flume configuration
# Name the components on this agent
pglogs.sources = sl
pglogs.sinks = k1
pglogs.channels = c1
# Describe/configure the source
pglogs.sources.sl.type = syslogudp
pglogs.sources.sl.port = 5140
pglogs.sources.sl.host = 0.0.0.0
# Describe the sink
pglogs.sinks.k1.type = hdfs
# Use a channel which buffers events in memory
pglogs.channels.c1.type = memory
pglogs.channels.c1.capacity = 1000
pglogs.channels.c1.transactionCapacity = 100
#Describe the HDFS Sink 
pglogs.sinks.k1.hdfs.path = /user/postgres/logs
pglogs.sinks.k1.hdfs.filePrefix = pg
pglogs.sinks.k1.hdfs.fileSuffix = .log
pglogs.sinks.k1.hdfs.fileType = DataStream
# Bind the source and sink to the channel
pglogs.sources.sl.channels = c1
pglogs.sinks.k1.channel = c1
  1. kaarelkaarel08-08-2013

    btw, for longer spanning query time analysis actually another approach of storing just postgres’ internal statistics and graphing them could be used. our inhouse tool for that is here – https://github.com/zalando/PGObserver

  2. onon06-21-2014

    Hello!

  3. viagraviagra06-22-2014

    Hello!

  4. byby06-22-2014

    Hello!

  5. cheap_cialischeap_cialis06-29-2014

    Hello!

  6. Hello!

  7. cialiscialis07-02-2014

    Hello!

  8. ukuk07-04-2014

    Hello!

  9. erectionerection07-16-2014

    Hello!

  10. withoutwithout07-16-2014

    Hello!

  11. cialis_onlinecialis_online07-18-2014

    Hello!

  12. LakeshaLakesha08-28-2014

    07S[0-9]497

  1. Fluming your PostgreSQL logs - OpenSCG | BigDat...07-28-13
  2. http://www.ubergamez.com09-18-13
  3. Springfield Missouri Private Investigator09-18-13
  4. http://www.tex4tex.eu09-19-13
  5. over the counter herpes medicine09-21-13
  6. restaurant voucher09-21-13
  7. hemoroizi externi09-21-13
  8. e cigarettes starter kit09-21-13
  9. diet-pills.us09-22-13
  10. chloe moretz hot09-22-13
  11. come fare soldi con internet yahoo09-22-13
  12. payday loans for bad credit09-23-13
  13. clasimedica.com09-23-13
  14. Search Rex Craigslist Search Engine09-23-13
  15. Diabetic Shoes Charleston09-24-13
  16. click here09-24-13
  17. Craigslist Search Engine Search Rex09-24-13
  18. Durham Electrician09-24-13
  19. hyip news online09-24-13
  20. v2 cigs starter kit09-25-13
  21. great business ideas09-25-13
  22. medical aid quotes online09-25-13
  23. Carpet Pet Urine Removal09-25-13
  24. where to sell clothes09-25-13
  25. create an app for android09-26-13
  26. lavorare da casa09-26-13
  27. movers marietta ga09-26-13
  28. giochi casino slot machine09-27-13
  29. green coffee bean extract09-27-13
  30. cardiologist reviews los angeles09-27-13
  31. book of ra online spielen09-28-13
  32. e cigarette free trial09-28-13
  33. hcg drops09-28-13
  34. garcinia cambogia supplements09-28-13
  35. promovare site09-29-13
  36. http://tabelafipecarros0.wordpress.com09-30-13
  37. casino bonus ohne einzahlung09-30-13
  38. dr oz pure saffron extract09-30-13
  39. internet marketer09-30-13
  40. victoria justice music video09-30-13
  41. police auctions phoenix az10-01-13
  42. family research zone10-02-13
  43. certified limited edition prints10-03-13
  44. cheapest car insurance10-05-13
  45. cheminform10-05-13
  46. learning to play guitar10-07-13
  47. pop over to these guys10-07-13
  48. how to play electric guitar10-07-13
  49. video advertising in facebook10-07-13
  50. blucigs.com10-08-13
  51. giochi slot gratis 3D casino10-08-13
  52. meratol10-09-13
  53. rencontres gratuite10-10-13
  54. how to last longer in bed10-12-13
  55. wellbabysite.com10-13-13
  56. e cig reviews10-14-13
  57. nieruchom10-15-13

Leave a Reply

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

/* ]]> */