Replication

Replication allows data from one database server to be replicated to another server. Replication is used mainly to achieve the following:

• High availability: A second server can take over if the primary server fails.
• Load balancing: Several servers can serve the same requests.
• Fast execution: PostgreSQL is shipped with the tools to execute the queries efficiently, such as several kind of indexes and smart planner. In addition, the read load can be distributed on the replicas in streaming replication. 

PostgreSQL supports replication out of the box via streaming and logical replication. Streaming replication is a master-standby replication that uses file-based log shipping. Streaming replication is a binary replication technique, because SQL statements aren't analyzed. It's based on taking a snapshot of the master node, and then shipping the changes—the WAL files—from the master node to the slave node and replaying them on the slave. The master can be used for read/write operations, and the slave can be used to serve read requests. Streaming replication is relatively easy to set up and configure; it can support synchronous and asynchronous replications as well as cascading replication.

A synchronous replication is the default streaming-replication mode. If the primary server crashes, transactions that were committed on the primary may not have been replicated to the standby server, leading to data inconsistency. In synchronous replication, a data-modifying operation must be committed on one or more synchronous servers in order to be considered successful. In cascading replication, one could add a replica to a slave. This allows PostgreSQL to scale horizontally for read operations.

PostgreSQL also supports logical streaming replication; unlike streaming replication, which replicates the binary data bit by bit, logical replication translates the WAL files back to logical changes. This gives us the freedom to have more control over the replication such as applying filters. For example, in logical replication, parts of data can be replicated, while in streaming replication the slave is a clone of the master. Another important aspect of logical replication is being able to write on the replicated server, such as extra indexes, as well as temporary tables. Finally, you can replicate data from several servers and combine it on a single server.

In addition to PostgreSQL replication techniques, there are several other open source solutions to target different workloads:

• Slony-1: This is a master-to-multiple-slave replication system. Unlike PostgreSQL, it can be used with different server versions. So, one could replicate the 9.6 server data to the 11 server. Slony is very useful for upgrading servers without downtime.
• Bucardo: An open source project for an asynchronous replication system that allows multi-master as well as multi-slave operations.
• pgpool-II: This is the middleware between PostgreSQL and the client. In addition to replication, it can be used for connection pooling, load balancing, and parallel query execution.
• HA management tools: Patroni, stolon, and repmgr are a set of management tools for HA for PostgreSQL. repmgr manages the replication and failover of PostgreSQL. stolon is a cloud-native PostgreSQL manager. Patroni is a template for PostgreSQL HA with ZooKeeper, etcd, or Consul.
• Distributed Replicated Block Device (DRBD): A general solution for HA. It can be understood as a RAID-1 network. This solution requires advanced knowledge and may not be straightforward to set up.