Technology Information& News

.

Tips On How To Convert Sqlite Database Into PostgreSQL Format


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 subselects, 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
  • similar syntax for BOOLEAN type: ‘t’ is TRUE and ‘f’ is FALSE in both SQLite and PostgreSQL (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 autoincrement columns into serial
  • converting datetime types into timestamp

When this happens, special tools are useful to automate the entire process of data migration.

Image result for Sqlite Database Into PostgreSQL Format

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 necesary 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 Linux/Unix and Windows PostgreSQL servers are supported
  • Option to modify resulting table structure
  • Convert indexes and relationships between tables
  • Stores conversion settings into profile
  • Option to merge SQLite data into a current PostgreSQL database
  • Option to synchronize PostgreSQL database with SQLite data
  • Support for multibyte character sets
  • Option to export SQLite database into PostgreSQL script file
  • Command line support



Leave a Reply

Your email address will not be published. Required fields are marked *