PostgreSQL Replication and Ignition Redundancy

Ignition
1

Hello,

I am the database developer setting up replication for PostgreSQL 9.6. Our project is also using Ignition with Redundancy setup. Has anyone used this setup with redundancy setup on both Ignition and PostgreSQL separately? Are there any impact to the PostgreSQL replication of which I should be aware?

thanks

2

Have you ever set this up?
I’m on the same path… and I’m struggling with it… :sweat:

3

I haven’t done this myself, but I use PostgreSQL a lot, so I’ve studied it a bit. The key is that PostgreSQL doesn’t have any master-master mode, and cannot just swap master and slave roles. I would set up (at least) three servers: master, slave #1 (to master), and slave #2 (to slave #1).
Failure recovery would be as follows:

  • Slave #2 fails: repair or replace, re-sync with Slave #1.
  • Slave #1 fails: promote Slave #2 to slave of master. Repair or replace original slave #1 to be slave #2. If repaired, rewind it to last good checkpoint before resync.
  • Master fails: promote Slave #1 to master. Slave #2 becomes Slave #1. Repair or replace original master to be slave #2. If repaired, rewind as above before resync.

Consider using synchronous replication from master to slave #1, ensuring transactions commit in two servers before returning success to the client. You can load balance by allowing Select queries in one or both slaves.

4

:slight_smile::slight_smile:
Now I know why I’m struggling…

@pturmel
I’m not so good in PostgreSQL and I don’t understand half of what you just wrote… :blush:

So, it’s not so simple to setup PostgreSQL redundancy like for Ignition…

I’m testing two PostgreSQL servers in VM and I don’t know how to switch Ignition from one to another when one ‘fails’.
And back, when one server returns… :unamused: :

5

OK, I’ve done some reading and testing about PostgreSQL and streaming replications, hot-standby,…
I’ve successfully setup two VM’s with Postgres on each and setup master and slave. Syncing between them works and when the master goes down, I can promote the slave to master with trigger file.
On third VM I have Ignition gateway.

But, I still fail to understand, how to tell Ignition to use second Postgres (which is now master), when master Postgres fails?

I have master Postgres with IP: 192.168.123.210 and slave Postgres with IP: 192.168.123.211.

In Ignition database connection setting you specify Connect URL as:
jdbc:postgresql://192.168.123.210:5432/ignition

When master Postgres fails and slave is promoted to master, the IP for Postgres server is different:
jdbc:postgresql://192.168.123.211:5432/ignition

Of course, I’m looking for an automated way for Ignition to switch between master and slave databases.
But maybe I’m wrong… and all this must be done manually… :unamused:

6

For redundant history, there are these solutions:

  • tag history splitter (data are sent in 2 DB or more if you cascade splitter)
  • mysql 5.7.18 with group replication. The mysql router manage the connection to the master node.
    But you need at least 3 DB nodes. https://dev.mysql.com/downloads/
    Not tested but seems to be promising
7

Video tutorials to setup mysql Innodb cluster

8

@mazeyrat
Thank you for your info about MySQL, but we are using PostgreSQL and I can’t change that…

After some more searching and testing, I came to conclusion, that there is no ‘easy/simple’ automated switch over to backup database in case of primary database failure. I’m talking here about simple systems, not some big corporate solutions.

So one solution is: streaming replication to standby PostgreSQL server; when the primary server fails, manually promote standby server to primary and manually point Ignition to ‘new’ primary Postgres server (new IP).

Second: tag history splitter; I must test this more, but it seems simpler than streaming replication…

I would really like to know how you guys out there doing this (with PostgreSQL)?
Especially when using redundant Ignition gateways?

9

Hey @zxcslo

Sorry I do not have any first-hand experience with Postgres and Ignition. However, you might have more fruitful support in a Postgres specific forum (rather than Ignition forum).

I was already doing some research when I came across this article re setting up master-slave db’s in Postgres.

It’s a bit dated from 2013 and it’s specific to Ubuntu but might still be helpful:
https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps

Cheers,
Ryan

10

I’m resurrecting this for a bit…

Anyone take a look at Postgres-XL?

I don’t know if I’d jump on it or not. It’s hard to find a balance between data integrity / availability, and how much management wants to spend to achieve it.