CYBERTEC PostgreSQL Logo

Synchronous and asynchronous replication

What is Synchronous and asynchronous replication

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 two ways:

  • synchronous and
  • asynchronous replication

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 primary server within reach. Fortunately, PostgreSQL offers the means to achieve exactly that. Administrators can create read-only replicas of a primary 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 standby AFTER a transaction has been committed on the primary server. There is usually a small replication delay, which can cause (usually) minor data loss in the case of a crash.

Asynchronous Replication

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

Cascaded replication

In PostgreSQL, replication is not only possible from a primary to a single standby, it is also possible to replicate from a single primary to multiple standbys or to use standbys to replicate to even more standbys (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 primary 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 primary 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 standbys 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.

Synchronous Replication

A transaction can return only if a sufficient number of standbys 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.

Setting up PostgreSQL Streaming Replication

If you are looking to set up streaming replication for PostgreSQL 13, this is the tutorial you have been looking for. We will show you how to configure PostgreSQL replication and how to set up your database servers quickly.

Streaming Replication

Professional help

You want to know more about synchronous and asynchronous replication? Contact us today to receive your personal offer from CYBERTEC. We offer timely delivery, professional handling, and over 20 years of PostgreSQL experience.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram