Force drop db enquanto outros podem estar conectados

PostgreSQL 13 adicionado:

DROP DATABASE mydb WITH (FORCE);

O manual:

FORCE

Tente encerrar todas as conexões existentes com o banco de dados de destino. Ele não termina se transações preparadas, slots de replicação lógica ativa ou assinaturas estiverem presentes no banco de dados de destino.

Isso falhará se o usuário atual não tiver permissões para encerrar outras conexões. As permissões necessárias são as mesmas pg_terminate_backenddescritas na Seção 9.27.2 . Isso também falhará se não pudermos encerrar as conexões.

No PostgreSQL 12 e versões anteriores , você não pode descartar um banco de dados enquanto os clientes estiverem conectados a ele.

Pelo menos, não com o dropdbutilitário - que é apenas um wrapper simples em torno da DROP DATABASEconsulta do servidor.

A solução alternativa bastante robusta segue:

Conecte-se ao seu servidor como superusuário , usando psqlou outro cliente. Não use o banco de dados que deseja eliminar.

psql -h localhost postgres postgres

Agora, usando o cliente de banco de dados simples, você pode forçar o descarte do banco de dados usando três etapas simples:

1. Certifique-se de que ninguém possa se conectar a este banco de dados. Você pode usar um dos seguintes métodos (o segundo parece mais seguro, mas não impede conexões de superusuários).

   /* Method 1: update system catalog */
   UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';
   
   /* Method 2: use ALTER DATABASE. Superusers still can connect!
   ALTER DATABASE mydb CONNECTION LIMIT 0; */
   

2. Força a desconexão de todos os clientes conectados a este banco de dados, usando pg_terminate_backend.

    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'mydb';
   
    /* For old versions of PostgreSQL (up to 9.1), change pid to procpid:
   
    SELECT pg_terminate_backend(procpid)
    FROM pg_stat_activity
    WHERE datname = 'mydb'; */
   

3. Solte o banco de dados.

    DROP DATABASE mydb;
   

A etapa 1 requer privilégios de superusuário para o primeiro método e privilégios de proprietário do banco de dados para o segundo. A etapa 2 requer privilégios de superusuário . A etapa 3 requer privilégio de proprietário do banco de dados.

O Postgres 13 adiciona a FORCEopção para arquivos DROP DATABASE. Veja a resposta de filiprem .

O utilitário shell dropdbé basicamente apenas um wrapper em torno do comando SQL e herda a mesma opção. Portanto, agora também é simples e confiável do shell:

dropdb mydb --force

Ou curta:

dropdb mydb -f

Para Postgres 12 e anteriores , existe uma maneira com os utilitários shell dropdb& pg_ctl(ou pg_ctlclusterno Debian e derivados) também. Mas o método do filiprem é superior por vários motivos:

• Ele apenas desconecta os usuários do banco de dados em questão.
• Ele não precisa reiniciar todo o cluster.
• Impede reconexão imediata, possivelmente estragando o dropdbcomando.

eu cito man pg_ctlcluster:

Com a --forceopção, o modo "rápido" é usado, que reverte todas as transações ativas, desconecta os clientes imediatamente e, portanto, encerra de forma limpa. Se isso não funcionar, o desligamento será tentado novamente no modo "imediato", o que pode deixar o cluster em um estado inconsistente e, portanto, levará a uma execução de recuperação na próxima inicialização. Se isso ainda não ajudar, o processo do postmaster é encerrado. Sai com 0 em caso de sucesso, com 2 se o servidor não estiver em execução e com 1 em outras condições de falha. Este modo deve ser usado apenas quando a máquina estiver prestes a ser desligada.

pg_ctlcluster 9.1 main restart --force

ou

pg_ctl restart -D datadir -m fast

ou

pg_ctl restart -D datadir -m immediate

imediatamente seguido por:

dropdb mydb

Possivelmente em um roteiro para sucessão imediata.

Usando a resposta do @filiprem no meu caso e simplificando:

-- Connecting to the current user localhost's postgres instance
psql

-- Making sure the database exists
SELECT * from pg_database where datname = 'my_database_name'

-- Disallow new connections
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_database_name';
ALTER DATABASE my_database_name CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'my_database_name';

-- Drop database
DROP DATABASE my_database_name

Para aqueles que encontram esta pergunta agora, 8 anos depois, o PostgreSQL 13 finalmente adiciona o --forcesinalizador que esta pergunta pede:

DROP DATABASE database_to_drop WITH (FORCE)

ou

dropdb [-f/--force] database_to_drop

vai

Tente encerrar todas as conexões existentes com o banco de dados de destino. Ele não termina se transações preparadas, slots de replicação lógica ativa ou assinaturas estiverem presentes no banco de dados de destino.

Isso falhará se o usuário atual não tiver permissões para encerrar outras conexões. As permissões necessárias são as mesmas do pg_terminate_backend, descritas na Seção 9.27.2. Isso também falhará se não pudermos encerrar as conexões.

https://www.postgresql.org/docs/13/sql-dropdatabase.html

Usando a resposta de @Dorian , corrigida e aprimorada:

DB="my_database_name" psql <<EOF
-- Making sure the database exists
SELECT * from pg_database where datname = '${DB}';

-- Disallow new connections
UPDATE pg_database SET datallowconn = false WHERE datname = '${DB}';
ALTER DATABASE ${DB} CONNECTION LIMIT 1;

-- Terminate existing connections
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '${DB}';

-- Drop database
DROP DATABASE ${DB};
EOF

Se você estiver em algo como o RDS, onde as conexões sem um banco de dados selecionado o colocam no banco de dados que você pediu para ser criado por padrão, você pode fazer essa variante para evitar ser a última conexão aberta.

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist; 

 CREATE DATABASE temporary_db_that_shouldnt_exist with OWNER your_user; 

 \connect temporary_db_that_shouldnt_exist 
 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'the_db_you_want_removed'; 


 DROP DATABASE IF EXISTS the_db_you_want_removed; 
 -- 
 -- Name: the_db_you_want_removed; Type: DATABASE; Schema: -; Owner: your_user 
 -- 

 CREATE DATABASE savings_champion WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; 


 ALTER DATABASE the_db_you_want_removed OWNER TO your_user; 

 \connect the_db_you_want_removed 

 DROP DATABASE IF EXISTS temporary_db_that_shouldnt_exist;