In recent years, streaming replication has been one of the major features introduced in PostgreSQL. The idea is to use the PostgreSQL transaction log (WAL) to synchronize an arbitrary number of services and to replicate data inside a cluster.In recent years, streaming replication has been one of the major features introduced in PostgreSQL. The idea is to use the PostgreSQL transaction log (WAL) to synchronize an arbitrary number of services and to replicate data inside a cluster. In PostgreSQL replication can be made in various ways:

  • Synchronous
  • Asynchronous

You can decide on which method is more beneficial to you according to your needs.

Asynchronous replication in PostgreSQL

In the event of server failure and disaster, it is a good idea to have a standby / replica of your master server within reach. Fortunately, PostgreSQL offers the means to achieve exactly that. Administrators can create read-only replicas of a master server easily and use those replicas for various purposes such as:

  • Continuous backups
  • Automatic Failovers
  • Scaling out read-only workloads
  • Achieving geo-redundancies

Asynchronous is the standard way to replicate data in the PostgreSQL world and offers a reliable and easy way to distribute data and make your setups more failsafe.

The main advantages of asynchronous replication are a low overhead, simplicity, and robustness. As a result, asynchronous replication is the ideal solution for automatic failovers and enterprise-grade redundancies.

How asynchronous replication works

If you are running asynchronous replication, data might hit the slave AFTER a transaction has been committed on the primary server (master). There is usually a small replication delay, which can cause (usually) minor data loss in the case of a crash.

PostgreSQL Replication Asynchronous

In most cases this is totally acceptable because asynchronous replication promises little overhead and does not slow down the master.

Cascaded replication

In PostgreSQL, replication is not only possible from a master to a single slave, it is also possible to replicate from a single master to multiple slaves or to use slaves to replicate to even more slaves (cascaded replication). Cascading is especially useful if you are looking for a geographically distributed PostgreSQL replication solution.

Consider the following example:

Your main database server is based in New York, USA. You want to create replicas in Frankfurt, Stuttgart, Berlin and Aachen (Germany). If all your replicas are directly attached to the New York master server, data have to be sent across the Atlantic Ocean four times. Cascaded replication is a good alternative. You could attach the Frankfurt server to the master in the USA and dispatch data inside Germany from the Frankfurt server.

Synchronous replication in PostgreSQL

If you are not able to take the risk of losing a single COMMIT, synchronous replication might be what you are looking for. In PostgreSQL, you can replicate synchronously to as many slaves as you want to ensure that a COMMIT is only valid once it has been confirmed by the desired number of PostgreSQL servers.

Synchronous replication ensures the highest possible security for your transactions because a single crashing server can no longer cause data loss.

How synchronous replication works

It ensures that no data can be lost. Here is how it works.

PostgreSQL Replication Synchronous

A transaction can return only if a sufficient number of slaves have confirmed the write.

Advanced features: Quorum COMMIT

With the introduction of PostgreSQL 10.0, even more sophisticated COMMIT methods are supported. One of the most noteworthy features is the ability to do “quorum COMMITs”.
FIRST num_sync (standby_name [, …]) ANY num_sync (standby_name [, …])
The idea is to give developers and administrators a more fine-grained way to configure replication.

Professional help

Contact us today to receive your personal offer from Cybertec. We offer timely delivery, professional handling, and over 17 years of PostgreSQL experience.

Contact us