Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the tables in SQLite as I please without having to manually make any changes to the PostgreSQL database. I will run the migration from Python, but it could be any external tool, which can be controlled from command line. The tool needs to be available for Linux, and it would be nice if it also ran on Windows.

share|improve this question
1  
Just to clarify, are you needing to migrate both data and schema? –  h0tw1r3 May 4 '11 at 17:08
    
Thanks for asking. The answer is yes. –  David May 4 '11 at 17:59
1  
Checked over in the PG wiki? –  Denis Jun 8 '11 at 9:47
    
Could you elaborate on why you need to do this? Perhaps there are better ways to approach your root problem than coping and converting the database. For example, CleanFill suggested developing an ETL. –  Nick Chammas Aug 17 '11 at 23:05
    
@Nick I am sorry, but it is a very special case, which would take too much explaining/discussion to describe. –  David Aug 17 '11 at 23:06

4 Answers 4

up vote 2 down vote accepted
+50

I am using Navicat to migrate between databases (MSSQL/MySQL primarily). It does run on Linux and Windows, but is primarily a GUI tool. If you create a profile, it can be started from the command-line. You can download a 30-day trial.

share|improve this answer
    
Thank you for the tip. Can it run the whole migration process using a single command line input? –  David Aug 18 '11 at 20:52
1  
I suggest you ask this question to Navicat support. They are very helpful (IMO). According to the website it should work, but I never had a need to test/use this. –  pritaeas Aug 19 '11 at 7:53

Have you looked into using ETL tools? They are external and I believe pentaho runs on both windows and linux and its free. pentaho's site

share|improve this answer
    
Hi, and welcome to the site! I know about Pentaho, but I did not know that it could solve this. Can it solve it do think? –  David Jun 6 '11 at 20:20
1  
I was thinking about ETL too whilst reading the question but I think the overhead and learning curve is rather high. Furthermore, I don't think it will be easy to do the migration "continously". –  DrColossos Jun 7 '11 at 6:18
    
Thanks! I'm personally more experienced with SSIS, but that comes with SQL Server and you have to pay for the SQL license. Pentaho is an ETL tool and is capable of migrating data from source to destination. You'll need the correct drivers to communicate with the source / destination. There is a bit of a learning curve with any of the ETL tools, but it's another tool in your toolbox, eh? There will be several steps 1 for migrating data, another for migrating DDL changes. I would start with 1 - just move the data(easier step), once you get that down, you will be more comfortable to proceed. –  CleanFill Jun 7 '11 at 13:13
    
From the sound of this, I am not sure that using Pentaho would be faster than writing this from scratch in Java or Python, given the learning curve and complexity of using a new ETL tool. –  David Jun 7 '11 at 18:22
    
@David - learning a new ETL tool is likely to be considerably easier than re-inventing the wheel IMO –  Jack Douglas Aug 18 '11 at 0:39

I think it's possible, but I don't think many people would be happy with it. In the general case, PosgreSQL and SQLite treat table definitions very differently.

CREATE TABLE test (trans_date date primary key);

If I dump the contents of that table from SQLite, here's what I get.

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (trans_date date primary key);
INSERT INTO "test" VALUES('2011-01-01');
INSERT INTO "test" VALUES('Oh, bugger.');
COMMIT;

I think every column would have to be VARCHAR() on the PostgreSQL side. It might not be too hard to write a script to change all the SQLite data types to VARCHAR(). But it feels an awful lot like kicking dead whales down the beach.

share|improve this answer
    
Converting all columns to VARCHAR would not be good enough. There should be a tool for this or some proper way to do it. –  David May 20 '11 at 9:43
    
In the example table above, you could create a table with a date column in PostgreSQL, but you couldn't import both rows into it. So, in the general case, I don't think anything but VARCHAR(n) could possibly work. –  Mike Sherrill 'Cat Recall' May 20 '11 at 11:14
    
Why do you get the "Oh, bugger." value from SQLite? –  David May 20 '11 at 11:21
2  
@David: Because SQLite lets you store "Oh, bugger" in a column of type "date". SQLite lets you store just about anything in just about any column. That's one of the main problems in migrating SQLite to any dbms that approximates the SQL standards. –  Mike Sherrill 'Cat Recall' Jun 8 '11 at 13:35

You can use

ESF Data Migration Toolkit.

I have tried it, it works good, and gives you options for conversion from multiple types of databases to multiple types of databases: such as sqlite, mysql, mssql, oracle, postgresql and just many many!

You just also just have a look at its trial/evaluation version, results are really satisfactory!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.