The process is simple, but it took many days and tries to figure everything out with help from the community. Here is how I did it. (I use Windows 11. The rails app is “moon” with a corresponding database “moon-db”. They are successfully deployed and working properly online.)
Backup - 1: Open a Command-line window where you launched your app (e.g., c:\moon). Type:
fly proxy 15432:5433 -a moon-db
NB: Windows will respond with: <Proxying local port 15432 to remote [moon-db.internal]:5433>. It looks that the Windows has hung. Ignore it
2: Open a new Command-line window of the same directory. Type:
pg_dump -p 15432 -h localhost -U postgres -c -d moon -f db_backup
NB 1. It will prompt you for password. You type the moon-db password noted down during the creation of the moon app, and hit the Return key - The cursor will not move while you type
NB 2. You will see a new file db_backup adding to the moon directory, which is your database backup text file. You can view it in a text editor
NB 3. Be extremely careful with the options. E.g., moon, not moon-db, which halted me for days until someone helped me to figure it out. It won’t restore if “-c” is missed
Restore - 1: Open a Command-line window where you launched your app. Type:
fly proxy 15432:5433 -a moon-db
2: Open a new Command-line window of the same directory. Type:
psql -p 15432 -h localhost -U postgres -d moon -f db_backup
3: Continue in this window, type:
fly postgres detach -a moon moon-db
The Windows should show something like this: <? Select the attachment that you would like to detach (Database will remain intact): PG Database: moon, PG User: moon, Environment variable: DATABASE_URL
Secret “DATABASE_URL” was scheduled to be removed from app moon
Detach completed successfully! >
[Somewhere you need to hit the Return key to continue.]
4: Continue in the same window, type:
fly postgres attach -a moon moon-db
The Windows should show something like this: <? Database “moon” already exists. Continue with the attachment process? Yes
Registering attachment already exists. Continue with the attachment process? (y/N) y
Creating user
Postgres cluster moon-db is now attached to moon
The following secret was added to moon:
DATABASE_URL=postgres://moon:xxxxxxxxxxxxxxx@top2.nearest.of.moon-db.internal:5432/moon?sslmode=disable>
[Somewhere you need to answer questions by Typing Y to continue]