Can't delete mpg database

[hilja]

I can’t delete an mpg database, it just hangs. I’ve turned off the machine which uses it.

Also the toast message is under the blur, had to remove the overlay to see what it says

[Sam-Fly]

Are you trying to delete the whole Database cluster, or just one of your databases within it?

Most commonly deletes fail when there’s still active connections to the database, which prevents Postgres from dropping the db. You might still have some kicking about blocking the delete. If you’ve just disconnected the front end app, I’d retry in a little bit.

If you’re still seeing issues deleting it after 30+ minutes, you might want to use fly mpg connect to pop a shell on the DB and check for any lingering connections

[hilja]

Yeah a database, not a cluster.

It’s been like this for a day now, I’ve turned off all the machines that might use it and also made sure I’m not proxying it. Also no active leases on any machines via CI etc.

Also tried in psql shell fly mpg connect with drop database mydb; but it won’t let me ERROR: must be owner of database mydb.

I managed to delete another database few days ago when I was messing with it.

[hilja]

Okay I see six connections:

SELECT * FROM pg_stat_activity WHERE datname = 'mydb';

But I can’t terminate them:

select pg_terminate_backend(pid) from pg_stat_activity where datname='mydb';

[hilja]

Also the dash shows some connections:

But where are they coming from, the server using the db is turned off. All the tabs I had open with my app are closed.

[hilja]

Oh, is it the healthcheck? But it hit’s my app, which then hits the db, and the app is turned off. Or is there another healthcheck for mpg?

[hilja]

Had to destroy the machine which was using the db, turning it off wasn’t enough. Does this mean the health check process lives on if a machine is turned off?

[Sam-Fly]

No, we don’t send health checks to stopped machines. However it looks like your app has had 1 machine that’s been consistently started, for the past 48h+.

If you check on fly-metrics.net you’ll see metrics for that 48e4... machine showing it’s been running consistently up until the past 2h. It was likely the source of those connections, might have just been missed when stopping machines in your consuming app

[hilja]

Thanks for the tips. I think I got it now.

[kyle]

Small update here—you can now force delete a database:

This does the equivalent of:

DROP DATABASE mydb WITH (FORCE);

which terminates active connections.

@hilja

Also the toast message is under the blur, had to remove the overlay to see what it says

This is also very annoying and is now fixed

[hilja]

This is great, thanks!