The CANDIS Blog

Ever had a situation like this:

Select from database ID where name = RICHARD;

Returns and ID of 55 for example.

Then go and do a query like this:

Select * from some_other_table where ID = 55;

Returns, “Sorry does not exist, time to die…..”

Well apparently indexes when corrupt – which is NOT SUPPOSED TO HAPPEN – can cause PostgreSQL to go all stupid and not do a table lookup for real. This happened to me. So I found this:

PlanetPostgresql

Turns out that a reindex and a full vacuum can do wonders – even though a full vacuum is not needed with autovacuum and indexes can’t get corrupted…..or so they say.

I have now added a system wide reindex maintenance plan for PostgreSQL every night. I know that MS-SQL server has an option for this with their maintenance jobs inside enterprise manager. Maybe someone should make an enterprise manager for PostgreSQL too?

During a recent test run to see if a new PostgreSQL back end server would hasten things up in a main cluster – that has now become CPU bound and NOT IO…… the wizardry of that I will blog about later.

In any case, the short of it is, that we were juggling PERC4 cards around servers (PCI-X here, PCIe there..) and also complete raid 1 and raid 10 arrays too. The cards are supposed to “detect” the correct array type from the drives if the firmware was missing. Anyway, through a comedy of errors, it worked exactly 1/3 times. The other times we had to remember the exact settings of our arrays (stripe, etc) and how it was structured. So we could clear PERC cards and then recreate the arrays – taking special care to not initalise the new arrays.

So in the end, you can move arrays and channels about. And with LVM, even designations like /sda /sdb reording is also not an issue. However you should rely on good old fashioned hand held way of doing things. Before you start write down all the salient details of your arrays first.