Tech

How to Migrate from SQLite to PostgreSQL

SQLite isself-contained relational DBMSthat is quite efficient for some tasks like managing data embedded into application. Thisdatabaseengine is focused oneconomy, independencyand simpledesignthat isidealchoiceforlocaldatastoragesonindividualdevices like tablets and smartphones. However, if database system is expected to scale, SQLite will not be able to handle it due to lack of advanceddatabasemanagementfeatures like client/server model support, transactions, etc.

A growth company may need more effectiveand robust DBMS someday. If SQLite was a solution for managing data at begin, it can be migrated to more advanced open-source system like PostgreSQL in order to eliminatetherestrictionsmentioned above.Here is the list ofmost essentialbenefits of PostgreSQL:

  • advanced object-relational database management system that supports all possible database features like sub selects, transactions, user-defined type sand much more
  • same BOOLEAN values as in SQLite: ‘t’ is TRUEand ‘f’ is FALSEunlikeotherpopular DBMS)
  • PostgreSQL is probably the most scalable andreliableamong open-source databasesystems

SQLite to PostgreSQL database migrationis easierthanthe same procedureforotherDBMS becauseSQLite has nostoredproceduresoranothercomplicatedobjects that must be handled. Despite of this fact, data transfermay become acomplicatedandtedioustaskdue tothe followingchallenges:

  • BLOB must be converted into BYTEA properly
  • different escapingsymbolsinsideINSERTINTOstatements
  • auto-increment columnsmust be convertedintoPostgreSQLserialtype properly
  • date time types must be mapped into timestamp correctly

Doing all these steps manually may cause errors and risk of data corruption due to human factor. That is why many database specialists usespecialtoolstoautomateSQLite to PostgreSQL migration.

Possible approaches to the migration

(1) The most straight forward method is to migrate SQLite dataviacomma separate values(CSV) files. Following this way, the first step is to exportthe sourcedatabaseintointermediateCSV files.The resulting data must be transformed according to PostgreSQL format and then imported into the destination database. SQLite databaseisexportedinto CSV formatthrough the following statements:

sqlite> .headers on

sqlite> .mode csv

sqlite> .outputpeople.csv

sqlite> SELECT * FROM people;

sqlite> .quit

Aftercompetingthesecommandsall dataoftable ‘people’ will beexportedintocomma separate valuesfile‘people.csv’.

The nextphase of SQLite to PostgreSQL migration is handled using free tool‘ pgloader’offered at http://pgloader.io. All necessary documentation on how to use the tool can be found on the official site.

This approach mayrequire additionalstepsifSQLite databaseincludessomenational (non-ANSI) symbolsand Unicode charset is not specified. In such casestheperson responsible for database migration has to convert charset usingspecialscriptorprogram.

(2) In order to avoid manual post-processing steps specified above it is reasonable to use special commercialsoftware solutionsthat canturn-key migration from SQLite to PostgreSQL with just a few clicks. One of such tools is SQLite to PostgreSQL converterdeveloped byIntelligent Converters, software company working in database migration field since 2001. The productprovidesallcapabilities that may be requiredfor smooth high qualityconversion:

  • all versionsof PostgreSQL running on Linux andWindowsplatforms aresupported
  • modification of thetargettablestructure is possible
  • indexesandforeign keys are converted
  • conversionsettingsmay be stored intoprofile for next run
  • SQLite datacan be mergedinto an existing PostgreSQL database
  • PostgreSQL databasecan be synchronizedwith SQLite data
  • full support for Unicode
  • SQLite databasecan be exportedinto PostgreSQL script
  • command lineis supported for automation purposes