Today I Learned: Migrating from sqlite to Postgres is easy with Sequel

I spent some time migrating an application from sqlite3 to Postgres today, and wanted to write down a few notes for next time. Here they are!

1. sqlite3 field types need to be adapted before they can work with Postgres

Things like datetime fields, text fields, and a few others, will need to be converted to valid Postgres types. The text type in Postgres is fantastic; use it. Datetime fields will probably need to be saved as timestamp fields in Postgres.

2. Manual migration is tedious, but not complicated

To migrate manually, you can use whichever tools you like for find/replace work. Things like sed or find/replace in your text editor are fine here. Basically, the process is

1. Create an uncompressed database dump file from sqlite3 ( sqlite3 your.db .dump > your.db.bak )
2. Edit the dump file as a text file, replacing column types as needed (see point #1 above)
3. Create your postgres database from a shell on your database system ( createdb yourdatabasename )
4. Import the dump file into Postgres ( psql -dyourdatabasename -U username -W < your.db.bak ) (will prompt for username‘s password)

3. If you’ve got a lot of tables and columns with many different datatypes, it’s easier to use a third-party migration tool

There are plenty of tools out there to help you. PGloader seems like a good tool, but I ended up using a ruby tool called sequel. If you’ve got Ruby installed, or are comfortable setting up a Ruby environment, I suggest going that route.

In this case, I was flabbergasted when I experienced how much easier it is to work with Ruby on FreeBSD (the target system) than Debian Linux (the server we were migrating the database away from). Another huge win for FreeBSD. However, these instructions should work with both Linux and FreeBSD (they’re a tiny bit vague because package names will be different between different operating systems).

Basically, you’ll need ruby and rubygems installed, along with postgres, the postgres-dev libraries for your postgres version, and the sqlite3-dev libraries on your OS. Then, just do a

gem install sequel pg sqlite3

Once you create your postgres (destination) database with createdb yourdatabasename, importing from a sqlite3 database file is as simple as running:

sequel -C sqlite:///path/to/live/db/yourdatabasename.db postgres://localhost/yourdatabasename

Cheatsheet: Creating a user in Postgres

I’ve referenced setting up a Postgres user several times; here’s a quick cheatsheet on how to do this.

# become the postgres user
su - postgres # (the user is called pgsql on FreeBSD)
# create the actual database you'll be importing your sqlite3 database into
createdb yourdatabasename
psql # enter your database shell

# run these SQL statements in your database shell
CREATE USER dbuser WITH PASSWORD 'thisisyourpassword';
GRANT ALL PRIVILEGES ON DATABASE "yourdatabasename" to dbuser;
ALTER DATABASE fotoportal owner to fportal;

# Ctrl-d to exit your database shell

# log in from your shell to make sure it worked
psql -h localhost -U dbuser -d yourdatabasename

That’s it! I hope that helps someone else — it’ll certainly help me when I need to do another import of a sqlite3 database to postgres in the future.