SQLite is self-contained relational DBMS that is quite efficient for some tasks like managing data embedded into application. This database engine is focused on economy, independency and simple design that is ideal choice for local data storages on individual devices like tablets and smartphones. However, if database system is expected to scale, SQLite will not be able to handle it due to lack of advanced database management features like client/server model support, transactions, etc.
A growth company may need more effective and 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 eliminate the restrictions mentioned above. Here is the list of most essential benefits of PostgreSQL:
- advanced object-relational database management system that supports all possible database features like subselects, transactions, user-defined types and much more
- same BOOLEAN values as in SQLite: ‘t’ is TRUE and ‘f’ is FALSE unlike other popular DBMS)
- PostgreSQL is probably the most scalable and reliable among open-source database systems
SQLite to PostgreSQL database migration is easier than the same procedure for other DBMS because SQLite has no stored procedures or another complicated objects that must be handled. Despite of this fact, data transfer may become a complicated and tedious task due to the following challenges:
- BLOB must be converted into BYTEA properly
- different escaping symbols inside INSERT INTO statements
- auto-increment columns must be converted into PostgreSQL serial type properly
- datetime 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 use special tools to automate SQLite to PostgreSQL migration.
Possible approaches to the migration
(1) The most straight forward method is to migrate SQLite data via comma separate values (CSV) files. Following this way, the first step is to export the source database into intermediate CSV files. The resulting data must be transformed according to PostgreSQL format and then imported into the destination database. SQLite database is exported into CSV format through the following statements:
sqlite> .headers on
sqlite> .mode csv
sqlite> .output people.csv
sqlite> SELECT * FROM people;
sqlite> .quit
After competing these commands all data of table ‘people’ will be exported into comma separate values file ‘people.csv’.
The next phase 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 may require additional steps if SQLite database includes some national (non-ANSI) symbols and Unicode charset is not specified. In such cases the person responsible for database migration has to convert charset using special script or program.
(2) In order to avoid manual post-processing steps specified above it is reasonable to use special commercial software solutions that can turn-key migration from SQLite to PostgreSQL with just a few clicks. One of such tools is SQLite to PostgreSQL converter developed by Intelligent Converters, software company working in database migration field since 2001. The product provides all capabilities that may be required for smooth high quality conversion:
- all versions of PostgreSQL running on Linux and Windows platforms are supported
- modification of the target table structure is possible
- indexes and foreign keys are converted
- conversion settings may be stored into profile for next run
- SQLite data can be merged into an existing PostgreSQL database
- PostgreSQL database can be synchronized with SQLite data
- full support for Unicode
- SQLite database can be exported into PostgreSQL script
- command line is supported for automation purposes
