How to Clone or Duplicate a PostgreSQL Database

Sometimes you may find yourself needing to duplicate a postgres database – complete with schema, data; exactly. Sometimes I need to do this because I want to try out some ideas on an existing database but without the hassle of having to backup and restore or write rollbacks for the changes I want to make.

Luckily, it’s super easy to do this.  First ensure that there are not active connections to the source database; and then open the SQL Terminal of your choice and execute:


This will create a new database, by using the source database as a template.

If you get the message: “ERROR: Database being accessed by other users.” don’t worry; it just means that there are still open database connections, and these will need to be closed before it will work.

Tunnelling Your Way Through the Government’s Metadata Retention Laws

If you happen to be fortunate enough to have access to a Unix based web server (such as an EC2 or Linode) machine) you will surprised how easy it is to safely browse the web – circumventing the Australian government’s 2 year mandatory metadata retention laws.

t_29_0You can use an SSH tunnel to use your off-site server as SOCKS Host. A SOCKS Host (or Server) is a general purpose proxy server that establishes a TCP connection to another server on behalf of a client, then routes all the traffic back and forth between the client and the server. It works for any kind of network protocol on any port. Because the connection is secure, only the client and the host can access the the data.

This is how is circumvents Government spying. The only connection your ISP can see is the initial tunnel made to the server. All the delivery of websites etc through that connection are invisible to them (and the government).

It’s tragically easy to setup, simple initiate a SSH connection with dynamic application-level port forwarding, like this:

ssh -D 12345

And then, tell your browser that you want to use a HTTP SOCKS 5 Host for proxying (Options > Advanced > Connection Settings for Firefox):

Screen Shot 2015-11-03 at 1.52.46 PMUse manual proxy configuration; set it to SOCKS v5 on the same port you specified as forwarding.

Be warned however, while your Internet traffic will be encrypted and invisible, your DNS lookups will still be public. Firefox has a setting called ‘network.proxy.socks_remote_dns’ which you can enable by browsing to the address ‘about:config’ and searching for the string above.

Lastly, be warned that browsing this way will slightly decrease speed of your browsing – but this may be a small price to pay, and may not even be noticeable.

While this is all trivial for Linux and OSX users; Windows users will need to jump through a few more hoops. This blog post inspired and references an excellent Linode Guide which covers things in more detail, and includes instructions for Windows users using Putty.

MySQL’s UTF8 isn’t *really* UTF8 (and how to properly support Unicode)

I was forced to pull out my hair recently, after several SQL queries with UTF8 uni-coded text being fired from my project of the day, were failing to insert into my UTF8 MySQL database. Working on a project with a lot of middle eastern and asian content was simply crashing out my SQL insert queries and I was loosing a tremendous quantity of data.

Turns out MySQL’s UTF8 character set only partially implements proper UTF-8 encoding. It can only store UTF-8-encoded symbols that consist of one to three bytes; encoded symbols that take up four bytes aren’t supported. Luckily, MySQL 5.5.3 introduced a new encoding called utf8mb4 which maps to proper UTF-8 and thus fully supports Unicode, including astral symbols.

Switching from MySQL’s utf8 to utf8mb4

First, we need to change the character set and collation properties of the database, tables, and columns, to use utf8mb4, instead of utf8.

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note: Don’t simply copy-paste this! You will need to tailor the queries to meet your specific requirements, the above lines are for illustrative purposes only.

Repair and optimize all tables:

Do this with some simple SQL Queries:

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

Or using the MySQL command-line:

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Note: Don’t forget to change the character encoding for your PHP/Ruby code to use the correct character encoding – i.e. utf8mb4 instead of utf8.

Test the changes:

You can test that the changes have taken effect by running this command to see if the collation/charsets have been switched to utf8mb4:

SHOW VARIABLES WHERE Variable_name LIKE ’character\_set\_%’ OR Variable_name LIKE ’collation%’;

Alternatively, change the default character set in MySQL.

In the /etc/my.cnf config file, we set the following instructions:

default-character-set = utf8mb4

default-character-set = utf8mb4

collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

That’s it. Obviously you will need to restart my SQL first though, and it will only apply to databases made after the default change, but I highly recommend this change. Even if you think you don’t need the full range of UTF8 characters, it only takes one to mess things up!


Never use utf8 in MySQL — always use utf8mb4 instead. Arbitrarily limiting the set of symbols you can use is just silly and is bound to cause a problem if your user is a young Japanese girl putting lots of cats in your strings. Why would you risk loosing lose data every time an Arabian user writes a message in their native locale. There’s no logical reason for it, and a few minutes of pain now, might save you a lot of heartache later. Do the right thing, and use utf8mb4 instead.

How to Speed Up Importing or Recovering a Large MySQL Database

I recently had to import a 30GB MySQL database from a backup of a client’s production database. My development workstation really struggled with the hundred’s of thousands of INSERT queries, and the import either took an unacceptably long time or failed outright.

Fortunately, there is a sure-fire way to increase the import though (in my case a 100 fold increase in speed).

Simply open Terminal and type:

> mysql -uXXX -pXXX

…and replace the X’s with an appropriate username and password and then paste this at the MySQL prompt:

CREATE DATABASE my_database;
USE my_database;
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE /some/path/database_file.sql;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

A few notes: ‘set global net_buffer_length’ and ‘set global max_allowed_packet’ only apply if your source filename and path are over a network. Also simply omit the ‘CREATE DATABASE my_database;’ line if you already imported the blank schema, or the database already exists.

Monitoring the Progress of a Very Large MySQL Database Import

Previously I have mentioned an awesome little command-line tool called ‘pv’. Recently, I was trying to restore a clients of legacy database on my development machine, which was a staggering 30GB .sql file. I was having quite a few problems trying to wrestle this beast, the worst of which being that I had no idea how much longer the import would take, or if it had locked up. Luckily, PV comes to the rescue.

Normally, when importing a MySQL dump file, you can just type:

mysql -uxxx -pxxx dbname < /sqlfile.sql

…to import directly from the file. However, you can pv the file and pipe it into the mysql executable like:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

And you will get an awesome progress bar about how completed the task is. You can also use it in the reverse. You’d be surprised how useful it can be.

Hands-down, the BEST way to Install MySQL on Mac OSX Mavericks

I recently had some database woes. I needed to restore a MySQL database of a clients existing website that was dozens of gigabytes in size. I had a great deal of trouble trying to import that data, but that’s a story for another time. This was about getting MySQL installed in the first place (for a development environment).

At first, I tried using Homebrew – because its awesome and I like it. But sadly, for one reason or another the default configuration just wasn’t working for me. It was simple and blind, but too well hidden and I didn’t feel like there was enough “control” (like getting setting/getting the default root password for example). If you’re going to automate something, then automate it – but don’t ask me to run stuff to secure my install when your supposed to be automating it for me.  Bah humbug!

However, the wonderful folks at solved all my problems. It was simply perfection. Exactly the right balance of automation and control.

Just open up Terminal and paste this into it:

bash <(curl -Ls

It will tell you or prompt you for the rest. And don’t forget to get the text file containing the root password, before you mistakenly delete it.

Simplicity itself.

(comic property of

How to Fix ‘Requirements installation failed’ When Installing RVM Ruby on OSX Mavericks

Installing Ruby with RVM on Mac is a cinch, simply execute:

\curl -sSL | bash -s stable --ruby

But recently, while trying to install RVM instead of the default Mavericks Ruby, the script that normally just “works” fails with the ominous message:

: Requirements installation failed with status: 1.

It turns out to be something funky with Mavericks and the Homebrew step of the installation. Luckily, despite the failure of the script, you can simply install Hombrew manually to solve the problem. Installing Homebrew is a triviality, simply execute the following command in Terminal:

ruby -e "$(curl -fsSL"

It will first install the XCode Command Line Tools (don’t worry if you already have it, just hit “install” and let it do it’s thing) and then once the Xcode dialog disappears, hit [any] key in Terminal and it will auto-download and auto-install itself. Once its all finished (and it can take a few minutes, just be patient), simply re-execute the command to install RVM Ruby.

Showing Hidden Files in OSX Finder

Just like when using Windows, sometimes it is necessary to make special hidden system files visible to Finder.  There is no preference for it, but with a simple Terminal command, things can be made visible very easily, on a privileged user account.

Simply open the ‘Terminal’ application, and at the prompt, type:

defaults write AppleShowAllFiles TRUE

and then:

killall Finder

This will cause all Finder windows to close and then reopen with the hidden files, visible and identifiable with a ghost-like appearance. You should be able to interact with them normally now.

Screen Shot 2013-11-28 at 11.00.54 pm

Convert SQL Server Database to a SQLite Database

Recently, I wanted to resurrect an old project of mine I worked on in my spare time originally designed to work on .Net technologies. Naturally, I backed it against a SQL Server 2005 Database which turned out to be a bad idea because it made portability of that data a bit of a nightmare.

So it was with great happiness that I found this tool (mirrored here) by liron.levi who posted an article on CodeProject on how to accomplish this task. You still need a Windows machine and an install of SQL Server (Express Edition with Advanced Tools is alright) but it got the job done just fine for me.

If you want a no-fuss easy way to convert your databases into a format easier to deal with and a lot more portable to-boot.