How to Enable PostgreSQL Replication on Ubuntu (Step-by-Step Guide)

PostgreSQL replication allows you to duplicate and synchronize database objects, including schemas, tables, and transactions, across multiple servers to improve performance, fault tolerance, and high availability. This article will guide you through the steps to set up and enable data replication for PostgreSQL on Ubuntu.

Types of PostgreSQL Data Replication

PostgreSQL data replication refers to the process of copying data from one PostgreSQL database to another, and keeping the data in these databases synchronized. This practice is often used in database management to enhance data security, increase availability, and distribute the workload across multiple systems.

Think of PostgreSQL data replication as maintaining a real-time backup—whenever data updates on the primary server, those changes reflect on the standby server automatically, ensuring continuous data consistency. Replication involves copying the entire database (full replication) or parts of the database (partial replication) to another system. When updates, inserts, or deletions occur in the primary PostgreSQL database, those modifications are instantly transferred to the standby system, keeping both databases in sync.

There are two main forms of PostgreSQL replication: physical replication and logical replication.

Streaming Replication

Streaming replication copies the primary database’s physical files and updates them on the standby server in real time, ensuring minimal data loss and faster failover. It’s a binary copy of the database cluster’s files, providing a hot standby system. Physical replication is straightforward and efficient, as it duplicates data exactly as is, but it lacks flexibility because you can’t replicate a subset of the database or transform data during the replication process.

Logical Replication

Logical replication lets you choose which tables to copy and apply filters, making it useful for syncing multiple databases, integrating data, or running analytics on selected records. It allows you to replicate specific tables and even transform data while replicating. This is achieved by sending the changes of the data (the write-ahead log or WAL) in a logical format that includes the data and the changes, rather than in a binary format.

Setting up PostgreSQL replication is critical for database administrators who want to enhance disaster recovery, distribute query loads for better performance, and simplify data migration between servers.

Let’s get started on how to setup replication on PostgreSQL step-by-step…

How To Set Up PostgreSQL Replication (Step-by-Step Guide)

Prerequisites

• Two Ubuntu 24.04 or newer servers: A primary server and a standby server.
• Ensure PostgreSQL is installed and running on both Ubuntu servers, with compatible versions for seamless replication.
• Sudo or root access to both servers.

Step 1: Configuring the Primary Server

1.1 Editing the PostgreSQL Configuration File

Open the PostgreSQL configuration file located at /etc/postgresql/{version}/main/postgresql.conf in a text editor:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Replace {version} with your PostgreSQL version.

Edit the configuration file to set up streaming replication by adjusting the write-ahead log (WAL) and connection parameters.

• wal_level – set this to replica.
• max_wal_senders – set this to the number of standby servers that you have.
• wal_keep_segments – set this to a higher value like 64. This represents the number of past log file segments kept in the pg_xlog directory.

wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

1.2 Editing the pg_hba.conf File

In the same directory, edit the pg_hba.conf file:

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

Add the following line at the end of the file. Replace {standby-ip-address} with the IP address of the standby server:

host replication all {standby-ip-address}/32 md5

1.3 Restart PostgreSQL

After editing the configuration files, restart the PostgreSQL service:

sudo systemctl restart postgresql

Step 2: Configuring the Standby Server

2.1 Stop the PostgreSQL Service

Before we can configure the standby server, we must stop the PostgreSQL service:

sudo systemctl stop postgresql

2.2 Copying Data from the Primary Server

Use the pg_basebackup utility to take a full snapshot of the primary PostgreSQL database and transfer it to the standby server while keeping replication logs intact.

sudo -u postgres pg_basebackup -h {primary-ip-address} -D /var/lib/postgresql/{version}/main -U replication -v -P --wal-method=stream

Replace {primary-ip-address} with your primary server’s IP and {version} with your PostgreSQL version to avoid version mismatch issues.

2.3 Creating the Recovery Configuration File

The standby server stays in recovery mode, automatically fetching missing WAL files from the primary database to keep the data in sync.

Create a file named standby.signal in the PostgreSQL data directory:

sudo touch /var/lib/postgresql/{version}/main/standby.signal

Then, create a postgresql.auto.conf file:

sudo nano /var/lib/postgresql/{version}/main/postgresql.auto.conf

Add the following lines:

primary_conninfo = 'host={primary-ip-address} user=replication password={your-password} application_name={standby-hostname}'

Replace {primary-ip-address} with the IP address of the primary server, {your-password} with the password you want to use, and {standby-hostname} with the hostname of the standby server.

2.4 Start the PostgreSQL Service

Finally, start the PostgreSQL service:

sudo systemctl start postgresql

To apply future PostgreSQL replication configuration changes without downtime, reload the service instead of restarting it.

When Should You Set Up PostgreSQL Replication?

PostgreSQL replication isn’t just for large enterprises—it’s essential for anyone who needs data redundancy, high availability, or workload distribution. Here are real-world scenarios where setting up replication makes a big difference:

1. Ensuring High Availability for Critical Applications

If your database supports business-critical applications like e-commerce platforms, banking systems, or SaaS products, downtime is not an option. Setting up streaming replication ensures that if the primary server fails, a standby server can take over instantly, minimizing service disruptions.

🔹 Example: A fintech company processing real-time transactions needs continuous database uptime. With replication, they can failover to a standby server without data loss.

2. Load Balancing for Read-Heavy Applications

If your application runs frequent read queries (e.g., reporting dashboards, analytics tools, or customer portals), a single database server may become a bottleneck. Replication allows read scaling by distributing queries across multiple standby servers, improving performance.

🔹 Example: A content-heavy website with millions of visitors uses read replicas to handle high traffic, ensuring fast page loads while keeping the primary database focused on writes.

3. Disaster Recovery and Data Protection

Without replication, a hardware failure, cyberattack, or accidental data deletion can lead to irreversible data loss. By maintaining a real-time standby replica, businesses can recover quickly without relying on outdated backups.

🔹 Example: A healthcare system storing patient records replicates data across different locations to prevent loss in case of a server crash or ransomware attack.

4. Seamless Database Migrations and Upgrades

When upgrading PostgreSQL versions or migrating to new infrastructure, downtime can be costly. Replication allows zero-downtime migrations by syncing the new server with the old one before switching over.

🔹 Example: A cloud service provider upgrades its PostgreSQL version by setting up a replicated instance on a new server, ensuring a smooth transition without service interruptions.

5. Multi-Region Deployment for Global Applications

If your application serves users worldwide, latency becomes a concern. Replication lets you deploy geographically distributed database servers, reducing response times and ensuring users access data from the nearest location.

🔹 Example: A global e-commerce platform replicates its database to different data centers, ensuring fast transactions for customers in North America, Europe, and Asia.

Do You Need PostgreSQL Replication?

If your application requires high availability, better performance, disaster recovery, or multi-region support, setting up replication is a smart move. For small-scale projects, it may not be necessary, but for mission-critical databases, it’s a must-have.

FAQs

Final Thoughts

Setting up PostgreSQL replication on Ubuntu ensures high availability, improves database performance, and strengthens disaster recovery strategies. Whether using streaming replication for real-time failover or logical replication for selective data syncing, a well-configured system minimizes downtime and enhances scalability. Regular monitoring and security best practices keep your replication setup stable and secure.