Bolic Bolic - 7 months ago 54
Ruby Question

PostgresSQL 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?


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
        connection.select_all "select pg_terminate_backend( from pg_stat_activity where datname='#{configuration['database']}' AND state='idle';"
        connection.drop_database configuration['database']