CODEWITHSUNDEEP
sqlitepostgresqlvps
Chris
Chris

Reputation: 12181

Load sqlite database into Postgres

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?

Upvotes: 5

Views: 5876

Answers (3)

hraban
hraban

Reputation: 2091

PostgreSQL supports "foreign data wrappers", which allow you to directly access any data source through the DB, including sqlite. Even up to automatically importing the schema. You can then use create table localtbl as (select * from remotetbl) to get your data into the actual PG storage.

Upvotes: 0

hobs
hobs

Reputation: 19259

Heroku's database conversion tool is called sequel. Here are the ruby gems you need:

gem install sequel
gem install sqlite3
gem install pg

Then this worked for me for a sqlite database file named 'tweets.db' in the current working directory:

sequel -C sqlite://tweets.db postgres://pgusername:pgpassword@localhost/pgdatabasename

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324521

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 one called sequel that will work 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.

Upvotes: 4

Related Questions