How to reset/clear database?

[piotrkulpinski]

Hi,

I want to clear and re-populate a database on my fly instance.
I tried drop & create but I get an error: “There is 1 other session using the database.”.

Is there a way to clear database or at least disconnect other apps from using it so I can drop it?

PS: I tried fly postgres detach but I get the “Error error running user-delete: 500: ERROR: role “xxx” cannot be dropped because some objects depend on it (SQLSTATE 2BP01)” error.

Please help.
Thanks

[guillaume]

Hi,

Yes, Postgres prevents you from dropping a DB where an app is connected, since it would mess up this app.

The simplest thing would be to edit your app’s code and not to open a connection to your DB, then run fly deploy.

You can also stop the machine running your app:

• fly machine stop <machine-id> (later restart it with fly machine start <machine-id>), if this is a V2 app running on Machines (our latest platform) (get the ID of the machine by running fly machine list.
• fly scale count 0 (later restart it with fly scale count 1), if it’s a legacy V1 Nomad app.

Then you can try again to drop & recreate the DB.

Tell me if that works for you.