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.
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.