Multi region Postgres Implementation

I have implemented the multi region implementation and now my app as well as Postgres apps(V2) have two clusters in different regions. Can someone guide me on how i can verify either it is working like it is supposed to.

Hi @amirhussain,

I’m glad you’ve gotten this far! When you say:

Does that mean you’re using fly_postgres? Or are you taking a different approach?

Assuming you’re talking about fly_postgres, to verify:

  • verify that clustering is working
  • verify that you can read and write when connecting to the primary
    • use a Chrome extension like ModHeader to override the request headers.
    • set the header fly-prefer-region to your primary region, like mia
    • verify the app works (reads/writes)
  • verify that you can read and write when connecting to the secondary
    • set the header fly-prefer-region to a secondary region where your app is deployed, like lax
    • verify the app works (reads/writes)
    • review the README of fly_postgres on wrapping up complex operations to proxy them to the primary. See my presentation for examples of what I mean.
  • TIP: You can render the current region you are connected to on your a page of your app to prove to yourself that you are connecting to the machine you want. You can use System.get_env("FLY_REGION") or Fly.my_region() which does the same thing.
1 Like

@Mark I have successfully verified these parts but when i change fly-prefer-region to the secondary region it reads fine but write requests are not working and nothing pops up in the logs as well.

Assuming you’re talking about fly_postgres, to verify:

* verify that clustering is working
* verify that you can read and write when connecting to the primary
* use a Chrome extension like ModHeader to override the request headers.
* set the header fly-prefer-region to your primary region, like mia
* verify the app works (reads/writes)

@Mark , I checked the DB app logs and it seems like the secondary region write request is going to the read only part. What could be wrong here?

ERROR: cannot execute INSERT in a read-only transaction

I did this in runtime.exs for the port changing in the database url:

database_url =
    if is_nil(System.get_env("PRIMARY_REGION")) || is_nil(System.get_env("FLY_REGION")) ||
         System.get_env("PRIMARY_REGION") == System.get_env("FLY_REGION") do
      database_url
    else
      URI.parse(database_url) |> Map.put(:port, 5433) |> URI.to_string()
    end

Hi @amirhussain,

The library does the DB URL and port changes itself. This is not needed.

@Mark Wasn’t this mentioned in the docs?

so @Mark , if i remove this from runtime.exs. It will work fine?

Hi @amirhussain,

If you want to use roll your own solution in framework XYZ, then that guide provides general instructions for how to do it. If you want to use the fly-replay header to replay a POST request in the primary region, then that is helpful. This is helpful for stateless frameworks like PHP, Rails, etc where the applications can’t cluster together and communicate privately.

If you want to use the fly_postgres Elixir library specifically for an Elixir project, then you don’t need any of the code changes in the Multi-region PostgreSQL guide. The guide is still helpful for the Postgres cluster setup information.

1 Like

@Mark , It will be great if docs include elixir specific guide as well. Thank you so much

Good point. I added this section to the doc. Hopefully that can help others who follow a similar path.

Thanks!

1 Like