如果你需要断开一个特定用户的会话，这有助于我:

检查当前所有连接:

select * from pg_stat_activity; 

授予你的用户一个角色(不重要):

set role "db_admin";

杀死届:

select pg_terminate_backend(pid)
from pg_stat_activity
where usename = '*** USER NAME TO DISCONNECT ***';

只是想指出，如果一些其他后台进程正在使用数据库，哈里斯的答案可能无法工作，在我的情况下，它是延迟的工作，我做到了:

script/delayed_job stop

只有这样我才能删除/重置数据库。

案例: 查询执行失败:

DROP TABLE dbo.t_tabelname

解决方案: a.显示查询状态活动如下:

SELECT * FROM pg_stat_activity  ;

b.查找“查询”列包含的行:

'DROP TABLE dbo.t_tabelname'

c.在同一行中，获取“PID”列的值

example : 16409

d.执行以下脚本:

SELECT 
    pg_terminate_backend(25263) 
FROM 
    pg_stat_activity 
WHERE 
    -- don't kill my own connection!
    25263 <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
    ;

没有必要放弃。只需删除并重新创建公共模式。在大多数情况下，这有完全相同的效果。

namespace :db do

desc 'Clear the database'
task :clear_db => :environment do |t,args|
  ActiveRecord::Base.establish_connection
  ActiveRecord::Base.connection.tables.each do |table|
    next if table == 'schema_migrations'
    ActiveRecord::Base.connection.execute("TRUNCATE #{table}")
  end
end

desc 'Delete all tables (but not the database)'
task :drop_schema => :environment do |t,args|
  ActiveRecord::Base.establish_connection
  ActiveRecord::Base.connection.execute("DROP SCHEMA public CASCADE")
  ActiveRecord::Base.connection.execute("CREATE SCHEMA public")
  ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO postgres")
  ActiveRecord::Base.connection.execute("GRANT ALL ON SCHEMA public TO public")
  ActiveRecord::Base.connection.execute("COMMENT ON SCHEMA public IS 'standard public schema'")
end

desc 'Recreate the database and seed'
task :redo_db => :environment do |t,args|
  # Executes the dependencies, but only once
  Rake::Task["db:drop_schema"].invoke
  Rake::Task["db:migrate"].invoke
  Rake::Task["db:migrate:status"].invoke 
  Rake::Task["db:structure:dump"].invoke
  Rake::Task["db:seed"].invoke
end

end

我使用下面的rake任务来覆盖Rails drop_database方法。

lib / database.rake

require 'active_record/connection_adapters/postgresql_adapter'
module ActiveRecord
  module ConnectionAdapters
    class PostgreSQLAdapter < AbstractAdapter
      def drop_database(name)
        raise "Nah, I won't drop the production database" if Rails.env.production?
        execute <<-SQL
          UPDATE pg_catalog.pg_database
          SET datallowconn=false WHERE datname='#{name}'
        SQL

        execute <<-SQL
          SELECT pg_terminate_backend(pg_stat_activity.pid)
          FROM pg_stat_activity
          WHERE pg_stat_activity.datname = '#{name}';
        SQL
        execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}"
      end
    end
  end
end

编辑:这是Postgresql 9.2+

MacOS，如果postgresql安装了brew:

brew services restart postgresql

UBUNTU,

首先用这个检查(在后台运行的kill服务器)

sudo kill -9 $(lsof -i :3000 -t)

如果你没有找到pid，那么你只需要重新启动 Postgresql服务的命令如下所示:

sudo service postgresql restart