CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresql
FastSolutions
FastSolutions

Reputation: 1829

Overwrite postgresql database

Sometimes we do a COPY from our Production Database to our Quality Database. When this happens I just create a new database and link it in the Rails Application.

Question: Can you empty an entire postgresql Database including the Relationships and import the new DB?

Problem: When importing the database it does not overwrite the current data / structure / relations ...

Info:

I export the Production database like this:

## Dump without user privileges
pg_dump -x -U database1 -h localhost -O database1 > database1.sql

And normally I import the exported database like this:

## Import
psql -U database2 database2 < database1.sql

Upvotes: 1

Views: 1350

Answers (1)

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

I've used the following rake task for a number of years and it's worked well for me. The local:db:abort_if_active_connections dependency isn't strictly necessary but it's nice as otherwise the backup fails since you can't drop a database currently in use.

You'll want to tweak the local:backup:production task's system commands to be whatever you need to get a copy of the database. Then you can just run:

bin/rake local:backup:production

lib/tasks/local/backup.rake

namespace :local do

  namespace :backup do

    desc 'Backup production and restore to development'
    task :production => ['production:db']

    namespace :production do
      desc 'Backup production database and restore to development'
      task :db => ['local:db:abort_if_active_connections', 'db:drop', 'db:create'] do
        config = ActiveRecord::Base.configurations[Rails.env]
        gz_file = "#{ActiveRecord::Base.configurations['production']['database']}.gz"
        puts "Copying production database to temporary file on this machine..."
        system "scp [email protected]:/tmp/#{gz_file} /tmp/#{gz_file}"
        puts "Recreating local database..."
        system "gunzip -c /tmp/#{gz_file} | psql #{config['database']}"
        puts "Removing temporary file..."
        File.unlink "/tmp/#{gz_file}"
      end
    end

  end

end

lib/tasks/local.rake

namespace :local do

  namespace :db do
    task :abort_if_active_connections => ['db:load_config'] do

      config = ActiveRecord::Base.configurations[Rails.env]
      ActiveRecord::Base.establish_connection(config.merge('database' => 'postgres', 'schema_search_path' => 'public'))

      version = ActiveRecord::Base.connection.send(:postgresql_version)
      if version >= 90200
        pid = 'pid'
      else
        pid = 'procpid'
      end

      connections = ActiveRecord::Base.connection.select_all("SELECT * FROM pg_stat_activity WHERE pg_stat_activity.datname = '#{config['database']}' AND #{pid} <> #{$$}")

      unless connections.empty?
        puts
        puts "There are active connections to the database '#{config['database']}':"
        puts
        puts "%-7s %-20s %-16s %-20s %s" % %w[pid usename client_addr application_name backend_start]
        connections.each do |c|
          puts "%-7s %-20s %-16s %-20s %s" % [c[pid], c['usename'], c['client_addr'], c['application_name'], c['backend_start']]
        end
        puts
        exit 1
      end

      ActiveRecord::Base.clear_all_connections!
    end
  end

end

Upvotes: 2

Related Questions