Postgres Quick Tips

Quick Tip – Postgres Data Dumping

Hey guys, just a quick tip on something I used recently – how to dump data to CSV from postgres.

So, if you want to dump something on the server itself, you can run the following commands:

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

Which will dump out the data into a CSV at the specified path. If you are logging in through a remote connection on a client, then you can do the same using a similar command:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

This will then dump out locally to your client.

On both of these commands, the file will be written with the permissions of the item in question – with the first one, it will be written as the user which the server is running under (usually postgres) – in the second one it will be as whoever you ran psql as.

This was pulled from a stackoverflow answer, so go upvote them, its a very good answer and is very well written – much better than I have managed here.


Its been a while…

Hey guys, so yea its been a little bit of a while – at least since August when I posted part one of getting started with Gulp. Yes, there is a part 2, however I have been really busy with work and just not having the incentive to do more on it in my spare time.

So, what have I been up to? Well a lot of stuff at work, doing some large migration items between several database systems as well as doing a full deployment system for clients. That is as well as helping to run a fledgeling hackspace, which is doing surprisingly well considering its in its second year (infact our second AGM is next week!). Apart from that, Ive been trying to do some work on making videos on youtube, and learning how to record and edit things – and then there are the conferences!

Was at FOSDEM again this year, which was great, especially seeing everyone again. I helped with the Perl room, and did a secondary set of videos which should be uploaded at some point soon. These will be a secondary set of recordings for the talks, as last year there were a few recording issues from the official FOSDEM team – they should be more ironed out this year, so we will see when they get released! (I had to help fix the official ones last year as well..)

The next major conference for me is FlossUK, of UKUUG for those old ones in the audience, where I will be talking on Lets Encrypt, or more precisely a set of scripts I created specifically to make managing the SSL certificates I use on this server easier, and also on some Perl modules i’ve been working on for making testing with external software, which need running instances to run. The first of these is Test::Instance::Apache, and eventually I will be creating an nginx, Postgres, and MySQL set of modules with a similar interface – as well as anything else I run in to and need to test either in day to day work, or just feel like making work.

Apart from that though, theres not much else. Ive been playing games both online and on pen and paper, as well as playing around on my new laptop recently – an Asus UX305CA which is meaning I can write and work on minor things almost anywhere without worrying about running out of battery as quickly – as well as not letting me play (many) games on it, as its just a small m3 processor. I may actually do a full write up on this machine at some point, as its a rather nice little light thing.

I think thats about it for now – I will post more here in the future on various things, as they happen. I would love to try and write more, especially tutorials – if you want me to try and write a tutorial on something (especially if its outside my normal work area!) then please leave a comment or contact me at the various other places you can find listed on this site.

Beginners Guide Gulp TBS Tutorials

TBS Tutorials – Gulp – Getting Started – Part 1

Hey guys, welcome to the first in (hopefully) a series of useful tutorials, for various things that I end up using or learning for either my day job or for fun. In this one we will be going over Gulp, how to get started with it, and some of the basic functionality, with us eventually ending up with a Gulp setup which will compile from Sass and Pug (Originally known as Jade) in to CSS and HTML – all while serving it to your browser automatically. What are Sass and Jade? don’t worry at this second, we will be covering those later on in this tutorial!

Blogs Brussels Travel

Brussels and FOSDEM

It’s that time of year! Me and Mark have head off to Brussels for FOSDEM, a large developer conference held at the university. We left at around 7 from Lancaster, drove to the airport and jumped on our plane.

So after an uneventful but comfortable flight (the plane was almost half empty!) We arrived, managed to catch the right train the first time, and arrived at our hotel – the lovely Pullman Hotel in South Brussels.

After acquainting ourselves with our respective rooms, we headed deeper into Brussels, towards where the pre event social would be taking place the next day.
We found several brilliant little gift shops with interesting items (La Suite, as well as a few Manga/Comic stores), then headed back towards the hotel to find somewhere to eat. Ended up at a great Steakhouse just opposite the station, which did a large selection of gorgeous salads – If I remember rightly, mine had smoked duck and goose liver pâté on top of a large plate of salad. My only regret was not going back there to have another – maybe next year!

That evening eventually consisted of myself and Mark sat in the hotel bar, working our way through both todo lists and the beer selection – as always, work follows us, especially when we can work remotely.

The next day (Friday) started off with an awesome breakfast at the Häagen-Dazs Café just next to the hotel, where we had a plate of deliciousness


Which was then followed by more work. At lunch, we packed in work and headed into the centre of Brussels, this time in search of a microphone which would work with the camcorders Mark had brought with him, as backups for the FOSDEM video’s. After searching high and low, we eventually found an affordable wireless microphone set in Cash Converters – which had the odd precaution of putting both mine and Marks’ bags behind the checkout when we entered.

After this, we wasted a bit of time and grabbed some food, before heading to the traiditional pre-drinks for FOSDEM, at Delirium Cafe. An amazing place, which has become a major part of the FOSDEM ritual, and is definitely something to experience. After hanging out with various people from inside and outside the Perl Community, myself and Mark left early(ish) so we could be in on the Saturday to help unpack the van for the Perl stand.

Saturday rolled round, and apart form forgetting to set my alarm, and waking to Mark ringing me saying ‘are you ready to go then?’, and then us getting completely confused on which platform the Metro was meant to leave from, was a very interesting day. I managed to get to a couple of interesting talks, including one on testing black-box software by inserting a Javascript runtime into it, and two on the current state of KiCAD (an Open Source schematic and pcb designing software). As well, I managed to go round and have a talk with most of the other stands, nabbing the standard metric tonne of brochures, leaflets, and ofcourse, stickers. Unfortunately I was not able to get a sticker from the Python table, but got hold of a t-shirt anyway.

The Saturday evening Perl meal, organised by Wendy & Liz, was at the same place as last year, a lovely restaurant about 20 minutes away from the venue. The staff did amazingly, considering we arrived in dribs and drabs, and the food was gorgeous (Are you noticing a pattern here? Yes, I love my food!). As the evening wore on, the beer continued flowing, and conversations became heated debates.

So here we are at Sunday! Which was the day for all the Perl talks. This year I managed to catch every single one, mainly because I ended up helping in the room, taking backup video footage, and setting up the speakers. There were many interesting talks on both Perl 6 and Perl 5, showing both the merits and pitfalls of both an established, and a brand new language. There isn’t much else to say about this, apart from to watch the videos when they come out – both myself and Claudio have been trying to get things sorted on this, and so hopefully everything will be sorted soon!

Obviously, if there was a Saturday Perl meal, then there has to be a Sunday Perl meal, again arranged by Wendy & Liz at a French style restaurant within 5 minutes of the venue – infact it was the same place that the FOSDEM staff ended up at when they had finished packing up at the University. Again, the food was amazing, with me having Diablo Prawns as a starter, and then a large (and I mean large!) Tuna (I think…) steak for the main – brilliantly cooked. Again the night continued on to conversations, about various things, before we all bid each other farewell for another event.

Myself and Mark spent Monday having another lazy walk around Brussels, going back to the shops we had noticed things in on Thursday and Friday, as well as having a wander around with our cameras, before catching the train back to the Airport. The flight home, apart from being a bit more packed than the flight there, was uneventful, apart from marking the end of a brilliant time at FOSDEM, where I met many old friends, and made several great new ones, who I all hope to see again in the future!

So thats a short(ish) post on my recent trip – I started writing this on the Friday morning actually, but never got a chance to finish it until now, almost 2 weeks on. However, I will leave you with a couple of pictures I took on the Monday while we were killing time before coming home, as well as a shot from Thursday night.

Lighting Projects

Bedroom Lighting

So, the other day (well, yesterday infact!) I was talking to one of my friends who was explaining how they use a SAD(Seasonal Affective Disorder) light to help them wake up in the morning. As mornings are something which I have a large difficulty in dealing with, I thought ‘I wonder if this will help…’. However, buying an SAD lamp just for a ‘what if’ moment is a little bit overkill – they range from £45 up to £$$$$ – so I decided to go the DIY approach.

So the main idea of an SAD light, and of the treatment they provide, is to expose your senses to daylight more during the winter months, when the amount of daylight is less from natural sources. So what I bought was an LED strip of Daylight LED’s, and a wireless RF remote to turn them on and off – as you can see in the header image!

The rest of the project is now just installing it – which consists of just sticking the LED’s along the top of my headboard, cable tying the controller on, and then just testing it! you can see the various steps in the gallery below:

It was complete luck that where I finished lined up, but it gave me a brilliant spot to attach the RF receiver. The only major changes I made was taping the power supply to the RF receiver (the socket was a bit lose), and putting tape over the BRIGHT BLUE LED on the power supply.

So, the LED strip set I bought was this one, and here is the RF Remote set. I did not notice that the remote was RF when I bought it, however it was a much better choice than an IR remote set, as it doesnt need line of sight.

I will find out if this helps when I want to get up, however as I am currently using it as the main light in my room, it will definitely be useful as a reading light!

Let me know in the comments if you have any questions, ideas, etc. about this setup, and if you would like to see more about this project or other projects of mine – I would like to build a proper alarm clock into this, replacing the RF remote at some point.


Transmission: Pi

So regularly, I find myself needing another install disk for a particular Linux distribution – be this for a desktop, a server, or a Raspberry Pi. Which means that I have, at any one time, atleast 2 downloads for various Linux distros on any one particular machine I use… although it is rarely the one which I want. This is a big issue at times, depending on the size of the installer – A generic installer for the Raspberry Pi alone of just Raspbian is 1.03Gb. This adds up to about 10 – 15 minutes on a decent connection, but could mean an hour or two somewhere else. So it is probably worth having a Torrent Client constantly running.

Using a desktop, or server is an option for this, however for something which is not going to be doing a whole lot of heavy lifting, they seem like a bit overkill for always on – also the power cost is fairly huge for something like that too. Enter the Raspberry Pi.

On its own, however there is little use trying to use a Pi for this. Most people only use an 8Gb card for the main OS, and that isnt much for 1Gb install images. So an external hard drive is also a must.

The final thing you will want is a wired connection for your Pi, unless you are completely insane and want a wireless thing… yea lets not go there, you’re already reading this blog!

So, the parts list:

  • Raspberry Pi (B, B+, or 2 – one with an ethernet port)
  • Memory card with Raspbian installed
  • External HDD – I’m using a 1TB 2.5″ Samsung USB drive
  • Ethernet connection to your home/office network

Putting it together

So the first thing to do is wire it all together. I’m not going to insult you by showing you how to do that, however do check you get the USB the right way up on the 3rd try. You will also want to set up the Pi with ssh access, and probably your own username and password access. You will also probably want the external HDD to automatically mount on boot.

Mount a HDD on boot

To get a drive to automatically mount on Boot, you will want to add it to /etc/fstab so that it can be correctly identified. When you plug your drive in, it will be assigned a device name similar to /dev/sda1 which corresponds to the first partition on the sda device. If you have multiple devices, and also multiple partitions on a device, then this is not guaranteed between reboots of your machine – one of the other devices may respond quicker the next time round. What you should use instead is the devices UUID, or unique identifier, which you can find out using the assigned name. To find out the assigned name, you can use fdisk -l. This will need to be run as sudo or the root user, and should give an output something like this:

Disk /dev/mmcblk0: 16.0 GB, 16005464064 bytes
4 heads, 16 sectors/track, 488448 cylinders, total 31260672 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0002c262

        Device Boot      Start         End      Blocks   Id  System
/dev/mmcblk0p1            8192      122879       57344    c  W95 FAT32 (LBA)
/dev/mmcblk0p2          122880    31260671    15568896   83  Linux

Disk /dev/sda: 1000.2 GB, 1000204885504 bytes
38 heads, 27 sectors/track, 1904020 cylinders, total 1953525167 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000bc67c

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1            2048  1953525166   976761559+  83  Linux

From this you can see the that there are two devices, one with two partitions (/dev/mmcblk0) and another device with one partition (/dev/sda). The next step is to search for the device by ID, looking for the partition you want – in my case its /dev/sda1.

To find the UUID’s available, run ls -la /dev/disk/by-uuid – you will get an output similar to the following:

tbsliver@tux ~ $ ls -la /dev/disk/by-uuid/
total 0
drwxr-xr-x 2 root root 100 Jan  1  1970 .
drwxr-xr-x 6 root root 120 Jan  1  1970 ..
lrwxrwxrwx 1 root root  15 Jan  1  1970 2654-BFC0 -> ../../mmcblk0p1
lrwxrwxrwx 1 root root  15 Jan 25 15:58 548da502-ebde-45c0-9ab2-de5e2431ee0b -> ../../mmcblk0p2
lrwxrwxrwx 1 root root  10 Jan  1  1970 d4f121eb-8b3f-44b9-b886-aa020032adc9 -> ../../sda1

From this i can see that the UUID is d4f121eb-8b3f-44b9-b886-aa020032adc9 for that partition. This can then be used in /etc/fstab. The next step is to create a mounting point for it, which I will create at /media/Tardis (an ongoing theme of my HDD’s) with the command sudo mkdir /media/Tardis. The final things you need to know are the filesystem used on the device (in my case its ext4). So I will edit mine to look like the following:

proc            /proc           proc    defaults          0       0
/dev/mmcblk0p1  /boot           vfat    defaults          0       2
/dev/mmcblk0p2  /               ext4    defaults,noatime  0       1
# External Drive
UUID=d4f121eb-8b3f-44b9-b886-aa020032adc9 /media/Tardis ext4 defaults 0 0

There are many more options you can add to this, so its well worth reading the man page for fstab – run man fstab to read this.

Installing Transmission

The application we will be using for this is called Transmission. It is quite a lightweight Linux based Bittorent client, and actually has a non-gui option in the form of a daemon. So lets install it:

sudo apt-get update
sudo apt-get install transmission-daemon

Now we have the basic transmission daemon installed, stop the process so we can change a few settings. One thing to note about transmission-daemon is that if you change any settings, then try and to a restart it will overwrite your changes with new ones. You will have to do a reload to get it to fully reload the settings from the file. So the best bet is to just stop it at the moment, and start it again when we’ve finished:

sudo service transmission-daemon stop

Now we want to change a few of the settings. These are stored in /etc/transmission-daemon/settings.json. But first, we should create a directory to put the downloaded files in. For this, change to the directory you mounted your external HDD at earlier, and make a few directories, assigning them the correct user and group:

sudo mkdir /media/Tardis/torrents
sudo chown debian-transmission:debian-transmission /media/Tardis/torrents
sudo mkdir /media/Tardis/torrents/downloading /media/Tardis/torrents/finished
sudo chown debian-transmission:debian-transmission /media/Tardis/torrents/*

This has now created the folders we will point Transmission at for downloading, and they will all exist on our external HDD, saving the space on the SD card. So now, open up the settings file with your favourite editor (mine is vim) – you will have to do this as root or with sudo, for example sudo vim /etc/transmission-daemon/settings.json. From here, you can change the following lines:

"download-dir": "/media/Tardis/torrents/finished",
"incomplete-dir": "/media/Tardis/torrents/downloading",
"incomplete-dir-enabled": true,
"rpc-password": "example",
"rpc-username": "tbsliver",
"rpc-whitelist": ",192.168.0.*",

Here you can see we have set the download dir to one we created earlier (the finished directory), and also set up a seperate incomplete directory (called downloading) for torrents in progress. This saves having a bit of a mess in the main download directory – You have to enable tihs option, hence the incomplete-dir-enabled option.

The other things to note are the rpc options. Note the password is plaintext – this is only like this until you start transmission again, at which point it hashes the password in this file. I have also changed the rpc-whitelist to include my local network, otherwise it will deny your conections. The final thing is the username, which im obviously going to use as mine.

After this is changed, there are a few other settings which may be worth sorting, however these are easiest done through the web interface. So now you can start the daemon:

sudo service transmission-daemon start

And then go to the IP address of the Pi, on port 9091 (by default). For me this was, which then asked me for the username and password I set earlier. After that, it should give you a standard Transmission window in your browser!

Batten Down The Hatches

The final few steps are just minor belt-and-braces parts, best practices I have found when using always on Torrent Clients. The first is, ENABLE ENCRYPTION. Even if all your torrents are legal ones, I find its best to just enable this – go to the settings window by clicking the button in the bottom left with the spanner on, and go to the Peers tab. In the dropdown menu, select Require encryption.

The other thing I have done is to throttle the client during normal internet hours, which for my house is between 8AM and 1AM. For that, go to the Speed tab in the settings window, and set a reasonable Upload and Download speed for during the day – this all depends on your local internet connection. Then tick the Scheduled Times box, and pick a set of times and frequency which suits you. This way, when most people are using the internet, its not going to wipe everyones bandwidth out, and when its out of hours then it can just download as fast as it wants.

And There We Go!

Well, that was longer than I expected, however its still a useful tutorial on how to set up a web only Transmission client. There is obviously more which can be done for this, and if people would like to know more or have questions, please ask in the Disqus section below! Guest comments are allowed, however abuse of this feature will mean I break your fingers with a cricket bat. Oh, and also turn it off. So you’ve been warned! But comments and criticisms, hints and tips are all welcome.

Now then, its Stupid-O-Clock, and I could do with sleep. Night!


New Comments Ability

Note: This no longer applies since moving to WordPress. Here for posterity

Well, it may have been a few days (ok, weeks…) since my last update, however I have been fairly busy with work, and also with the variet of other things I do which arent updating my blog. Ok so thats really no excuse, but still, theres now a new feature here! Theres a disqus forum at the bottom of every blog page, and eventually I will be setting one up on each of the project pages, and the project update pages. This will then allow all you nice people to ask questions about things in the blog posts. I wonder how long until this gets abused…

So yes, this is only a short update, but hopefully there will be something new I post shortly! In the meantime, I’l see you in the comments.


Playing With Init

So this is less about me playing with init itself, but playing with multiple different distros which have different init systems. But why, you may ask? Well if you know me, you probably have also heard about the current systemd debacle, which is currently tearing a lot of communities apart – some want to use this new shiny, while others are wary of it, as it is (relatively) untested. I sit on the fence – I dont mind it being on my desktop and laptop systems, as bootup speed is sometimes actually important, but any of the servers I currently administer I am putting it nowhere near. This is mainly due to wanting ‘stable’ software, which generally means Debian Wheezy, which is exceedingly tried and tested.
I am also unlikely to try anything new on the currently running servers, mainly because ‘if it aint broke, dont fix it’, but also because its best practice to test any new dependencies first on a staging or testing environment.

So you dont mind systemd?

No, not really. Its another answer to a hard problem, although as with anything, there are always decisions made which people dont agree with that much. The one I am not such a fan of, is the use of binary logging, and also what I feel to be a slightly more obscure way of managing daemons. Now, dont get me wrong, haveing a central database of all the running daemons is damn useful, and makes it much easier to see what is actually running on a server – although using top or ps is also possible. And having an easier way of defining start/stop/restart scripts, in a standard format, is also useful.

However, limiting init scripts to JUST start/stop/restart/reload, is fairly idiotic. I know of several apps which define extra init points, allowing you to test your setup – for example, OpenNMS has a CheckJava option, which will make sure you have actually set up the java environment correctly, and it hasnt broken in an update. Also, being able to gracefully shut down Apache, as opposed to the kill -9 shutdown, means that when using something behind a load balancer you dont end up with customers sites just dying because the server they happen to be on currently before the load balancer kicks you over to another server is rebooting.

Thats all server stuff though

True, however as I am using it every day for work, I am getting very used to the way it works. And having to re-write init scripts to work with a new system, as well as introduce a full migration path for them, is something that I am really not looking forward to doing, especially with something which seems to have more power than an init system should. So, I have been looking at other Distros, to see what is available and how things work – first on a Laptop machine, so I can play without worrying about my main development machine or any of my servers. And here is what I’ve looked at.

Elementary OS

Yes, ok, its a desktop only distribution, but its been intriguing me for quite a while, and with a new release just out, I thought I’d take a quick peek at it. First thing to notice is, it’s definitely based on Ubuntu, with a very nice interface. However, the machine I installed it on doesnt have much grunt, and so due to the fact it seems to be a heavily re-skinned Gnome 3 interface, it ran very jerkily – with no way to turn off the animations among other things. So, very nice, but not for this machine. Onto the next…


Whats this? Debian without systemd? Well I have been keeping an eye on this for a while, and last week found out that they had released a pre-alpha installer, very much based on the current Debian installer. And it worked! Installed all the way through, got me to an XFCE4 desktop (my prefered DE!) and looked good! right until it disconnected from the Wi-Fi mid update and refused to re-connect. So, nice try! I will definitely come back and visit it when it’s had a chance to iron out some of the creases, as I really dont have the spare time to help with this project all that much. Which brings me onto the next Distro


This is one which I have played on while working on a client provided dev box, and in all honesty is very interesting. I have yet to install this, but it is next on the list, and intreagues me due to the way that the package management can either be done using a simple package manager, or using whats known as the ports tree – you navigate to the folder corresponding to the program you want to install, and thenmake install clean in there – and it takes care of the rest. One other bonus is, the documentation for this Distro is amazing, they have rock solid support, and you can customise your machines as much as you need. Also, no systemd, as it doesnt run on any of the *BSD’s.

So thats it for now, I felt like a little rant, and letting people know what I’ve been up to! Also this weekend I will be up at MakerFaire Newcastle, on the HACMan and LAMM stand, so look for me there! I will hopefully be posting some updates about various things I’ve been working on soon too.


New Pages

I’m Back!

And wow I have managed to fail massively at this blogging routine. However, I’m back in the driving seat, as it were, and have developed a few new features – namely my new Projects pages!.

This is hopefully going to evolve a bit more over the coming weeks, and I will be eventually blogging about the various helper scripts I wrote to help make it a bit easier to write posts – if you want to have a look at them, head on over to them on Github, they are the generate-blog and project-update scripts.

Anyway, this is only a quick update as its actually 4 in the morning, and I should really go to bed. Hopefully I will get round to writing more about this tomorrow/today (depending on how you look at it)…


One Week Later…

Well that was close!

I almost managed to fail one of my new year promises within the first week! Phew! Thankfully I have managed to sit down (just now) and start working on a blog post. What, did you think I’d had a beer?

Speaking of beer, it has been a week! In that time I’ve had a party, two gaming nights, and new years, and yet not a drop of alcohol has passed my lips! Even my mouthwash is alcohol free… (Not on purpose, just hadnt noticed before). So this is going well! only 3 and a bit weeks left to go before it is all over, and I can enjoy some nice Belgian beers in Brussels, after the last day of FOSDEM.

What about the other plans?

So my other targets for this year are off to a great start! I started writing some presentations today, after some various ideas from Matt and Mark at work. I have managed to get hold of, and write an entirety of one page in my page-a-day diary! So i’m only about 7 days behind…

Also, obviously this is my second blog post this year, so that was close. As for the CPAN module, I am still working through the documentation so that it can be used straight away by someone who didnt write the whole thing. I am hoping to actually finish writing the documentation in the coming week, and then start looking towards modifications I can do on it.

What modifications?

Well, I can see a use of being able to use it for bash as well as zsh, so will port across the most basic of the plugins I use, or find equivalent ones, and then get App-Antigen to work properly with that. Honestly, im fairly sure this will be quite easy, due to how I’ve written it, but MVP first (Minimum Viable Product).

The other modifications I can see being useful, is for it to work from other git repositories, as well as from tarballs – including ones which are on your local machine, for those of you who actually develop plugins for your shell!

So what else have you been doing?

Well, this week I have been working quite hard on a new product which we are releasing at work – see the recent blog post on it on Mark’s blog. This has been a long run of design and development by myself and the rest of the team, and hopefully will be useful to a lot of people! I will be following up the release with a blog post of my own on the Shadowcat Site at some point, so watch that space!

Apart from that, there hasnt been much, apart from my usual gaming nights, and hoping to go out cycling more. After getting hold of a Brompton through the Cycle Scheme I have been cycling every day to work, and have been planning on going on a long ride at some point with just my camera.