How to detach postgres without deleting db user?

I want to detach a postgres cluster to replace it with a newer one. I want to keep all the data as is in case I need to rollback.

The detach command wants to delete the db user for some reason, I don’t understand why, what is the value here? Not like a user record consumes any resources. But since the db user has data and cannot be deleted, the detach command fails and keeps the pg attached.

➜ postgres detach --app app db
? Select the attachment that you would like to detach (Database will remain intact):  PG Database: db, PG User: app, Environment variable: DATABASE_URL
Error error running user-delete: 500: ERROR: role "app" cannot be dropped because some objects depend on it (SQLSTATE 2BP01)

– How do I detach pg cluster and keep its user and data? It doesn’t seem like I can have two clusters attached since they have the same DATABASE_URL secret
– Why can’t I have more than one pg attached?
– Why does it have to delete the db user on detach?
– How do I list all attached pg clusters?

@Elder It’s typically a bad practice to use the same PG user across multiple applications.

In any case, this is a bug in the Stolon setup’s detach command, you can workaround the issue by doing the following:

Why can’t I have more than one pg attached?

You can attach more than one app to a database, they just “shouldn’t” have the same user. You can look at the attach options if you need additional customization. fly pg attach --help.

@shaun thanks, I found this post earlier and did exactly that – reassigned data to another user in the old cluster.

I’m not using the same PG user across multiple applications, I was performing migration from the stolon based stack to postgres-flex. I wanted to keep my old cluster as it was so I could quickly return to it in case the migration was not successful. In any case I don’t really see why detach insists to delete the user and doesn’t make it optional, eg delete user? [y/n]

Oh, I assume I can also attach more than one cluster by having custom --variable-name? I somehow missed that

1 Like