fly pg import

There is much it can do, but complicated cases require varying amounts of manual stitch-up afterwards.

fly pg import would work in this context by creating an ephemeral importer machine within the new organization—and then connecting back to your old database from there. A special variant of Flycast makes this possible (if I’m reading its docs correctly).

fly pg import diagram again: new db ← ephemeral machine (within the new org) ← old db (via flycast)

I don’t have a second organization myself, to test, but something along the lines of…

fly pg create --name new-db --org new-org

# allow new-org to access old-db...
fly ips allocate-v6 --private --org new-org -a old-db

# use the above flycast IP to talk to old-db from within new-org...
fly pg import -a new-db 'postgres://user:password@[fdaa:0:22b7:0:1::3]:5432/cdb?sslmode=disable'

# and if you already have a web app in the new organization, then...
fly pg attach      -a new-web-app --database-name cdb new-db
fly secrets deploy -a new-web-app

Replace fdaa:0:22b7:0:1::3 with the fresh address that fly ips allocate-v6 gave you; user, password, and cdb come from the old DATABASE_URL.

One of the limitations is that it will only import a single cdb, so if you have multiple, you will need to do this again. Another is that user is not created in new-db, and ownerships all come out as postgres instead. Consequently, if you had custom roles, then you will need to re-create them all by hand.

Hope this helps a little!