mix ecto.drop is not dropping the database during build about blockscout-terraform HOT 14 CLOSED

The user these scripts run with has sufficient permissions to create databases, and as far as I'm aware, permission to drop them as well.

from blockscout-terraform.

Was there any error output? I would assume this is how we should be dropping the database, but perhaps it may be a better question for @KronicDeth or @alexgaribay since I'm not as familiar with the details of the application itself

from blockscout-terraform.

There was no error given during the build. Everything continued on as normal but the database never dropped. I'm wondering if it didn't have the right permissions to drop the db.

from blockscout-terraform.

RDS PostgreSQL permissions work the same as normal postgres in that you need either CREATEDB or to be the owner (in postgres, not in RDS/AWS permissions) of the database to DROP it. Of course, to drop and recreate you need CREATEDB, so you'll need to be the owner to DROP (or some level of admin that can drop databases you don't own) and then also have CREATEDB permissions.

from blockscout-terraform.

The script is run with set -e at the top, so if a command exits non-zero, it should fail the entire script as a result. There are some caveats with that in Bash, but I would guess the command actually succeeded, but perhaps didn't do what we think that command does. i think you need to add --force as an argument, because otherwise it expects user input for confirmation, at least based on the docs.

from blockscout-terraform.

I'll give it a try with --force.

from blockscout-terraform.

Here is the error that I'm receiving using mix ecto.drop --force

Script - bin/deployment/migrate
[stdout]==> ethereum_jsonrpc
[stdout]Compiling 15 files (.ex)
[stdout]Generated ethereum_jsonrpc app
[stdout]==> explorer
[stdout]Compiling 47 files (.ex)
[stdout]Generated explorer app
[stdout]==> explorer_web
[stdout]Compiling 47 files (.ex)
[stdout]Generated explorer_web app
[stderr]** (Mix) The database for Explorer.Repo couldn't be dropped: ERROR 55006 (object_in_use): database "sokol" is being accessed by other users
[stderr]
[stderr]There are 10 other sessions using the database.```

from blockscout-terraform.

@bitwalker Any luck finding a resolution to the above issue?

from blockscout-terraform.

@bitwalker The only way I'm finding a solution to this is by destroying this build and starting an entirely new build.

from blockscout-terraform.

Hmm, I think this is a rare case where you need to connect as a superuser directly, drop all other connections to the database forcibly, then dropping the database by hand, letting the new version run the schema migrations and seeds from scratch. I don't think there is a way for us to have Ecto kick users during a drop - it seems to assume that the drop will be run by the only connected session. I haven't dug in further, but my recommendation would be to treat this as a one-off manual job, rather than trying to deploy this as part of a deployment lifecycle event. If it truly needs to be automated, then we probably need to run a shell command with psql to connect as superuser, drop other connections, and drop the database, rather than using mix ecto.drop.

from blockscout-terraform.

Thanks, @bitwalker. I think you're right. I don't see many times that we'll need to drop in the future. I will do it manually for now 👍

from blockscout-terraform.

@acravenho this is my go-to answer for how to kick clients in Postgres https://stackoverflow.com/a/5408501/470451

from blockscout-terraform.

Bookmarked, thank you 👍

from blockscout-terraform.

Is this issue resolved? since i am still unable to drop db despite using --force. And it is rather frequent

from blockscout-terraform.