Submitted by Joseph Conway on Sat, 11/09/2010 - 02:12
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:
-- configs requiring postgresql restart
select name, setting, context
from pg_settings where context = 'postmaster';
-- configs requiring postgresql reload
select name, setting, context
from pg_settings where context = 'sighup';
Submitted by Joseph Conway on Sat, 24/07/2010 - 19:31
When you pass large amounts of data to and from PL/R, quite a lot of time is needed for converting. A change is being tested which treats arrays of 4 byte integers and 8 byte floating point values as a special case, resulting in a dramatic performance improvement. In a recent post, I discussed PL/R performance related to seismic timeseries data stored as an array of floats that are all recorded during some seismic event at a constant sampling rate. The problem was that when dealing with, say, 14000 arrays of floats, each having on the order of 16000 elements, passing the data to and from PL/R proved slower than hoped.
Submitted by Irenie White on Fri, 23/07/2010 - 12:00
The vast majority of Debian installations are simplified with the use of Preseeding and Netboot. Friedrich Weber, a school student on a work experience placement with us at our German office has observed the process and captured it in a Howto here: Imagine the following situation: you find yourself with ten to twenty brand new Notebooks and the opportunity to install them with Debian and customise to your own taste. In any case it would be great fun to manually perform the Debian installation and configuration on each Notebook. This is where Debian Preseed comes into play. The concept is simple and self-explanatory; usually, whoever is doing the installation will be faced with a number of issues during the process (e.g.
Submitted by Irenie White on Wed, 14/07/2010 - 11:38
nVidia has made it known that the free nv Driver is no longer going to be supported. Starting with the newly publicised Fermi Graphics Cards, future cards from nVidia will no longer work with nv Drivers. Originally, nv Drivers from nVidia were developed to provide basic 2D performance. 3D support was neither wanted nor planned in the open driver, users with 3D requirements have always been referred to proprietary drivers from nVidia. In future, the nv driver will no longer be maintained and all users are referred to the proprietary drivers.
Submitted by Joseph Conway on Mon, 12/07/2010 - 03:58
When you pass large amounts of data to and from PL/R, quite a lot of time is needed for converting. It's better to directly store the data as R objects. I had been planning to continue with timeseries aggregation, but decided to take a side-road based on a recent question on the PL/R mailing list. The question was related to seismic data, which is in fact timeseries data. However, I guess the data is normally stored as an array of floats that are all recorded during some seismic event at a constant sampling rate.
Submitted by Joseph Conway on Fri, 09/07/2010 - 01:21
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. This is actually quite a large topic, so I this tip will likely just scratch the surface. The question was related to storing min, max, and avg summaries on an hourly, daily, and weekly basis. The basic idea, for example, is that you can keep raw data for maybe a week, hourly summaries for 6 months, daily summaries for 3 years, and weekly summaries forever.
Submitted by Joseph Conway on Wed, 07/07/2010 - 22:34
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). So besides trying to figure out how the database ended up in this state, he needed a script to reset all of his sequences to the correct next value. I had run into a similar need not too long ago. Namely, when setting up multi-master replication with Bucardo you need your sequences to draw different values on either master so as not to conflict.
Submitted by Joseph Conway on Wed, 07/07/2010 - 02:10
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:
Submitted by Martin Zobel-Helas on Mon, 07/06/2010 - 10:25
The text editor vim offers several tools for automation. This howto describes a way to auto-include text modules when creating new files. Often during programming or administration you need the same text modules again and again. The editor vim is very helpful here, as it can detect a file type while it is being created and insert pre-defined text modules accordingly. This behaviour can be configured in the file .vim/plugin/autoinsert.vim, for example with: