I cannot find a way to detect which Postgres db is connected to which App.
As far as I could see is, that fly postgres attach more or less sets the DATABASE_URL secret, which cannot be retrieved later from the fly secrets to check its content.
Also apps info and status json payloads do not show anything in that regards.
How can I check which DB is currently connected to a given app?
I want to simplify our backup/restore strategy, which consists of a blue-green db instances. if there is a db-blue currently connected, a snapshot gets restored to db-green and attached to the app while blue gets detached.
If you run fly postgres users list -a <postgres cluster app name> on your Postgres app, then you should see the app name of the consuming app in the list of users.
Hi @andie
Thank you for your response. I checked it and it works as long as you do not tinker around with users or using snapshots for other apps.
e.g I got one playground database that was created out of a snapshot from another playground. I re-use the same db password in this case and the database has the same name. But it is assigned to a different app. So if I check the db users, it contains now the initial (backup) and the new playground users … dev, staging, preprod users so to say.
So this seems not to work out for us.
Another thing I realized was that I cannot just attach a restored snapshot to the same app that it was assigned to already, since it tries to create that database user. So I had to use the secrets set DATABASE_URL instead to assign it manually. Not sure what else I missed from not doing postgres attach?
I haven’t had a chance to test this, but for dbs created from snapshots, have you tried using the fly postgres detach command and then re-attaching the same app or whatever app you choose?
You can specify a custom database name and database user with the fly postgres attach command, too, which could be helpful?
For future reference, the DATABASE_URL secret becomes an environment variable within the runtime environment of your app’s Machines. So you should be able to get it with fly ssh console and env | grep DATABASE_URL.
To the best of my knowledge, the following is still true:
When you attach an app to Postgres, a number of things happen:
A new database and user are created in the Postgres cluster app, using the name of the consuming app (a Fly App). If the consuming app is named “my-app”, then both the database and the user are named “my_app”.
The user is allocated a generated password.
The consuming app and the Postgres app are marked as attached in the great floating ledger in the Fly.io cloud, which enables you to use fly postgres detach later.
So I think if you’ve manually given your app access to the database you want it to have access to, you’re only missing the ability to use the convenience function fly postgres detach to revoke it.