4

I have a Rails application, created initially using SQLite3. Now I have a need to move it to PostgreSQL with all the data.

I tried to do it as suggested in "How to migrate from SQLite to PostgreSQL (Rails)" using:

sqlite3 development.db .dump | psql dbname username

Which, in my case, is:

sqlite3 development.sqlite3 .dump | psql dev_db deployer

And I got this:

ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
    ^
BEGIN
ERROR:  syntax error at or near "AUTOINCREMENT"
LINE 1: CREATE TABLE "people" ("id" INTEGER PRIMARY KEY AUTOINCREMEN...

Are those just SQL implementation differences? How can I solve this?

Community
  • 1
  • 1
Mikhail Chuprynski
  • 2,404
  • 2
  • 29
  • 42
  • 2
    http://stackoverflow.com/questions/6204142/how-to-convert-sqlite3-script-into-script-that-postgresql-can-understand This will help you. – BookOfGreg Jan 08 '16 at 16:09
  • 2
    I'd recommend taking a look at [Sequel](http://sequel.jeremyevans.net/). One of the nice features of its command-line executable is [it can copy databases from one DBM to another](http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases). In addition, it's a great ORM, making it really easy to move from one database to another without changing your code. DBM specific SQL can cause problems, but Sequel helps negate the differences. I'd also suggest *NOT* writing DBM specific SQL and instead rely on your ORM to manage the DB as much as possible. – the Tin Man Jan 08 '16 at 21:23
  • @theTinMan thank you, posted the answer – Mikhail Chuprynski Jan 11 '16 at 16:09

1 Answers1

6

Thanks to @theTinMan 

gem install sequel

sequel -C sqlite://db/development.sqlite3 postgres://user:password@localhost/dbname
Mikhail Chuprynski
  • 2,404
  • 2
  • 29
  • 42