Postgres Flex - Important Stability Updates

Fresh Produce
11

I did this for an app that was on 0.36 or something. It felt quite scary.

I tried to follow Backup, Restores, & Snapshots · Fly Docs which mentions a fly info command which no longer exists and suggests restoring from a snapshot, which also didn’t work.

I first tried to create a new pg machine from the existing one by doing

fly volumes list --app old-db
fly volumes snapshots list vol_something
fly postgres create --snapshot-id vs_something --name new-db

but it the snapshot thing had no effect, since when I did fly pg connect --app new-db and \l I didn’t see it listed.

I then did

fly postgres import --app new-db "$DATABASE_URL"

which was mentioned in the email, using as DATABASE_URL the secret that’s currently in use by the app.

And then as mentioned at Backup, Restores, & Snapshots · Fly Docs I tried to do the switchover with detach&attach.

fly postgres detach old-db

failed with

Error: error running user-delete: 500: ERROR: role "myapp" cannot be dropped because some objects depend on it (SQLSTATE 2BP01)

I tried to fly machine stop or fly scale count 0 the app as suggested in some forum posts, but to no avail. Some posts suggested deleting the user which seems very destructive.
Then I saw How to detach postgres without deleting db user? - #3 by Elder which suggests just reassigning ownership. Apparently that’s easily reversible. So I did

fly postgres connect --app old-db
…
REASSIGN OWNED BY myapp TO postgres;
\c myapp
REASSIGN OWNED BY myapp TO postgres;

(note it was not enough to reassign in the master db, had to switch to myapp db)

and then I was able to run

fly postgres detach --app myapp old-db
fly postgres attach --app myapp new-db

This told me Secret "DATABASE_URL" was scheduled to be removed from app myapp and Postgres cluster new-db is now attached to myapp The following secret was added to myapp: DATABASE_URL=*** .

HOWEVER: On trying to load the page, it was still trying to connect to the old db, which now was missing the user.

I tried fly machine restart but still it used the old connection string. I then manually did

fly secrets set --app myapp 'DATABASE_URL=***'

which told me Updating existing machines in 'myapp' with rolling strategy and seems to have both set and applied(?) the secret.

Maybe there’s a separate command for that which fly attach neglected to run.

Hope this helps someone (or inspires fixing some papercuts in flyctl).

3 Likes