覆盖postgresql数据库

Question

有时我们从生产数据库复制到质量数据库。当发生这种情况时，我只需创建一个新数据库并将其链接到Rails应用程序中。

问题：可以清空整个postgresql数据库，包括关系并导入新的DB吗？

问题：导入数据库时不会覆盖当前的数据/结构/关系.

信息：

我像这样导出生产数据库：

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

通常，我会像这样导入导出的数据库：

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

Answer 1

多年来，我一直在使用下面的rake任务，它对我来说效果很好。local:db:abort_if_active_connections依赖关系并不是绝对必要的，但由于不能删除当前正在使用的数据库，所以备份失败了，这很好。

您需要调整local:backup:production任务的系统命令，使其成为获取数据库副本所需的任何命令。然后你就可以跑：

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 user@example.com:/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