The Latest

I write a lot of SQL queries at work and, quite regularly, find myself exporting them to CSV files. PostgreSQL’s command line client, psql, has a command to handle this task – \COPY – that you can use to output the results of a SELECT statement to a local CSV file. It looks like this:

\COPY (SELECT * FROM foo) TO '~/output.csv' CSV

That works well, but it’s tedious to constantly convert commented, indented SQL into a one-line COPY command. Sure, I could create a macro in my text editor to handle this. Since I’m probably not the only person annoyed by this though, I created a simple tool anyone can use to automate the conversion: SQL to CSV. Give it a try!

Now on GitHub: certbot-renew, my super-simple bash script for renewing Let’s Encrypt certs.

I’ve been putting more effort lately on disaster-planning, and a big part of that is having backups. Linode automatically backs up my entire system, but I noticed something troubling in their documentation:

This method is very reliable, but can fail to properly back up the data files for database services like MySQL. If the snapshot occurs during a transaction, the database’s files may be backed up in an unclean state.

Yikes! So while my system and files may be backed up, my databases- which mean everything to the various WordPress sites I host- could potentially be unrecoverable. That is no solution at all.

Following their advice, I decided to write a script I could use to automate this. mysqldump is the obvious tool for this job, and the output can be compressed with gzip to conserve space. The resulting bash script could then be added as a cron job to automate it.

More…

I am now my own server admin. Cool!

This weekend I took the plunge and set up my own VPS on Linode. The thing that drove me to finally ditch my GoDaddy shared hosting was Git. Git was one of the first tools I was introduced to as a developer at PhotoShelter, and it has completely changed my expectations for a testing environment- not to mention saved my ass more than once. I needed a server of my own where I was free to install the tools I wanted.

More…

Oh hey, look at that. My website now has its own SSL certificate. In fact, I’m routing all of my main site’s traffic to HTTPS.

Note that the blog isn’t protected by the SSL… I can’t afford to pony up for a wildcard cert just yet.

What preempted this? Development on my latest project, EveryMark (working title). EveryMark is a lightweight bookmarking app. It’s an easy way to store and access bookmarks across many devices without needing to login. It’s something I’m designing to meet a personal need, so it isn’t clear yet how useful this might be to someone else. In the end though I do plan on packaging it up in a way that it can be distributed to others.