PostgreSQL dump from Ruby on Rails controller

Question

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?

Smittles2003 · Accepted Answer · 2016-08-27 15:04:46Z

1

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.

answered Aug 27, 2016 at 15:04

Smittles2003

651 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bolic Over a year ago

Actually I need a undo stack so that the user can undo every change to the database. But for now this is too much work and I want to face it later while implementing a mechanism which saves the hole database. Then displaying all available versions. The user picks one and the database state is restored. I am using Postgres and I also found these two commands. But how can I achieve to do this in a controller, so that the user have access to this.

spickermann Over a year ago

@Bolic : What should happen it another user uses the application at the same time? How do you want to ensure that one doesn't reset the changes the other user made?

Bolic Over a year ago

This is just a prototype. Later on there will be a working undo stack. But for now I am trying to just do a dump from a controller with a given name. Then the user can select one and the dump is loaded back.

spickermann Over a year ago

Wouldn't it be simpler to use a gem like paper_trail right from the start ,instead of messing around with the database what would need to be removed before going into production anyway?

Bolic · Accepted Answer · 2016-09-05 07:15:47Z

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

Collectives™ on Stack Overflow

PostgreSQL dump from Ruby on Rails controller

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related