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: