Is SQLite a good solution to backup postgres on Heroku ?

Question

Hi all !

I'm using the Sequel (Taps) ruby gem for a remote backup of my production database (PostgreSQL).

I wonder if storing that backup with SQLite is a good solution.

What's your feeling ?

Thx !

---

Edit:

Thanks! In fact, my app is hoted on Heroku and I though it was simply impossible to run pg_dump.

But -- I found that nice rake task : http://github.com/jpearl/heroku_backups

Answer 1

I don't have much experience with Postgres and SQLite, but I do not feel comfortable with the type conversion that has to occur between the databases (you have to rely on both the PostgreSQL and the SQLite drivers for Ruby), nor with the synchronization problems that could arise if your production database is thoroughly used.

Have you already tried to restore the original database from the SQLite copy? What happens if the Sequel ruby gem is not maintained anymore?

The PostgreSQL manual has a section dedicated to backup - The pg_dump command may be a good fit for your problem, and would have the advantage of maximal reliability.