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.
-
1Just to clarify, are you needing to migrate both data and schema?– h0tw1r3May 4, 2011 at 17:08
-
1Checked over in the PG wiki?– Denis de BernardyJun 8, 2011 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 ChammasAug 17, 2011 at 23:05
-
@Nick I am sorry, but it is a very special case, which would take too much explaining/discussion to describe.– DavidAug 17, 2011 at 23:06
-
@David - If you want more answers, it may indeed help to summarize your use case in another paragraph as best you can without too much explaining/discussing. As it stands you are asking us to do too much guesswork. Links to other SE sites are always helpful if they help us understand where you are coming from.– Jack DouglasAug 18, 2011 at 0:54
Comments disabled on deleted / locked posts / reviews
|
5 Answers
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.
-
Thank you for the tip. Can it run the whole migration process using a single command line input?– DavidAug 18, 2011 at 20:52
-
1I 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.– pritaeasAug 19, 2011 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
-
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?– DavidJun 6, 2011 at 20:20
-
1I 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". Jun 7, 2011 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. Jun 7, 2011 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.– DavidJun 7, 2011 at 18:22
-
@David - learning a new ETL tool is likely to be considerably easier than re-inventing the wheel IMO Aug 18, 2011 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.
answered May 6, 2011 at 1:35
Mike Sherrill 'Cat Recall'Mike Sherrill 'Cat Recall'
10.8k1 gold badge36 silver badges43 bronze badges
-
Converting all columns to VARCHAR would not be good enough. There should be a tool for this or some proper way to do it.– DavidMay 20, 2011 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. May 20, 2011 at 11:14
-
-
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. Jun 8, 2011 at 13:35
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.
