0

I'm trying to get the most up-to-date production data from my Heroku app restored into my development database on localhost. I backed up my production database with 

heroku pg:backups capture --app <my-app-name>

and downloaded it using 

curl -o latest.dump `heroku pg:backups public-url`

and it sounds like I need to convert it from PostgreSQL to Sqlite3 possibly using something like the process here http://manuelvanrijn.nl/blog/2012/01/18/convert-postgresql-to-sqlite/, but I'm not 100% on that. Is there a better way to get the binary latest.dump data restored into my development.sqlite3?

Edit: it sounds like all I need to do is set up Postgres on my machine, reconfigure my RoR app settings to use Postgres, and restore the production db with pg_restore. I'm a little confused though because of my database.yml file:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

Is heroku ignoring the production settings and using Postgres anyways?

mjswartz
  • 715
  • 1
  • 6
  • 19
  • 1
    In ideal scenario you should be using `Postgres`, IMHO try using `Postgresql` for development as well and dump data with regular `postgres` command. please don't spend time on these things. you can easily skip this part and learn something more important. – Abhinay Sep 05 '16 at 16:01

1 Answers1

0

A better alternative is to make sure you have what is called dev/production parity by using the same database in testing, development and production.

Using different databases can let bugs slip into production which is embarrassing and can be costly.

Setup Postgres on your local machine and use pg_restore to mirror the production database.

Added

Heroku does not support SQLite as it is a really bad fit:

SQLite runs in memory, and backs up its data store in files on disk. While this strategy works well for development, Heroku’s Cedar stack has an ephemeral filesystem. You can write to it, and you can read from it, but the contents will be cleared periodically. If you were to use SQLite on Heroku, you would lose your entire database at least once every 24 hours. https://devcenter.heroku.com/articles/sqlite3

Quite frankly SQLite is decent for mobile apps where you need an integrated DB on the device or just for getting a "hello world" rails app up and running easy but not so good for real world web apps in the cloud.

Also Heroku will override any of the settings you use in database.yml with the ENV[:DATABASE_URL] variable. So yes you are correct in that it uses Postgres anyways.

There are several easy installers for Postgres, and its available for every package installer imaginable.

Follow How To Setup Ruby on Rails with Postgres . This is a minimal database.yml that works with Postgres.app out of the box:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: hermes_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: hermes_test

# Don't add a production section! It is set by ENV vars anyways.
max
  • 96,212
  • 14
  • 104
  • 165
  • Do you have a reference on how to do this? It looks like from the references I've found (https://www.digitalocean.com/community/tutorials/how-to-setup-ruby-on-rails-with-postgres and http://stackoverflow.com/questions/6710654/change-from-sqlite-to-postgresql-in-a-fresh-rails-project) that not a lot of changes need to be made - but I'd like to be careful. – mjswartz Sep 09 '16 at 14:37
  • Edited - your on the complete wrong path. You should be setting up Postgres on your development can instead of trying to get sqlite to work in production. – max Sep 10 '16 at 11:22
  • Max, sorry to be unclear - that is exactly what I was trying to do as soon as I saw your initial post. – mjswartz Sep 12 '16 at 12:49