代码之家 › 专栏 › 技术社区 › FastSolutions

覆盖postgresql数据库

postgresql database ruby-on-rails

FastSolutions · 技术社区 · 10 年前

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

问题: 您可以清空整个postgresql数据库(包括Relationships)并导入新数据库吗?

问题: 导入数据库时,不会覆盖当前数据/结构/关系。。。

信息:

我这样导出生产数据库:

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

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

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

1 回复 | 直到 10 年前

Philip Hallstrom 10 年前

我使用以下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