Thursday, February 23, 2012

Postgresql corruption

I've had a couple of Postgresql corruption problems I've been working through lately so I figured I'd share some method so that you don't have to go through the trial and error process I did in getting to the bottom of it. The first thing you have to do is figure out exactly where the corruption is, and what sort of corruption you have. Most of the time corruption is going to be do to an errant action by the admin, or hardware conditions, or a corrupt index in your table.

So first figure out where in your table the corruption is. You can do this using a select query and using the ctid value to figure out where it begins. You will want to set the following two options when doing this:

=>\set FETCH_COUNT 1
=>\pset pager off

Next we will want to run the query itself a sample of this would be as follows:

=>select ctid, * from table;

This will scroll and scroll until you hit the nasty portion where it will crash the instance. Once this occurs you can start to narrow down the affected area. You may wish to attempt a reindex first before you continue on, to make sure that the corruption just isn't a bad index.

=>reindex table tablename

The next step if the reindex doesn't work it is likely you'll need to 'cut out the bruise'. The first thing you can try is a query to delete the values based on the ctid with something like the following:

=> delete from table where ctid >= '(12312,0)' and ctid < '(12313,0)'

This may fail with an error message similar to:

ERROR:  could not open segment [n] of relation [x]/[y]/[z] (target block [b]): No such file or directory

If that occurs we are going to have to zero out the block ourselves. To do this we can use the dd utility.
First lets take a look at the file in question which we can do using a command similar to:

dd if=data/postgresql/base/x/y bs=8192 skip=17897 count=1 | hexdump -C | less

The values in 'if=<value>' are going to match up as base/y/z (with the y and z being from the error message above). The value in 'skip=<number>' is going to match the first value in the page ctid we discovered as corrupt earlier. You will likely see some junk data in here such as a file name written in or a time stamp or what have you. To fix this we are merely going to write an 8k block in at that page as it would be rather unlikely that we could recover rows at this point. To do this we'll use the following:

dd if=/dev/zero of=data/postgresql/base/x/y bs=8192 seek=17897 count=1 conv=notrunc

And now if you run a query looking in that page you'll find a count of zero rows. Now you should be able to do a successful pg_dump.

Ryan Koch
3X Systems

Tuesday, February 14, 2012

Own your data

Through my relatively short IT career I've seen the rise of this thing that is referred to as the 'cloud'. It's spoken about as if some monolithic being that provides you the wonderful content your consume on your PCs, or use to conduct business. The reality of the thing as significantly more complex and drastically more piecemeal and important. We've entered a new time where we can centralize our infrastructures in such a way that the user experience is seamless, and our workload is reduced dramatically. With that have come a large and varying number of technologies claiming to be the best at backing up your infrastructure and your clients. Some have started to migrate their data to the 'public cloud' in order to protect themselves from disasters and the like. This is a respectable aim as having data off site is really the best way to go, but they are also handing over their mission essential data to a third party who may or may not be reliable, much less trustworthy. Having your own private cloud doesn't have to be difficult, and it doesn't have to be expensive.

It's important to own your data. I cannot stress this enough. The documents your users store, the records that keep your business going, and the power points your executives display they are all vital to the survivability of your business. You really shouldn't trust data of that kind of importance to some distant data center that isn't truly accountable to you. If you wanted to pull your data, or perhaps virtual infrastructure you've placed there down the road, how cooperative are they going to be? Even worse, what if the public infrastructure goes down? I don't know if you recall but Amazon's cloud service got hit with 11 hours of downtime or limited usage. While the data was in itself safe access to it was cut off.

There are a few solutions out there that can allow you to accomplish a sufficient backup strategy, and without relying on the public cloud. A lot of where to go depends on what sort of infrastructure you have, and what you are looking to back up. Do you have a Hyper-V or ESX(i) set up? Do you have a lot of windows clients, and some windows servers? How big is your infrastructure?

If you are using primarily windows based computers and servers, and/or you are using Hyper-V I might humbly suggest the product I work on for a living. The 3X Backup Appliance is your private cloud right out of the box. You can do file and system level backups for windows machines, and even do VM level backups of Hyper-V.  Other than touching the product every day, why do I think it works so well for the private cloud? It's easy to use, requires little maintenance, and features some very effective deduplication.

What ever solution you decide to go with, please do take heed to the plea to 'own your data'. 43% of businesses lose important data due to not having a proper backup strategy. Don't be one of those. At the end of the day your clients will appreciate it, you will be protected, and your data will be safe and accessible. If you have any questions at all about our solution or any others feel free to comment here, email me at, use #3XSystems on Twitter, or check out our new Reddit Community.

Monday, February 13, 2012

Tasker and an announcement

Before we get into the meat of this post (Tasker) I'd like to share some 3X Systems related news. From here on you can get answers to questions and quick support via the hash tag #3xsystems on Twitter. You'll get a response from either @3XSystems or myself @Ryan_Koch. And if that isn't enough, we have also created a subreddit on Reddit you can use to discuss any issues or questions you have, which can be found here:

So I imagine if you are an Android user that likes to tinker around a bit you may be aware of an application called Tasker. If you aren't aware Tasker is an application that allows you to create automated tasks based on profiles you create. The limits to this application are limited only by your imagination and skill set. While it's been around for a while I wanted to prod at yet further awareness of it (because it's simply amazing) and share a couple of the recipes I'm using.

Recipe #1 : Arriving at work

When I arrive at work Tasker picks up via network location that I'm within a KM of the building. I've set the radius this large because net location is rather bad with accuracy and I don't want to destroy my battery by leaving GPS turned on all the time. The simple view of the profile shows the location variable which is 'Work' and a task called 'Wifi On'. The wifi task does two different things for my benefit. The first of these is that it turns on wifi (one less thing to do once at the desk) and the second thing it does is launch the application AirDroid, which is a pretty cool application that can manage your phone from a web browser (assuming you are on the same wifi network). When I leave the acceptable radius the task exists and runs the exit task called 'wifi off' which in this case turns wifi off and kills the AirDroid application.

Recipe #2: Meeting Mode

This recipe puts the phone into a 'Meeting Mode' when you press a custom shortcut key, and then brings the phone out of said mode when a separate shortcut key is pressed. The Meeting Mode automatically rejects phone calls and sends a notification SMS to the caller, and silences the ringer and notification sound. In this screen capture the coffee cup is the meeting starting, and the space ship is exiting the meeting. When you press the meeting start short cut Tasker sets a variable called %Meeting to 1. Which leads to the a profile that is activated when %Meeting matches '1'. There is then a corresponding task called 'Meeting Mode'. All this task does is set the ringer and notification mode to level 0 or muted more or less.

The profile is then paired with another that automatically sends all calls to voicemail, and sends an SMS message to the caller proclaiming that I was in a meeting. The profile uses a similar logic to know when to kick off but it differs with the entrance of the call event.This happens by setting a task to make use of the SendSMS function in tasker and using the %CNUM variable to pull the 'last called number (in).'

Recipe #3: Custom Alarm

This one is a favorite of mine as it wakes me up in a way I don't detest too much. The customer alarm uses both a time and day context on the profile in order to define when it goes off. In my case I have it set up for weekday mornings. You can set it up to only run for a specified amount of time, and since I'm some what kind to the half asleep version of me I set it to run for 3 minutes. The profile runs a task called 'morning' which does the following:

1. Sets the system volume all the way up
2. Sets the media volume all the way up
3. Uses the text to speech engine to say "Good Morning Ryan, I.T. Like a champion today"
4. Use the music application to start playing the song of my choice
5. Load the music application so I can stop the song if I so choose

I definitely suggest giving this application a look, as it only costs $6.49 and is more than worth it. Between the various amount of playing around one can do, and the convenience of automating some tasks it is just simple hard to justify passing up.