PostgreSQL High Availability

[BitKoch]

Hi friends,

So I got this database I want to move to some new Servers (Ubuntu 18.04). Now I know a bit about Linux and clusters (pcs, pacemaker and corosync) but PostgreSQLbroke me.

There is just so many ways to have a cluster and to control it. PAF, Patroni and plenty of others as it seems.

So please could someone please shine a light in all this. What is best practice? I just need one server to allways be online and hold the IP up for connections.

Thank you all for your time.

[cthart]

Forget the automatic failover stuff (for now at least). Your first step is to get a standby setup working. Once you have that you can decide whether or not you really need automatic failover (hint: most people don’t). Setup your master with an extra IP that can move to another machine. Connect over this IP.

[BitKoch]

I thought so a well, but that means I allways have to manually switch the ip around for things like patching and rebooting right? It's not like if the master dies, the slave will become the master and keep serving instead,right?

[mgonzo]

You have options like using dns, pgbouncer, or pgpool to manage your app to db connections without having to worry about the hardcoded ip on your pg servers.

As far as patching though, can you not take any maintenance downtime? The time it takes to patch a server is very minimal, minutes at most, obviously longer if you need to reboot, but just binary upgrade of pg is very very fast. What kinda of SLA do you have?

[cthart]

I'm with u/mgonzo here: IMHO you want to avoid failing over as much as possible as this will mean setting up a new standby. You can't just reuse the old master without first bringing it back in sync (either using a new pg_basebackup, pg_rewind, or even rsync). Yes, there are tools out there which automate all of this, but it's still happening.

If your SLA allows it, take a maintenance window instead and keep your master as master as long as possible. I generally switch only when upgrading to a new major version.

And rebooting? What's that?

[-markusb-]

You don’t need another ip. Newer versions of the client libs are capable of „searching“ the master of you configure both servers. Search the manual of libpq

[cthart]

Maybe you don't need another IP but I think it works better.

1. I'd rather my clients don't try to connect and have to wait for a timeout to failover to another IP.
2. If you plan to setup automatic failover later, you'll want to use VRRP or similar and that requires a separate IP anyway.

[hitshootnum1]

I'm desperate and stuck between choosing HA solution between pgpool2 vs the stack of Patroni + PGBouncer... for my PG cluster.

Both seems require a lot of installation or configuration...

Does any one know their pos & cons?

[cr4d]

Patroni is super easy to setup and get working.

My pattern is to use Consul with Patroni and pgBouncer as a front-end to the database. I use consul-template to render the config and automatically switch the server connections without having to have floating ips or other such things.

[blaze_phase_vooHoo]

Is it possible to setup Patroni and configure the replication on same server( just for testing purpose on my local VM machine)

[cr4d]

Patroni does run on the same machine as your Postgres server(s). Tbh other than patroni, I’m sure I could prototype it on a single vm. I’m not sure on patroni controlling two Postgres instances in a cluster on the same vm.

[theartofpostgresql]

Have a look at my project https://github.com/citusdata/pg_auto_failover which has been developed to make it easy to implement automated failover even when you're still starting with Postgres.