SQLite can be described as efficient self-contained SQL relational database engine. This DBMS is centered on economy, efficiency, independence and simple design which makes it ideal choice as local data storage for individual applications and devices. However, compared with other advanced database management systems, SQLite doesn’t support client/server model. Consequently it can’t be employed to execute a shared repository of enterprise data.
In light of this fact it’s not thrilled that lots of companies give consideration to migrating their databases to more effective and robust DBMS. To eliminate the restrictions mentioned above, an individual may give consideration to migration from SQLite to PostgreSQL having in mind essential benefits of PostgreSQL:
- sophisticated object-relational DBMS having virtually all possible database features like sub-selects, transactions, user-defined types and much more
- PostgreSQL is probably the most scalable and reliable database systems
- it costs nothing and perhaps better it is open-source
- the same implementation of BOOLEAN type: both SQLite and PostgreSQL treat ‘t’ as TRUE and ‘f’ as FALSE (unlike other popular DBMS)
The procedure of database migration from SQLite to PostgreSQL is much more easy than migration between another DBMS. This is because SQLite database is simply a data storage without stored procedures as well as other non-trivial database objects. Nevertheless, even that task becomes complicated and tedious routine when converting a large number of SQLite tables as a result of such challenges as:
- changing BLOB into BYTEA
- different means of escaping strings inside INSERT INTO clauses
- converting auto increment columns into serial
- converting datetime types into timestamp
When this happens, special tools are useful to automate the entire process of data migration.
Available Solutions
(1) Migrating SQLite data through CSV format. Probably the most simple technique of doing SQLite to PostgreSQL migration would be to export the source database into intermediate Comma Separate Values format after which import it to PostgreSQL. SQLite databases could be exported into CSV format below:
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT * FROM customers;
sqlite> .quit
After running these statements specified columns of table ‘customers’ will be exported into ‘data.csv’ file.
The next aspect of the migration process can be carried out via free pgloader tool offered at http://pgloader.io. To load CSV data into PostgreSQL database with pgloader you need to define some details about the operation. Below is a good example:
LOAD CSV
FROM ‘path/to/file.csv’ (x, y, a, b, c, d)
INTO postgresql:///pgloader?csv (a, b, d, c)
WITH truncate,
skip header = 1,
fields optionally enclosed by ‘”‘,
fields escaped by double-quote,
fields terminated by ‘,’
SET client_encoding to ‘latin1’,
work_mem to ’12MB’,
standard_conforming_strings to ‘on’
BEFORE LOAD DO
$$ drop table if exists csv; $$,
$$ create table csv (
a bigint,
b bigint,
c char(2),
d text
);
$$;
Besides this post-processing, additional steps are usually necessary when the original SQLite data includes some national (non-ANSI) symbols and isn’t saved in Unicode code page. When this happens the database migration specialist need to do the required conversion of charset applying special script or program.
(2) You will find commercial software solutions that can perform complete migration from SQLite to PostgreSQL with a bit of clicks of mouse button. One of those is SQLite to PostgreSQL converter by Intelligent Converters. This product offers all required functionality for efficient conversion:
- All versions of PostgreSQL are supported including Azure
- Option to modify resulting table structure
- Convert indexes and relationships between tables
- Stores conversion settings into profile
- Option to merge or synchronize existing PostgreSQL database with SQLite data
- Option to export SQLite database into PostgreSQL dump file containing SQL statement to create objects and to load the data
- Command line support
