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.
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.
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>
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
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
With the database gone, you can now run ecto to create the db afresh!
Not a single thing. This is a picture I took at Siena. ❤️ Italy, can't wait to go back!