Ecto drop Postgres database on AWS RDS

Lets say during your development cycles, you have screwed your postgres database and the easiest way to get out of the mess is to drop the database and restart fresh. In this example we will look at a Phoenix project that uses a Postgres database.

Warning

To make it clear, dropping the database is a bad idea if you haven't taken snapshots. Dropping deletes all data; so do it only in non-prod.

Problem: Dropping a database fails due to immortal rdsadmin

mix do ecto.drop, ecto.create, ecto.migrate

This nicely works on your local postgres instance.
However, if the database is on AWS RDS, you'll be greeted by this interesting message.

$ mix ecto.drop
** (Mix) The database for SuperPetStore.Repo couldn't be dropped, reason given: ERROR:  55006: database "petdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.
LOCATION:  dropdb, dbcommands.c:840

If you look at the number of connections

> select * from pg_stat_activity;

Sure enough, there will be one persistent connection from rdsadmin. It is AWS RDS user that takes care of backup, updates and maintenance. This connection gets in the way of dropping the db and starting afresh.

> use postgres;
You are now connected to database "postgres" as user "petuser"
Time: 0.078s
postgres> drop database petdb;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
database "petdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Time: 5.031s (5 seconds), executed in: 5.030s (5 seconds)
postgres>

pg_terminate_backend

select pg_terminate_backend(pid) from pg_stat_activity where datname='petdb';drop database pipeops;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
+------------------------+
| pg_terminate_backend   |
|------------------------|
| True                   |
+------------------------+
SELECT 1
DROP DATABASE
Time: 0.118s

First pg_terminate_backend allows you to terminate the rdsadmin account and shoving in the drop statement in the same line gets the job done before rdsadmin spawns up and connects to petdb.

With the database gone, you can now run ecto to create the db afresh!

Wait. What does the picture above have to do with this post?

Not a single thing. This is a picture I took at Siena. ❤️ Italy, can't wait to go back!