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)
- the most scalable, powerful and reliable open-source database system
Database migration from SQLite to PostgreSQL may be easier compared to other DBMS as SQLite has no stored procedures or other complicated objects implementing the database logic. 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) Migrating SQLite data via intermediate CSV files is one of the most straight forward methods. Following this way, the first step is to export the source database into intermediate CSV files. The output data must be converted to comply with the target format and then imported into PostgreSQL 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 step of database migration from SQLite to PostgreSQL can be implemented 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. Intelligent Converters, software company working in database migration field since 2001, developed such a tool. The product is called SQLite to PostgreSQL converter and it provides all capabilities that may be required for smooth high quality conversion:
- all versions of PostgreSQL running on Linux and Windows platforms are supported including forks and DBaaS variations such as Heroku, Azure for PostgreSQL and Amazon RDS
- 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
