current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
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
1

I want to copy my heroku production db (postgres) to my development (sqlite).

Copying a postgres db into another postgres db is easy using heroku pg:pull. Does anyone know how to use this command to copy postgres into sqlite?

Heroku docs on pg:pull do not say how to use different types of dbs. This old article implied that it used to be possible. Setting up a local postgres db is something I'd like to avoid.

share|improve this question
1  
"Setting up a local postgres db is something I'd like to avoid". Why? It's trivial, and doing dev in SQLite then production ops in PostgreSQL is practically guaranteed to bite you with a nasty error at some point - you need to at minimum test with Pg. –  Craig Ringer Apr 17 '14 at 0:13

1 Answer 1

up vote 1 down vote accepted

You will need do a pg_restore locally then dump the data using the -a option to dump data only.

It should look something like this:

  1. Download a data dump.

    heroku addons:add pgbackups
heroku pgbackups:capture
curl -o latest.dump `heroku pgbackups:url`

  2. Create a temporary database.

    sudo -u postgres createdb tempdb

  3. Restore the dump to your temporary database.

    sudo -u postgres pg_restore --verbose --clean --no-acl --no-owner -h localhost -d tempdb latest.dump

  4. Dump the data in the correct format.

    sudo -u postgres pg_dump --inserts -a -b tempdb > data.sql

  5. Read dump in sqlite3.

    sqlite3
> .read data.sql

This is an approximate solution. You will most likely need to make some small adjustments.

I agree with Craig Ringer that it might be worth getting postgres running locally. Hopefully this process will do the trick though!

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.