Replication queue managers

At the other end of the spectrum, some replication software does statement-based replication. Rather than sending blocks over, the list of transactions made to each table is extracted from the database, typically with triggers. Then those statements can be saved to a queue, shipped to some number of slave nodes, and then executed there. This introduces some non-trivial overhead on the master server, because the overhead of the triggers, queue management, and statement shipping is moderate.

However, the resulting copies are then completely independent of the master server. And unlike WAL shipping approaches, you can pick and choose exactly which tables do and don't get shipped to the standby. In addition, since high-level statements are being shipped, the servers don't even need to match perfectly. This form of replication is therefore useful for doing PostgreSQL version upgrades. You can bring a server running a newer version of the database software up, add it as a replica, and let it stream the data from the master at whatever speed you want until it catches up. Once it's current, a standard failover to that new node will allow you to bring the database up and running newer version.

It's not unusual for developers to think that they can just ship statements between database servers themselves, rather than use one of these packages. One hidden complexity in statement level replication is coping with non-deterministic statements, where the exact result will vary if you execute the same thing on a different server. Three sources for such data are: calls to generate random numbers, sequence generation, and statements that incorporate a server timestamp. You can expect the latter in particular to haunt the poor developer who decides to reinvent this particular wheel, as such statements are very common in database applications. Getting statement order to match exactly is another extremely tricky problem. It's unlikely you'll do a better job in the end than these mature solutions already available. There are good reasons that all these replication packages end up being more complicated than you might think is required for this sort of work--they can't be any simpler and still work correctly.