I keep getting this postgresql on a dedicated-cpu-1x/2GB postgres app.
ERROR: database "my_db" is being accessed by other users
I often get it when I try to do drop database my_db; in psql, and I am also getting it constantly when I attempt to set up a baseline Prisma migration (prisma migrate dev --name initial-migration --create-only) - it seems to create a temporary database named something like prisma_migrate_shadow_db_2dee3f30-a650-4a97-8d36-d1065aead477, and then I guess it fails to drop it, and the prisma command fails.
Does anyone have any idea what might be causing this?
Which one isn’t getting dropped? The prisma_migrate_shadow or a different one? That happens when something has a connection open to a particular DB. I would guess that prisma is keeping a connection to the DB its trying to drop for some reason. Or possible the app?
We don’t connect to your DBs, so it’s most likely something within the app causing it.
I think it turned out I had a Node app running at the time which kept connecting to it. The Node app used Prisma as an ORM for the postgresdb I was trying to drop. Prisma maintains a pool of DB connections. I attempted to use psql to kill all active connections before dropping the DB, but I’m guessing Prisma just immediately made new connections every time, preventing me from dropping the database. At some point it worked, maybe because I performed the drop quickly enough after killing connections. In hindsight I should have just turned off the Node app.
I’m only about 65% certain of the above though. Hope it helps.
I initially thought that this was due to GUI app that I was using, but looks like it’s not.
It’s completely random - I tried to run same command multiple times and finally it was ok and migration worked.
Now, this is just me thinking out loud, not finger pointing - maybe this is due to monitoring? Or some internal tool to track performance?
I should mention that I am using cheapest, 1 instance Postgres with 256MB Ram - this is just for dev.
It’s a strange bug, I stopped both my machines and still Prisma threw that error. Eventually I just ran prisma migrate reset which worked and let me migrate & seed the DB. It could be a problem on Prisma’s end with old migrations left pending, dunno. Or that migrate reset just skips the access check.
Just in case someone else is having the same issue, it is caused by the user flypgadmin performing health checks in the database, If you remove its role, you will be able to run your migrations just fine, after everything is done, you can give it back the roles that you removed
