How to copy your ...

I've finished building out the basic infrastructure of my website, and I'm now at the point where I'm starting to write blog posts. I'm writing all my posts in my production environment. Technopedia.com says:

"A production environment can be thought of as a real-time setting where programs are run and hardware setups are installed and relied on for organization or commercial daily operations."

This can be contrasted with my development environment, which runs locally on my computer and allows me build and test my website before I deploy it to production. Ruby on Rails, the web development framework which I've used to build this website, provides three environments for me - test, development, and production. I'm using Heroku, which is a 'cloud platform as a service' that simplifies the deployment process for me and helps me get my website on the internet. I'll try to write up a blog post just about that later, but for now all you need to know is that it also hosts my database - the place where all of these blog posts are stored.

Since I'm writing and storing these posts in my production environment, I don't have access to them in my development environment. There have been times where I have been changing the way my site looks, and it would be helpful for me to have my development environment look just like my production environment so I can see how the changes would look.

I did some research to see if this was possible, and sure enough, it is. What I needed to do was to make a backup copy of my production database, and then load that data into my development database. Heroku has some helpful commands that you can call through its command-line interface (CLI), which is a way to talk to your computer using text. Heroku also has a graphical user interface on their website where you can do the same things, but with the CLI you can write a 'script' to run multiple tasks at once.

Rails also provides a CLI, and something called custom rake tasks, which are the scripts you can write. Here is the rake task I wrote for this job:

# To run: rake db:pull_prod_db
# lib/tasks/pull_prod_db.rake

namespace :db do
  desc "Pull production database from Heroku and restore to development environment"
  task :pull_prod_db => [:dump_prod_db, :restore_dev_db, :config_dev_db]

  task :dump_prod_db do
    puts "Starting capture and download from Heroku..."
    puts "Looking for existing backup"
    dumpfile = "#{Rails.root}/latest.dump"
    File.delete(dumpfile) if File.exist?(dumpfile)
    puts "heroku pg:backups:capture"
    system "heroku pg:backups:capture"
    puts "heroku pg:backups:download"
    system "heroku pg:backups:download"
    puts "Done"
  end

  task :restore_dev_db do
    puts "Starting restore on development environment..."
    dev = Rails.application.config.database_configuration['development']
    puts "PG_RESTORE on development database"
    system "pg_restore --verbose --clean --no-privileges --no-owner -h 127.0.0.1 -U #{dev['username']} -d #{dev['database']} latest.dump"
    puts "Done"
  end

  task :config_dev_db do
    puts "Reconfiguring development environment..."
    puts "rails db:environment:set RAILS_ENV=development"
    system "rails db:environment:set RAILS_ENV=development"
    puts "Done"
  end
  
end
lang-rb

On line 1, you'll see the command that you run from the command line to start this rake task. On line 4, you see the namespace, which is a group of commands for any database tasks. Line 5 is the description of the task this script performs, and line 6 is the name of the task. This task will actually run the three sub-tasks; each one does a different thing.

The dump_prod_db subtask contains two statements: 'puts' and 'system'. The 'puts' statement writes out the text in quotes that follows it to the screen. I use these to describe the step that the computer is currently on. The 'system' statement will execute the command in quotes that follows it - these are the Heroku CLI commands to capture (copy) and download the contents of my production database.

The restore_dev_db subtask uses a combination of Ruby, Ruby on Rails, and PostgreSQL CLI commands to find the downloaded file from the previous subtask, and copy it into my development database.

The config_dev_db subtask uses a Rails CLI command to do a clean-up step. When you restore a production database to the development environment, it causes an error because Rails thinks your development database is now your production database.

The last thing you may want to do is to change where Active Storage stores your uploaded files locally. I'm using Amazon's S3 service as my file system in production. You can see this in your config/environments/production.rb file:

# Store uploaded files on amazon s3 system (see config/storage.yml for options).
config.active_storage.service = :amazon
lang-rb

The default for the development environment is to store your uploaded files locally. This is from my config/environments/development.rb file:

# Store uploaded files on the local file system (see config/storage.yml for options).
config.active_storage.service = :local
lang-rb

However, since I'm pulling the production database, all the image references are pointing to my S3 bucket. This broke all my images, so I just had to change my development.rb file from :local to :amazon to get all my images to show up.

If you have the same specific use case where you have a Rails application that is deployed to Heroku, you can just use this code copy your production database to your development database. However, if use something different for your deployment or are using a database that isn't PostgreSQL, you can modify it to achieve the same thing. Which is what I did :) Here is the blog post I found when researching how to do this that gave me the foundation for my code.

What did you think about this? Was it helpful? Were you able to use or modify the rake task for your own needs? Please let me know in the comments below, I'd love to hear from you. Happy coding y'all!

Emerson Emerson said:

works great! i didn't have a db username set for my dev database so needed to adjust this portion very easily like so - otherwise, get an error. thanks again!

task :restore_dev_db do
    puts "Starting restore on development environment..."
    dev = Rails.application.config.database_configuration['development']
    puts "PG_RESTORE on development database"
    if dev['username'].blank?
      usernameString = ""
    else 
      usernameString = "-U #{dev['username']}"
    end 
    system "pg_restore --verbose --clean --no-privileges --no-owner -h 127.0.0.1 #{usernameString} -d #{dev['database']} latest.dump"
    puts "Done"
  end

9 months ago

Thanks! Trying this now... Looks beautiful compared to more manual process I used before.

Possible suggestions?
1. Encourage running a backup of production prior to doing anything here...

# eg heroku - backup prod
# 1. capture back up 
heroku pg:backups:capture
# 2. wait for above command to complete
# 3. run download command
heroku pg:backups:download

2. your blog articles are tough to read on a wide monitor without adjusting my browser width... maybe add a fixed max width for readability? eg

/* More readability on ultra wide monitors */
#main-body-container #show-post-container {
    max-width: 1300px; 
    margin-left: auto;
    margin-right: auto;
}

ALI AHMAD said:

Can you help me out, how can I download database from aws amazon ?

About 3 years ago

Natty B said:

Thanks! I'll be copy and pasting this for my own use :)

About 4 years ago

The Big Dummy replied:

Glad you found it useful! Let me know if it works for you or if you had to edit or update anything for your use case.

Natalya B replied:

I think these nested comments are cool. Could you make a post about them?

Lol you nerd. Sure, I'll write a post about them just as soon as I'm done with my post about guest comments and my post about system tests :)

You like the new HTML5 canvas lines linking the nested comments? Also, is it ok to flex on your own website? Asking for a friend.

How to copy your production database to your development database

Reading time: 6 minutes | Published: 01/20/2020