How to migrate from SQLite3 to PostgreSQL

Question

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?

I'd recommend taking a look at Sequel. One of the nice features of its command-line executable is it can copy databases from one DBM to another. 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
– the Tin Man, Commented Jan 8, 2016 at 21:23

Mikhail Chuprynski · Accepted Answer · 2016-01-11 16:08:14Z

8

Thanks to @theTinMan

gem install sequel

sequel -C sqlite://db/development.sqlite3 postgres://user:password@localhost/dbname

answered Jan 11, 2016 at 16:08

Mikhail Chuprynski

2,5113 gold badges31 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shams Wali Sowmo Over a year ago

Was stuck on a problem in Strapi SQLite migrating to Supabase PostgreSQL for 7 HOURS straight. This two lines of code just solved everything in a few minutes. Damn.

Collectives™ on Stack Overflow

How to migrate from SQLite3 to PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related