This week I had a demo at DemoCamp, and had to get a snapshot of DrProject’s database. I had to borrow somebody’s laptop for the demo, and didn’t want to install PostgreSQL on it. So, I was challenged with a task of converting PostgreSQL database dump into SQLite database. After some time of surfing the web, I came to a conclusion that nobody has ever documented/blogged this (and I guess that makes sense – if anything, people should be moving from SQLite to PostgreSQL).

To get a dump from PostgreSQL is simple:

pg_dump -a -d db_name > dump.sql

This will create a text file with SQL statements for table definitions, and INSERTs of the data in the tables. This is what I had to begin with. The next step is to sanitize data. SQLite has no predefined data type for booleans, it uses integers with 0 or 1 for false and true, respectively. Therefore, we should replace ‘false’ with 0 and ‘true’ with 1 using ‘awk’ command on Linux. The next step is to connect to the SQLite database:

sqlite3 db_name.db

You will get a prompt like this:
sqlite>

Now, use the .read command to load the dump:
sqlite> .read dump.sql

That’s it!