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
• Faster execution: A query is executed on several machines at once to gain performance

PostgreSQL supports replication out of the box via streaming replication. Streaming replication is a master-slave replication that uses file-based log shipping. Streaming replication is a binary replication technique, because SQL statements are not analyzed. It is 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. In synchronous replication, a data modifying operation must be committed on all 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.

In PostgreSQL 10, additional replication technique is added, which is logical replication; unlike streaming replication, which replicates the binary data bit by bit, logical replication translate 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, one 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 systems. Unlike PostgreSQL, it can be used with different server versions. So, one could replicate the 9.6 server data to the 10 server. Slony is very useful for upgrading servers without downtime.
• pgpool-II: This is middleware between PostgreSQL and the client. In addition to replication, it can be used for connection pooling, load balancing, and parallel query execution.
• Distributed Replicated Block Device (DRBD): A general solution for HA. It can be understood as a network RAID-1.