CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
NotSoShabby
NotSoShabby

Reputation: 3708

Drop DB on AWS RDS instance - Postgres

Trying to drop a db from AWS RDS (postgres) is blocked only for superuser (which is reserved to AWS only).

trying to run this command on my instance:

DROP DATABASE "dbname"

results in error:

psycopg2.InternalError: DROP DATABASE cannot run inside a transaction block

I saw this issue in AWS forums, which seems to have been active by a lot of people, but no AWS representative giving a valid solution.

How can I drop my db without taking down the whole instance and raising it again?

Upvotes: 4

Views: 6389

Answers (2)

ADV-IT
ADV-IT

Reputation: 841

Worked for me:

  1. Connect to postgres DB, disconnect from DB you want to DROP!
  2. select pg_terminate_backend(pid) from pg_stat_activity where datname='yourdb';
  3. drop database 'yourdb';

Step 2 and 3 execute fast in order to prevent user rdsadmin reconnect

Upvotes: 6

Rodrigo Murillo
Rodrigo Murillo

Reputation: 13640

Try using ISOLATION_LEVEL_AUTOCOMMIT, a psycopg2 extensions:

No transaction is started when command are issued and no commit() or rollback() is required.

The connection must be in autocommit mode. You can way can set it using psycopg2 is through the autocommit attribute:

import psycopg2

con = psycopg2.connect(...)
con.autocommit = True

cur = con.cursor()
cur.execute('DROP DATABASE db;')

Upvotes: 0

Related Questions