Reattach the database

Phoenix
1

I have/had an elixir app running on fly. A process started to do bad things and I tried restarting the app but the rogue process was still running. I redployed. Rogue proccess still alive and spaming, so in desperation I destory the app. Now I create and deploy but the database is not attached so the deploy fails. I can’t attach the database as it gives and Error: could not resolve app. I can’t create a database because error name is taken.

2

Can you paste the exact log messages you’re receiving? Also, are you sure the new app is part of the same “organization” as the database? You can check by running fly info -a <database-name>.

3

Thanks Kurt,

fly postgres attach
Attaching…⣯ Error Could not resolve App

fly info -a broker-db
WARN app flag ‘broker-db’ does not match app name in config file ‘broker’
? Continue using ‘broker-db’ Yes
App
Name = broker-db
Owner = personal
Version = 0
Status = running
Hostname = broker-db.fly.dev

Services
PROTOCOL PORTS

IP Adresses
TYPE ADDRESS REGION CREATED AT

4

Ah! I think you’ll need this command: fly pg attach -a broker --postgres-app broker-db

Also try running fly info -a broker. It’s possible your logged in user doesn’t have access to those apps.

5

fly pg attach -a broker --postgres-app broker-db
Attaching…⡿ Error An unknown error occured.

fly info -a broker
App
Name = broker
Owner = personal
Version = 0
Status = error
Hostname = broker.fly.dev

Services
PROTOCOL PORTS
TCP 80 => 8080 [HTTP]
443 => 8080 [TLS, HTTP]

IP Adresses
TYPE ADDRESS REGION CREATED AT
v4 213.188.219.25 1h31m ago
v6 2a09:8280:1::3:1325 1h31m ago

6

Not sure if it’s related, but I’m getting an unknown error when trying to detach a postgres cluster at the moment.

7

@Richard_Pruss I’m taking a look at your database now. I will let you know what I find.

8

@Richard_Pruss Looks like one of your VM’s was stuck in a weird state and was preventing our backend from connecting to it. Would you mind trying to kick off the attach process again?

UPDATE:

You can pass in a custom database name via --database-name when issuing the attach command. This may help you workaround the issue you’re experiencing.

9

Thanks for the help shawn, still can’t attach
fly pg attach -a broker --postgres-app broker-db --database-name broker
Attaching…⣷ Error The specified database name ‘broker’ already exists

fly pg attach -a broker --postgres-app broker-db
Attaching…⣟ Error An unknown error occured.

10

@Richard_Pruss You should no longer see the “database name already exists” error. However, we are still investigating a platform-related issue that’s making these commands a little flakey. We will keep you posted!

11

Thanks @shaun the “database name already exists” error. But things are still flakey.

fly pg attach --postgres-app broker-db
Attaching…⡿ Error An unknown error occured.

fly pg attach -a broker --postgres-app broker-db --database-name broker
Attaching…⣟ Error An unknown error occured.

12

If you’re in a rush, you can also do this manually.

Something like this should do the trick:

  1. Connect to your Postgres instance.
    a. fly ssh console --app <app-name>
    b. psql postgres://postgres:$OPERATOR_PASSWORD@$FLY_APP_NAME.internal:5432

  2. Create a new database
    a. \l+ to list databases.
    b. CREATE DATABASE <database-name>;

  3. Create a new user
    a. \du+ to list users.
    b. CREATE USER <user-name> WITH LOGIN PASSWORD 'password';

  4. Grant access to database
    a. GRANT ALL PRIVILEGES ON DATABASE <database-name> TO <user-name>;

  5. Set your DATABASE_URL secret on your target consumer app.
    a. Your URL should look like:
    postgres://<user>:<password>@<postgres-app>.internal:5432/<database-name>
    b. Test the above URL using psql to ensure it works before setting it.
    c. fly secrets set DATABASE_URL=<database-url> --app <app-name>

1 Like
13

I’m running into this too attaching a DB on a new account and app. I got excited by Shaun’s SSH instructions, but that one’s hanging with “Waiting for host ⣟”

Any ideas would be much appreciated!

14

@Brendan_Roberts I just ssh’d directly into the database cluster and did the instructions, since my host wasn’t deployed yet

15

Hey everyone,

There has been a rewrite of PG attach/detach that has been made available within flyctl as of version v0.0.263. If anyone is still having trouble or is interesting in trying it out and providing some feedback, would love to hear how things go.

cc:// @Brendan_Roberts @Matt_Van_Veenendaal @Richard_Pruss

16

@shaun

I ran flyctl postgres attach --postgres-app my-postgres-cluster-name and I am getting the following error.

Running flyadmin database-list
Error unexpected end of JSON input
17

@Bhanu_Teja_Pachipulu Would you mind running fly image show --app <postgres-app-name> and let me know which version you’re on?

Also, can you confirm that the Postgres you’re targeting is healthy?

18

@shaun I am on flyctl 0.0.263 version.

After running the command you gave, I got the following output.

Screenshot 2021-12-09 at 6.43.27 AM
Screenshot 2021-12-09 at 6.43.27 AM1922×686 225 KB

Then I ran fly image update --app <my-postgres-app> (because in the output it asked me to update)

Then I ran flyctl postgres attach --postgres-app <my-postgres-name> and it worked.

Thanks!!

1 Like
19

@Bhanu_Teja_Pachipulu Perfect! The flyadmin commands were introduced in v0.0.3, so that would make sense.

1 Like
20

@shaun I think it would be good to post about the new update in the below thread too.