Asynchronous Replication in PostgreSQL: What It Is and How It Works

In PostgreSQL, asynchronous replication is a method where the primary server sends data changes to a standby server without waiting for confirmation. This means the primary continues working immediately, improving performance but risking slight data delay on the standby.

How It Works

Imagine you have a main office (primary server) and a backup office (standby server). When the main office finishes a task (a data change), it quickly tells the backup office about it but doesn't wait for the backup to confirm it received the message. This is like sending a quick text and moving on.

In PostgreSQL, asynchronous replication works similarly. The primary server sends data changes to the standby server but does not wait for the standby to write those changes to disk before continuing. This helps the primary server stay fast and responsive.

However, because the primary doesn't wait, the standby might be a little behind if it hasn't received or applied the latest changes yet. This is a trade-off between speed and data safety.

Example

This example shows how to set up asynchronous replication in PostgreSQL by configuring the primary and standby servers.

sql

/* On the primary server's postgresql.conf */
wal_level = replica
max_wal_senders = 3
wal_keep_size = 16MB

/* On the primary server's pg_hba.conf */
host replication replicator 192.168.1.100/32 md5

/* On the standby server's postgresql.auto.conf (PostgreSQL 12+) */
primary_conninfo = 'host=192.168.1.1 port=5432 user=replicator password=yourpassword'

/* Start the standby server */

Output

No direct output; this config enables asynchronous replication between primary and standby servers.

When to Use

Use asynchronous replication when you want to keep your primary server fast and responsive, and you can accept a small delay in data appearing on the standby server. It is ideal for:

• Disaster recovery setups where some delay is acceptable.
• Load balancing read queries on standby servers without slowing down writes on the primary.
• Environments where network latency might slow synchronous replication.

It is not recommended when you need zero data loss, as the standby might lag behind during failures.

Key Points

• Asynchronous replication sends changes without waiting for standby confirmation.
• It improves primary server performance by reducing wait times.
• There is a risk of data lag or loss if the primary fails before standby catches up.
• It is suitable for use cases where some delay is acceptable.

Key Takeaways

Asynchronous replication in PostgreSQL improves performance by not waiting for standby confirmation.

It can cause a delay in data appearing on the standby server.

Use it when some data lag is acceptable and fast primary response is important.

Configure it by setting wal_level to replica and connecting standby with primary_conninfo.

It is not suitable for zero data loss requirements.