Skip to content

Migrating an SQLite-based Django App to PostgreSQL

This was originally posted on my tumblelog about a month ago and is reproduced here to get the ball rolling.

This is mainly for my notes, so I’ll apologise now if this doesn’t work for you.

Getting Started
The first thing to do is back up our data into an easy to use (mostly) database agnostic (mostly) format. Django provides the dumpdata and loaddata commands to do this. To dump data for the analyse application to a file called dumpdata-desktop.json you do the following (from your project directory):

python dumpdata analyse > dumpddata-desktop.json

To dump everything just use dumpdata with no arguments and redirect the output to a file. Make sure you have enough memory to do this as earlier versions of Django don’t stream dumpdata output. I keep a copy of htop running whilst I’m doing this. If it seems really inefficient that’s because it is. The analyse app I’m dumping uses a 45mb sqlite3 database, but dies on anything less than a few gig of memory for dumping. If you’re on a resource constrained system, you can always move the sqlite database over to a django installation with more memory. Another option is to use a large swapfile/partition, but that’s going to slow things down substantially. If you’re really stuck, you can dumpdata individual applications and models. Once that’s done, you should have a serialized file containing all your django goodness ready for import into postgresql, mysql or anything else django can work with.

Configuring PostgreSQL
Now we need to set up postgreSQL. The first thing to do is to create a django user. The easiest way of doing this is to sudo -H -s to root and issue the following:

sudo -u postgres createuser -P analys

Replace analys with the username you want to use. Note that I’m using analys, not analyse as this is a reserved keyword for postgreSQL. Now we need to create a database for the user to use. In my case I need two databases (one for two similar but slightly different applications) that will use the same user. It may be better to use separate users for separate databases if some segregation of data is required, but I don’t need it for my two apps.

sudo -u postgres psql template1
CREATE DATABASE analys_desktop OWNER analys ENCODING ‘UTF8′;

Once the users and databases are created it’s time to give django access. To do this we’ll need to change the pg_hba.conf file, normally contained within something like /etc/postgresql/8.3/main/pg_hba.conf on Debian-based systems.

Go down to the section that specifies the users and how they connect and add the following:

local   analys_desktop    analys                      md5
local   analys_server    analys                      md5

Make sure you replace the database and user settings with your own. Restart postgresql. Assuming all goes well, we’re ready to set up Django.

Putting it all together
Now all we need to do is configure Django to use the new database and load the database back in. Go into and change the following settings:

DATABASE_ENGINE = ‘postgresql_psycopg2′           # ‘postgresql_psycopg2′, ‘postgresql’, ‘mysql’, ’sqlite3′ or ‘ado_mssql’.
DATABASE_NAME = ‘analys_desktop’             # Or path to database file if using sqlite3.
DATABASE_USER = ‘analys’             # Not used with sqlite3.
DATABASE_PASSWORD = ‘password’         # Not used with sqlite3.
DATABASE_HOST = ”             # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = ”             # Set to empty string for default. Not used with sqlite3.

Changet the DATABASE_PASSWORD value to the actual database password. Next we need to run python syncdb. Don’t create a superuser if you did a full dump. Finally we use the loaddata command to restore our data.

python loaddata dumpdata-desktop.json

There, all done!


Posted in Django, Guides.

Tagged with , .

Yahtzee I want to have your babies!!!

Many blogs die the death of a thousand non-posts and this one is no exception. I could’ve made excuses, said I was too busy in the real world building a team of atomic supermen to take over the world but that’d be far too easy. Instead I’ll leave you guessing as to whether the blog’s really dead or just sleeping by telling you about the greatest game reviewer known to man (bar the Gnome of course, but there you go).

Yahtzee is a British-born journalist currently living in Australia. Every wednesday he writes a video blog for The Escapist Magazine. Here’s the most recent one on the new old new slightly new version of the old Lara Croft Tomb Raider game er… Tomb Raider. Or something.

Gawd bless you Yahtzee, and all that sail in you.


Posted in Funny, Gaming.

Cool Nmap Options Tweaker

Ok, it might not really be a substitute for man nmap but the Nmap Tweaker over at ClearNet Security looks pretty cool, especially the “how long will a scan take” options. It’s something that was quickly knocked up in Ruby on Rails, which doesn’t surprise me as even I’ve managed to knock things up in RoR – and my development skills are so poor that every time I instantiate an object, god kills a kitten.


Posted in Links, Security.

There’s no Oscar for Security Theatre, but there are other ‘perks’

So the TSA, fantastic people that they are have decided to relax the bans in the war against liquid… err… terror, or something. However, bad news for anyone with who’s had a boob job. According to new TSA guidelines: Continued…


Posted in Funny, News, Security.

Zero Day/Wing Emergency Response Team! Go go Power Rangers!

Warning: This post contains large amounts of sarcasm. If you lack a sense of humour, can’t laugh at yourself (or someone else for that matter) without venting large amount of rage, please don’t look on.

I’m not really one for getting involved in security scene garbage, but sometimes stuff happens that seems stranger than life. The Zero Day Emergency Response Team is a group consisting of some highly respected individuals in the security community, as well as some perhaps more controversial ones. ZERT as they call themselves have released an unofficial patch for the vgx.dll issue affecting Outlook and Internet Explorer. From the press release: Continued…


Posted in Microsoft, Security.