How to convert your not-free Postgres to free Postgres

You may not have seen this, but we launched a free volume tier a few weeks ago. Which means, free Postgres for side projects.

Our Postgres installations make it easy to move volume sizes. Here’s what you’d run assuming you have a single Postgres instance running and want to scale the volume down to 1GB:

  1. Add a new 1GB volume to your Postgres app: fly volumes create pg_data --size 1 --region <your-pg-region> -a <pgname>
  2. Run fly status -a <pgname>, note which instance is the leader and how many instances are running
  3. Add one more instance: fly scale count 2 -a <pgname> (add a second instance)
  4. Wait a few minutes, then run fly status to make sure the new instance is in secondary state
  5. fly volumes list to get the old volume ID
  6. fly volumes delete <old-volume-id>
  7. fly scale count 1 -a <pgname>: scale back to one instance

This works because all our Postgres apps are clusters, even if they’re only running one instance. When you scale up, it creates a replica. When you remove the old volume, it stops the primary, the new replica takes over, and you’re back down to one happy Postgres.

Be careful: under no circumstances should leave one instance of postgres running with 2+ volumes. If that VM crashes, it might come up on an empty disk. You should always have the exact number of volumes as fly scale count <x> is set to.

4 Likes

I think step #3 might scale the app instead of the db, here is what I think you need to do:

fly scale count 2 -a <pgname>

Yep, good catch!

I finished, but now I am getting:

failed to connect: ** (DBConnection.ConnectionError) tcp recv: closed

I had to re-deploy my app for it to connect to the DB after scaling down. I just made a README change and deployed that and it worked.

TBH, not having a replication step and then seeing it not work made me think I lost the data.

All the steps need -a <pg-name>

1 Like

@kurt is there room here for a new CLI keyword to allow volume size-up or size-down, automating your instructions above, with the requisite waits, checks, etc.

fly pg vol expand/contract --from=x --to=y or something…

2 Likes

The two times I’ve followed this process to scale down and scale back up again the app has been unable to connect to the database.

The only way I’ve been able to get it working again is to detach and then re-attach the database.

Is there a better way to do this without downtime?

Does increasing vm instances before increasing volumes not work?

No it doesn’t. You do have to add a volume before you add VM instances. This is fine, what you don’t want is to have 2 volumes and one instance running for long periods of time.