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?

Answers


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

rake db:data:dump

Use the rake task to load the contents of db/data.yml into the database

rake db:data:load

This is the creators homepage:

http://blog.heroku.com/archives/2007/11/23/yamldb_for_databaseindependent_data_dumps/


I recently wrote out json with the data I wanted to transfer, and imported it on the other end.

somethings like:

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 'some@email.com'
local_account = Account.create!(email: remote_account.email)

Need Your Help

Make the width of an input field relative to its content

css wordpress url input

I want to display the url of the post in each post so visitors can easy share the URL. This means that the width of the input field in which the url is placed should be relative to the width of the...

Which unit testing framework?

c# .net unit-testing

I wondered which unit testing framework would be a good one to get really familiar with? I know this might be a question of opinion, but I thought I'd ask anyways. I know that I will need to do it