Postgresql – how long can your replica be offline when using repmgr with Barman

Ok, while the question(s) is(are) rather complex, the answer(s) is(are) pretty simple, albeit a bit lengthy.

The replication (simplified)

When an operation is saved in the oplog, it does not get distributed to the other members. It gets pulled by the replica set members.

A failed member rejoining the replica set will contact the primary and pull the oplog entries it hasn't processed. So it will eventually become consistent.

However, there is a caveat. For performance reasons, the oplog is a capped collection. This means there is limited space available for it. With limited space, you have a limited number of operations which can be written to the oplog. And limited operations reach back only a limited amount of time, since the oldest entries of the oplog will be overwritten when a new entry would exceed the oplogs size limit. The limited time frame is called the replication oplog window.

If member rejoins the cluster and its last operation is older than the oldest entry in the primaries oplog, it becomes stale and needs to have an complete resync of the data.

So in your example, after 3,S went down, 2,S still would happily pull the oplog. In your example, all three are up for a short amount of time, until 2,S failed. The election now would take place, and since there is no need to change the primary 1,P would still serve in this function.

Now, when 3,S came up within the oplog window, it would simply connect to the primary, pull the oplog, apply the oplog entries to its data set and all lived happily ever after (except for the admin who has to get his back moving to get up 2,S again for double redundancy).

In case 3,S comes up outside the oplog window, you'd have to initialize a full resync by

1. Shutting down the instance of mongod
2. Wipe its dbpath
3. Restart the instance

Note that if 2,S isn't up and resynced during that procedure, the cluster wouldn't have a majority up and running and hence be unwritable.

Acknowledgement of operations

With a write concern of {w:1}, the primary would acknowledge the query to be

1. Received and
2. syntactically correct since it is
3. Applied to the in memory data set of both the data and the oplog

With a write concern of {w:1,j:1}, all of the above applies. Additionally, modifications are written to the journal, making the data guaranteed to be durable - except for the edge case of the journal being damaged.

Keep in mind that operations are written to the journal every commitIntervalMs, 100msecs by default, one third of the configured value with a write concern of {"w":x, "j":1}. We will come back to this later.

With the replication process as detailed above, it is sufficient that the {"w":x, "j":1} for x > 1 works different, since the primary is then guaranteed to have the latest data of all replica set members: With said write concern, the write to the journal is only acknowledged for the primary.

The replica set is either writable from the clients point of view (the majority of non-hidden, non-delayed members is up) or not. If it is writeable, the primary is guaranteed to have the latest data, and secondaries failing back will either automatically resync from the oplog or need to be fully resynced.

Edge case for {w:1}

There is a special condition, however. With a write concern of {w:1} it can happen that a primary about to fail accepted writes which did not make it to the secondaries. When this this machine rejoins the replica set, it will notice that its newest write operation is newer than the oldest of the new primaries oplog it hasn't processed. The instance will then identify all write operations it has accepted but didn't make it to the secondaries, writing them to the rollback folder in dbpath, up to a limit of 300MB.

Dealing with rollbacks can be quite tricky, however, it is easy to prevent: set w > 1.

Rule of thumb: Never set w < 2 unless you can afford to loose data

Conclusion

With a write concern of w<1, you prevent rollbacks and can be pretty sure that a write operation is durable. It only would not be durable if all replica set members the data was supposed to be processed by failed within a time frame of commitIntervalMs, so (for the default values) 100ms in a worst case scenario (all journals are written at the exact same point in time), 50ms in a best case scenario (the journals are shifted by exactly 50ms), giving us an average of 75ms in which all affected replica set members had to fail. If that is too much for you, either add more replica set members, lowering the probability that all servers supposed to process the operations fail, or set j:1. Still, this applies only if w replica set members fail simultaneously. With a geographically distributed replica set, the probability for this scenario equals 0 for all practical purposes. And in case of the Zombie Apocalypse happening simultaneously with an invasion by little green aliens, I assume your concerns (no pun intended) will shift.

If write operations are extremely important to be written to disk when acknowledged, you still can take the sledgehammer of forcing a file sync before you consider a write operation to be successful.

You can follow your original strategy with a minor tweak: add Google Compute (GC) nodes into the current replica set and keep them in sync until ready to cut over. The only thing you need to change is to set those new GC nodes to have priority 0. They will be full members of the set, they just cannot be elected primary (or trigger elections).

Once you are ready to switch over to the Google nodes, you need only reset the priority to 1 (or whatever your normal values are), and then step down or remove the AWS nodes from the set. You can guarantee that a GC node is elected primary by giving them a higher priority than the AWS nodes before issuing the step down.