Helpful PostgreSQL Logging and Defaults

I use PostgreSQL every single day.  I develop against multiple versions of the database (4 versions running in parallel as I write), I have apps that use it and, my daily DBA-ing.  The biggest question I get from newbies and veterans a like is: “What are your PostgreSQL defaults?”

If you follow postgres, you already know that the default configuration (postgresql.conf) is very conservative from a resource (CPU, IOPS, Disk Space) perspective.  Over the last 10 years, we [the community] have developed some straightforward and easy to understand formulas that will help you tune… shared_buffers for example.  The item that always gets left out though is logging.  As a developer, I’m always looking for ways to see “How the database is answering the questions I ask”.  When I get a new postgres instance set up, I have a personal (somewhere in the cobwebs) checklist that I run.  Some are based on the purpose of the deployment (shared_buffers, work_mem, etc…), some are things that I always set.  Aside from memory, the biggest set of “standard” items I set are all related to logging.  I’m big on monitoring (my pg_stat_activity patch was accepted back for 9.2) and having the right detail presented to me is important.

TL;DR

 logging_collector = on
 log_filename = ‘postgresql-%a.log’
log_truncate_on_rotation=on
 log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ‘
 log_checkpoints = on
 log_autovacuum_min_duration = 0
 log_temp_files = 0
 log_lock_waits = on
the goal here is to have postgres ‘bubble-up’ details about what’s going on to us so

Read more

Simple CSV dumps from your Postgres database

Recently, I’ve spent a lot of time doing data shuffling.  Some of it for me and my own ( mostly ) malevolent purposes, but mostly, the kind of stuff that data analysts want to slice and dice.  I typically prefer to push analysts to the web based reporting setup I’ve built them, but a lot of times they are looking for some new angle that requires an aggregate we’re not already computing or a slightly different take on a report that already exists.  I usually end up building a web-based BIRT report that lets them self-service the CSV output for the data’s inevitable import into M$ office products.  There are times however, when it’s just simpler to build a quick CSV and email it over.  It makes them feel happy and it makes me feel happy.  The question is, what’s the best way to do this straight out of Postgres?

Read more

Announcing pgHA version 9.2-1.00.20

pgHA provides automated failure detection and switchover of a PostgreSQL streaming replica pair.  Version  9.2-1.00.20 has been updated to include a more intuitive control script as well as native nagios-compliant output for integration into your monitoring system.

Download pgHA.