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.
locked by Paul White♦ Aug 28 '15 at 12:01
This question exists because it has historical significance, but it is not considered a good, on-topic question for this site so please do not use it as evidence that you can ask similar questions here. This question and its answers are frozen and cannot be changed. See the help center for guidance on writing a good question.
Read more about locked posts here.
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.
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
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.
You can use
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!
I have good experience with Full Convert.