After some investigation, I noticed a setting "disable_machine_autostart": true in the machine running the Postgres App.
I didn’t set this setting, but it went away with the following update
fly machines update <machine_id> --autostart=true --app <app_name>
It seems the app is back to normal and auto-starting without any problem.