Skip navigation

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:

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

That’s it!


One Comment

    • Ferran Jorba
    • Posted July 25, 2008 at 7:36 am
    • Permalink

    Hi, regarding your sentence «if anything, people should be moving from SQLite to PostgreSQL», my disagree. I’m one of those users with DrProject on SQLite, for several reasons:

    1. It performs very nicely for our (small) team.
    2. It is so easy to backup/duplicate the installation.
    3. On the same server we have a larger MySQL instance, that already consumes many resources.

    I feel too many people just go blindly for those very large SQL servers where most of them should be better served with SQLite.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: