Copy a heroku postgres db to a local sqlite db

Question

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.

"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
– Craig Ringer, Commented Apr 17, 2014 at 0:13
I develop on a laptop with a 16GB SSD, so I prefer SQLite because each database is contained in one file which is easy to move to a HDD to free up disk space when I'm done with a project. Doing that kind of thing with PostgreSQL (and setting it up in the first place, IMO) is a pain in the neck. — Zaz
– Zaz, Commented May 11, 2015 at 21:27

Community · Accepted Answer · 2017-05-23 11:59:28Z

5

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:

Download a data dump.

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

Create a temporary database.
```
sudo -u postgres createdb tempdb
```

Restore the dump to your temporary database.

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

Dump the data in the correct format.

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

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!

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Apr 17, 2014 at 3:51

Nick Rempel

3,0222 gold badges25 silver badges31 bronze badges

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

3 Comments

Zaz Over a year ago

Why dump it to a temporary database first?

Nick Rempel Over a year ago

Not sure, feel free to edit. It's been a year since I've looked at this. :)

Jordan Over a year ago

step 2 is now "sudo -u _postgres createdb tempdb"

Collectives™ on Stack Overflow

Copy a heroku postgres db to a local sqlite db

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related