Drop DB on AWS RDS instance - Postgres

Question

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?

@TiagoMartinsPeres I'm not exactly sure as this was a long time ago, but the premise is to connect to a different db (you must be connected to a db to execute any query) and execute the DROP from there. I think you can do it from the default "postgres" db (every postgres has it). Please update if you managed. — NotSoShabby
– NotSoShabby, Commented Mar 3, 2022 at 10:19
@TiagoMartinsPeres, after a short read, maybe you can try executing psql -l on your server (directly on the postgres server, not as a query). This will give you a list of all databases available on that server. Then choose a db that is not the one you want to drop and run \connect <DB_NAME>, which will connect you to that db. There you should be able to run the DROP DB commnad — NotSoShabby
– NotSoShabby, Commented Mar 3, 2022 at 10:24

ADV-IT · Accepted Answer · 2020-09-09 22:45:23Z

6

Worked for me:

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

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

answered Sep 9, 2020 at 22:45

ADV-IT

8811 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Robbins Over a year ago

If you only have one DB in step 1, try create database tmpdb; then \c tmpdb

Rodrigo Murillo · Accepted Answer · 2019-12-26 14:13:43Z

0

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;')

answered Dec 26, 2019 at 14:13

Rodrigo Murillo

13.7k2 gold badges35 silver badges53 bronze badges

3 Comments

NotSoShabby Over a year ago

this gives me the same error. I think that AWS is wrapping it in a transaction, and not psycopg. I can also prove it by the fact that im able to drop the db locally (on a postgres container) with the same command

Rodrigo Murillo Over a year ago

Have you tried this PSQL command from a connected session? postgresql.org/docs/9.1/ecpg-sql-set-autocommit.html Try to set autocommit Off as shown. Then rerun the drop.

NotSoShabby Over a year ago

I did try that. Same error. this is so frustrating. I dont have control over my db

Collectives™ on Stack Overflow

Drop DB on AWS RDS instance - Postgres

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related