fly deploy changed postgres password - what did I do wrong?

[flk]

I tried removing the scale to zero on my postgres by following Scale to zero for Postgres Development projects · Fly Docs
I saved the config, removed the FLY_SCALE_TO_ZERO line noted that image show said flyio/postgres 14.6, then ran flyctl deploy . --image flyio/postgres:14.6.
It seemed to deploy fine, and then I tried logging into my app and it said libpq: failed (connection to server at "myapp-db.flycast" (fafa:1:fafa:0:1::6), port 5432 failed: FATAL: password authentication failed for user "myapp" ). I could see in the logs for the db app that requests were reaching it, but obviously the password for that user got changed.

Is that supposed to happen? (Shouldn’t the docs say so then?)

[mayailurus]

Hi… A couple of your older posts mention fly postgres detach calls, so my guess is that you unknowingly deleted a user and/or altered passwords prior to this, .

Also, the old Stolon-based image that you’re using doesn’t support FLY_SCALE_TO_ZERO in the first place, as far as I know. (I see no mentions in its source code.) Perhaps its config got mangled as well at some point?

Finally, the fly postgres commands and Legacy Postgres in general are officially deprecated now (after having spent ~2 years in a kind of limbo):

Unmanaged Fly Postgres is deprecated in favor of ‘fly mpg’ (Managed Postgres). Please visit https://fly.io/docs/mpg/overview/ for more information about Managed Postgres.

So you can generally expect a less and less smooth ride as time goes on…

(I don’t speak for Fly.io in any way, though.)

[flk]

It did work up until I ran that deploy, but yeah maybe something about my earlier upgrade trouble carried over.

I hadn’t noticed managed postgres was now fully “released”. Automated backups seems nice, but $38/month is quite a step up from my current $5.

[mayailurus]

True… The cost is mainly for the Support / “it just works” side, , rather than the backups, per se.

(With your current setup, you’re expected to be your own expert database administrator…)

[halfer]

If you’re on a budget, then as an alternative to self-maintained clusters, also consider:

• Supabase, which lets you have a free 500MB managed Postgres database forever (see here). I’ll be deploying this soon.
• SQLite and LiteFS (see here). This is on my radar to try.

[flk]

I saw 📣 PSA: Supabase *isn't* going to keep supporting our existing projects but that’s about a special integration being removed? So you’d be setting up supabase on supabase.com and then separately setting flyctl secrets set DATABASE_URL supabase.com/something, just without being able to manage it with flyctl postgres?

[halfer]

Ah, I don’t think I knew that Supabase was previously integrated. Yes, it would just be a regular external database provider, like AWS RDS. You can indeed use Fly secrets for credentials, yes.