Backup and Restore PostgreSQL in fly.io, step by step

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]

5 Likes

Thank you! I just followed your instructions for backup and all went well.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.