Migrating Django production database from SQLite3 to PostgreSQL using PgLoader

Today I ran into the issue of having to migrate an sqlite3 database to postgres for a Django app that was in production. The data needed to be kept untouched and seamlessy transitioned as it was real production data. After some research on the subject there were at least two options I found that worked.
Firstly always make sure you have safely backed up your sqlite database, next correctly setup your new database in postgres. (

$ createdb <db_name>

)

The first method is to use django to dump the database as json objects using:

$ ./manage.py dumpdata > db-data.json

Apply your database config changes to your app’s settings.py file and then import the database from the json file with:

$ ./manage.py loaddata db-data.json

But this operation was taking a long time and used a high amount of memory to export and import all my production data.

Enter PgLoader.

To import from your sqlite database simply run the following command (no sqlite data exporting required!):

$ pgloader --type sqlite db.sqlite3 postgresql://:@localhost/

I had initially run into some errors trying to use PgLoader such as:
An unhandled error condition has been signaled:

Failed to connect to pgsql at :UNIX (port 5432) as user “”: Database error 28000: role “” does not exist

I just entered the database credentials (db_username and db_password) to the command above.

An unhandled error condition has been signalled: :UTF-8 stream decoding error on #: the octet sequence #(204 199) cannot be decoded.

This was resolved after providing the –type flag to specifically tell PgLoader that the database was of sqlite type.

                    table name       read   imported     errors            time
------------------------------  ---------  ---------  ---------  --------------
fetch                                   0          0          0          0.000s
fetch meta data                        37         37          0          0.047s
create, truncate                        0          0          0          1.406s
------------------------------  ---------  ---------  ---------  --------------
django_migrations                      14         14          0          0.103s
app_userclass                           0          0          0          0.009s
app_userseries                      40132      40132          0          3.200s
app_usercollection                  50248      50248          0         27.978s
app_user                             2893       2893          0          1.251s
app_user_roles                          0          0          0          0.008s
app_externallink                        0          0          0          0.009s
app_tag                                 0          0          0          0.013s
app_file                                0          0          0          0.009s
app_screenshot                     392909     392909          0       1m51.695s
app_thanks                              0          0          0          0.015s
app_collectiontag                       0          0          0          0.028s
app_articles                        71307      71307          0         52.428s
auth_group                              0          0          0          0.015s
auth_group_permissions                  0          0          0          0.010s
auth_user_groups                        0          0          0          0.016s
auth_user_user_permissions              0          0          0          0.009s
django_admin_log                        0          0          0          0.037s
django_content_type                    17         17          0          0.094s
auth_permission                        51         51          0          0.061s
auth_user                               0          0          0          0.008s
django_session                          0          0          0          0.061s
index build completion                  0          0          0          0.064s
------------------------------  ---------  ---------  ---------  --------------
Create Indexes                         29         29          0          6.186s
Reset Sequences                         0          0          0          1.435s
------------------------------  ---------  ---------  ---------  --------------
Total streaming time               557571     557571          0       3m20.009s

As seen from the results table, all data and indexes were successfully transferred into the PostgreSQL database. I quickly ran some tests to confirm everything was running fine.

All in all a relatively quick and painless transition from sqlite to postgres thanks to PgLoader.

Some resources:

Leave a Reply

Your email address will not be published. Required fields are marked *