Search form

credativ

Earlier last year I was asked by the guys at xTuple to join them on their inaugural xTuple user conference, named xTupleCon in Norfolk, VA, to do akeynote presentation about Open Source in enterprise IT in general and PostgreSQL in particular.

I've been asked on at least three separate occasions lately how to know if changing a particular postgresql.conf item requires a restart, or a reload, of PostgreSQL. Here is my quick and dirty favorite way to answer this question:

Frequently when dealing with parametric data, you need to "roll up" the data in summary fashion as it ages in order to reduce the volume kept on hand, or maybe because the summary statistics are what really interests you. There are several ways to do that, and this post highlights four different approaches. I was reminded of this kind of "roll ups" today by a question on the pgsql-novice list.

Someone posted a dilemma to the pgsql-sql list today that involved many if not all of his sequences getting out of sync with their respective "serial" columns. In other words, something like "SELECT max(id) FROM sometable" yields 42, but the sequence nextval for sometable.id is currently set to 36. This is obviously bad (for reasons left as an exercise for the reader).

I was given a Postgres database dump to analyze today created by "pg_dump -Fc". The source database included PostGIS 1.3.x extensions. I'm not sure if this is standard with PostGIS, but the related database objects were all dumped with a hard-coded library path, specifically /usr/lib/postgresql/8.3/lib. On my machine, I have many PostgreSQL clusters (essentially at least one for every supported branch dating back to 7.3.x), but they are not located under /usr/lib/postgresql. As such, I needed a quick fix. To wit: