CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Bolic
Bolic

Reputation: 745

PostgreSQL dump from Ruby on Rails controller

I have implemented ruby on rails app and I want to have a very easy way to create save points and load them from the view of this app.

For now, before I implement a huge undo-stack, I want to do a SQL dump into a file by a ruby on rails controller method and also load the dumped file back into the database. How can I perform this?

Upvotes: 0

Views: 2998

Answers (2)

Bolic
Bolic

Reputation: 745

Finally I work out an answer, how to dump and restore if you are using a PG Database in Rails.

The problem with pg_restore is that the command cannot drop the database while other users (eg. rails server) are accessing it:

To dump the database:

cmd = "pg_dump -F c -v -U YOUR_ROLE -h localhost YOUR_DATABASE_NAME -f db/backups/hello.psql"

To restore:

system "rake environment db:drop"
system "rake db:create"

system "pg_restore -C -F c -v -U YOUR_ROLE -d YOUR_DATABASE_NAME db/backups/hello.psql"

Finally to get the rake environment db:drop to work you have to use this monkeypatch taken from 

# config/initializers/postgresql_database_tasks.rb
module ActiveRecord
  module Tasks
    class PostgreSQLDatabaseTasks
      def drop
        establish_master_connection
        connection.select_all "select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='#{configuration['database']}' AND state='idle';"
        connection.drop_database configuration['database']
      end
    end
  end
end

Upvotes: 1

Smittles2003
Smittles2003

Reputation: 65

First, I wonder what the problem you are actually trying to solve is - maybe something like database transactions would make sense here?

Assuming they don't, however, and you do need to get a full snapshot of the database and restore it, it is going to depend on what database you are using. I'm most familiar with Postgres and I know there exists pg_dump and pg_restore commands to do this type of thing.

https://coderwall.com/p/2e088w/rails-rake-tasks-to-dump-restore-postgresql-databases has a walkthrough of the actual commands needed, and does it in the form of a Rake task. If you are wanting to call it from the controller, however, I would pull those out into a new class that the controller can tell to dump or restore as needed.

Upvotes: 1

Related Questions