Multi Region Postgres Allowing Writes on All Nodes

Questions / Help
1

When I connect in a region that should be read only, it still allows writing to the reader node.

fly status:

➜  lucky_jumpstart git:(hot-cuz-im-fly) ✗ fly status -a tmp-postgres
App
  Name     = tmp-postgres
  Owner    = personal
  Version  = 2
  Status   = running
  Hostname = tmp-postgres.fly.dev

Instances
ID              PROCESS VERSION REGION  DESIRED STATUS                  HEALTH CHECKS           RESTARTS        CREATED
ecd9c017        app     2       ord     run     running (replica)       3 total, 3 passing      0               1h13m ago
60357b81        app     2       scl     run     running (leader)        3 total, 3 passing      0               1h14m ago

Which denotes that it is a replica in the status.

When I combine the host of the DATABASE_URL and FLY_REGION, I get ord.top2.nearest.of.tmp-postgres.internal, which is allowing me to write ord.tmp-postgres.internal also allows me to write.

Shouldn’t the replica be throwing a “read only” error when I try to write to this node?

2

Port 5432 on every nodes forwards you to the primary, writable postgres instance.

Port 5433 connects you directly to postgres replicas.

So the connection string you want for your primary region is:

top1.nearest.of.tmp-postgres.internal:5432

And in your replica regions:

top1.nearest.of.tmp-postgres.internal:5433
1 Like
3

AHHH perfect, I glossed over that in the docs!

Do I not need to region name itself? Example from the docs:

class Fly
  def self.database_url
    primary = ENV["PRIMARY_REGION"]
    current = ENV["FLY_REGION"]
    db_url = ENV["DATABASE_URL"]

    if primary.blank? || current.blank? || primary == current
      return db_url
    end

    u = URI.parse(db_url)
    u.hostname = "#{current}.#{u.hostname}"
    u.port = 5433

    return u.to_s
  end
end
4

I just fixed those docs. :wink:

top1.nearest.of.tmp-postgres.internal is new since we first wrote those docs, so all you need to do now is change the port and you’re good.

5

~~Hmm, don’t see the fixups but that’s okay. Looking here: https://fly.io/docs/getting-started/multi-region-databases/~~ must have just needed to deploy

Same condition? when ENV["PRIMARY_REGION"] != ENV["FLY_REGION"]?

6

Still isn’t working, but I’ve found breadcrumbs:

fly postgres attach added top2 instead of top1.

By switching this to top1, it now works as expected

Seems like a possible bug