Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have been developing locally for some time and am now pushing everything to production. Of course I was also adding data to the development server without thinking that I hadn't reconfigured it to be Postgres.

Now I have a SQLite DB who's information I need to be on a remote VPS on a Postgres DB there.

I have tried dumping to a .sql file but am getting a lot of syntax complaints from Postgres. What's the best way to do this?

share|improve this question
Could use an intermediate format like CSV? Although "just inserts" in the .sql dump should be okay if the Pg schema matches .. verify there is no DDL/DML in the .sql dump? Also, consider posting the part of the dump that generates the syntax error in Pg, along with the exact error message(s) .. – user166390 Aug 18 '12 at 18:37
DDL/DML? It's complaining about AUTOINCREMENT – Chris Aug 18 '12 at 18:39
Also some PRAGMA that I think I can safely delete – Chris Aug 18 '12 at 18:40
DDL = Data Definition Language = CREATE TABLE. (Forget what I said about DML in that context.) Just make sure Pg has the same schema -- Pg would use a sequence for the "autoincrement" column -- and then make sure the import only contains DML (e.g INSERT). – user166390 Aug 18 '12 at 18:40

1 Answer

up vote 2 down vote accepted

For pretty much any conversion between two databases the options are:

  1. Do a schema-only dump from the source database. Hand-convert it and load it into the target database. Then do a data only dump from the source DB in the most compatible form of SQL dump it offers. Try loading that into the target DB. When you hit problems, script transformations to the dump using sed/awk/perl/whatever and try again. Repeat until it loads and the results match.

  2. Like (1), hand-convert the schema. Then write a script in your preferred language that connects to both databases, SELECTs from one, and INSERTs into the other, possibly with some transformations of data types and representations.

  3. Use an ETL tool like Talend or Pentaho to connect to both databases and convert between them. ETL tools are like a "somebody else already wrote it" version of (2), but they can take some learning.

  4. Hope that you can find a pre-written conversion too. Heroku clearly has one for SQLite -> PostgreSQL; is it available without Heroku and able to function without all the other Heroku infrastructure and code?

After any of those, some post-transfer steps like using setval() to initialize sequences is typically required.

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.