How do I copy relevant user information (not all of it) from one production database to another for a Ruby on Rails Heroku application
I have two applications on Heroku. One is a production app, the other is a staging app. I have a new database on the staging server. I need to copy over the relevant user information, which is contained in some of the tables. I know about the pgbackups addon, but it copies the entire database. I don't want to copy the entire database, since it is too large, and most of the data is irrelevant.
I only want a small percentage of it, relating to user accounts. Is there some way to copy the relevant rows in the production database to the staging database? I can write Ruby on Rails queries to get the information, but it doesn't seem possible to save it to another database. What are the steps I need to take to copy the user data, or is there some workaround to do the same thing?
You can send a specific request from your production app to your staging app with the data you want to export, and store them in the staging database from there.
I have been using YamlDb to save the state of my database.
Install it with the following command:
script/plugin install git://github.com/adamwiggins/yaml_db.git
Use the rake task to dump the contents of Rails database to db/data.yml
Use the rake task to load the contents of db/data.yml into the database
This is the creators homepage:
I recently wrote out json with the data I wanted to transfer, and imported it on the other end.
File.open("users.json", "wb") do |f| f << User.where(condition: true).to_json end
And on the other database:
users_json = JSON.parse(File.read("users.json")) users_json.each do |json| User.create json end
The Postgres extension dblink might be a good option if you need to copy over certain portions of data between databases. You could connect into your destination database and run something like (note: you probably need to read some of the documentation to figure out the connection syntax):
CREATE EXTENSION dblink; INSERT INTO table SELECT * FROM dblink('...', 'select name from table') AS remote(name text);
I recently released a library called forceps that you can use to import active record models from different databases.
The first thing you need to do is to configure an Active Record connection for the production database. Heroku lets you obtain your Postgres database credentials with this command:
heroku pg:credentials <production database name> --app <production app name>
With these credentials you can configure a node named remote in your database.yml file.
remote: adapter: postgresql host: ... port: 5432 username: ... password: ... database: ... encoding: utf8
Now you can prepare an script to copy stuff from production into the default environment you are working on. You can test this script in development and then run it on staging.
For example, if you want to copy all the accounts, that script could be something like:
Rails.application.eager_load! Forceps.configure Forceps::Remote::Account.find_each do |remote_account| remote_account.copy_to_local end
Forceps lets you configure what objects are copied. By default it will explore and copy all the associated objects. This means it would be copying all the associated data to each account, which is not probably what you want here.
Also, instead of using #copy_to_local you can just use the remote classes Forceps::Remote::<Model> as any other active record model. You can then just copy whatever you need manually:
remote_account = Forceps::Remote::Account.find_by_email 'email@example.com' local_account = Account.create!(email: remote_account.email)