PostgreSQL HA with Repmgr

8 months ago

Description: Instant Postgres cluster for high availability and redundancy

Category: Storage

URL: https://railway.app/template/ha-postgres

18 Replies

alphalinkPRO

8 months ago

@melissa Thank you for the template. WDYT about adding optional support of readyset to this template?


8 months ago

Hey! Not opposed to adding things that will make the template better. I'm not familiar with readyset, but I know we have a template for it: https://railway.app/template/wr1OLZ

Looks like it would be an extra service? Would it replace pgpool or would it sit between pgpool and the nodes? I can make a ticket to research a bit, but any info you can share would be helpful to scope it out!


genai_worksPRO

7 months ago

Seems not to be working with Strapi, but it's probably not template issue. Strapi can't connect to the pool service


7 months ago

Hmmm thanks for letting me know @genai_works. I created a ticket to look into this when I have some time. Would love to know any helpful details you have, like error(s), etc.


7 months ago

Hi @genai_works, I was able to connect Strapi to the pgpool service without issue. If you've deployed Strapi from the template, you should simply update the variables in the Strapi service to reference the pgpool service, e.g.

DATABASE_PRIVATE_URL="${{pgpool.DATABASE_URL}}"

DATABASE_URL="${{pgpool.DATABASE_PUBLIC_URL}}"

Ideally, you should have all services deployed in the same project. Let me know if this helps


rgmvisserBIZCLASS

6 months ago

Nit: "Join the discussion" link at the bottom of this page https://docs.railway.app/guides/postgresql#high-availability-postgresql-cluster is broken.


rgmvisserBIZCLASS

6 months ago

I created a new thread (https://help.railway.app/questions/migration-docs-for-high-availability-dat-9c6e3d51), but maybe better here:

I was looking at the docs https://docs.railway.app/guides/postgresql#high-availability-postgresql-cluster which are great! A really useful addition to this would be if the team can add migration steps. We are already running a postgres + redis instance and would love to move to a more highly available setup.


isaac-hinmanPRO

6 months ago

Hi there. Fantastic that this is now available. From the docs:

By default, each node is deployed to a different region (US West, US East, and EU West) for fault tolerance.

How (if at all) does this relate to multi-region deployments? If we deploy, for example, instances of our backend services across multi regions, will they automatically read from the closest node?


6 months ago

Hey @isaac-hinman, this is a great callout and something we discussed yesterday. The multi-regional deployment, in it's current state, is mostly for redundancy in the case that a region goes down. I would love to explore how to squeeze performance out of it though, by routing the reads to the region nearest the requestor. I haven't ventured into the docs for how to do this yet, but if you're interested, I'm happy to prioritize it over the next couple of weeks.


6 months ago

@rgmvisser, I addressed your question in the other thread but will paste here for others as well:

We have this for Postgres right now -https://railway.app/template/VgqHWg (thanks for the reminder to mention it in docs, will do that now). It's a service that you can deploy which will do the migration for you. You can check out the script in the source if you'd prefer to do it manually. The script can also, optionally, enable replication between your standalone and the cluster, assuming you'll want to test before cutting over.

We paused building these migration tools to gauge interest, but we can pick up and see about doing the same for the rest. You can probably adapt the old plugin migration template for Redis (https://railway.app/template/redis-plugin-migration) if you're looking to get started ASAP.

Let me know when you intend to pick up this work, and I'd really love to help out. I expect that we'll learn a lot and identify opportunities to improve these templates.


isaac-hinmanPRO

6 months ago

Thanks @melissa. Yes, routing reads across regions would be a game changer, and would lead me to seriously consider Railway for larger/global scale projects/products. Of course, we'd also need some form of load-balancing/routing for API/HTTP layer as well, not just pg reads.


6 months ago

For sure, and thanks for that context, regional load-balancing is something we very much want to implement, and I suspect it's increasing in priority as we close out our big reliability projects and some other things like the proxy rollout and metal.

In the meantime, I'll start looking into the pg reads at least and see if there is anything we can do there to get ahead for when we do pick up load-balancing.


isaac-hinmanPRO

6 months ago

Multi regional load balancing Railway deploys with a few clicks is something that gets me quite excited!


melissa

For sure, and thanks for that context, regional load-balancing is something we very much want to implement, and I suspect it's increasing in priority as we close out our big reliability projects and some other things like the proxy rollout and metal.In the meantime, I'll start looking into the pg reads at least and see if there is anything we can do there to get ahead for when we do pick up load-balancing.

2 months ago

This sounds amazing. Is there any progress on this?


bmcdxyzPRO

2 months ago

Seems that a default deployment allows you to connect to the pgpool instance as the postgres user, but without requiring a password. Anyone else notice this? I would have expected it to honor the environment variable PGPOOL_POSTGRES_PASSWORD, but appears it isn't.


2 months ago

Thanks for the report, we will be looking into that.


brody

Thanks for the report, we will be looking into that.

bmcdxyzPRO

a month ago

Thanks. Shall I raise a support request for this too?


genai_worksPRO

a month ago

Hello,
Is it easy to add more postgresql nodes to this template? Can I do it myself?

Should I just duplicate, and adjust some variables?

P.S. Seems I know understand how it can be configured - just duplicate node + adjust varialbes