Deploying ParadeDB as a logical replication worker is a ParadeDB
Enterprise feature. This guide assumes you are using a
ParadeDB Enterprise build.
In order for ParadeDB to run as a logical replica, ParadeDB must be using
Postgres 17+.
AWS RDS/Aurora
Please follow AWS’ tutorial for configuring logical replication. In this tutorial, AWS RDS/Aurora is the publisher and ParadeDB is the subscriber.Azure Database
Please follow Azure’s tutorial for configuring logical replication. In this tutorial, Azure Database is the publisher and ParadeDB is the subscriber.Azure Cosmos DB for PostgreSQL does not support logical
replication.
GCP CloudSQL for PostgreSQL
Please follow Google’s tutorial for configuring logical replication. In this tutorial, GCP CloudSQL for PostgreSQL is the publisher and ParadeDB is the subscriber.AlloyDB for PostgreSQL
Please follow Google’s tutorial for configuring logical replication. In this tutorial, AlloyDB is the publisher and ParadeDB is the subscriber.Self-Hosted Postgres
This guide demonstrates how to configure replication between a self-hosted primary Postgres and ParadeDB.Environment Setup
We’ll use the following environment setup for our demonstration: Primary Server- OS: Ubuntu 24.04
- IP: 192.168.0.30
- Database Name:
marketplace
- Replication User:
replicator
- Replication Password:
passw0rd
- OS: Ubuntu 24.04
- IP:
192.168.0.31
,192.168.0.32
Primary Server Setup
1. Edit postgresql.conf
The postgresql.conf
file is the main configuration file for PostgreSQL. It contains all the server settings that control the behavior and performance of your PostgreSQL instance.
Ensure that your postgresql.conf
has the following settings applied:
listen_addresses
specifies the IP addresses on which PostgreSQL listens for connections. By default, PostgreSQL only listens onlocalhost
. To allow other servers (like your standby servers) to connect for replication, you need to include their IP addresses.max_wal_senders
determines the maximum number of concurrent connections that can send WAL (Write-Ahead Log) data.
pg_search
on the primary server, make sure to add it to shared_preload_libraries
if your Postgres version is less than 17. If you are installing it only on the standby server as a search replica,
you should skip this step.
2. Edit pg_hba.conf
The pg_hba.conf
file (PostgreSQL Host-Based Authentication file) controls client authentication, defining who can connect to the PostgreSQL server, how they can connect, and from where. It ensures that only authorized users and systems can access the database.
We need to allow the replication user to connect from the standby servers’ IP addresses. Add the following lines to allow replication from the local network and localhost. This configuration ensures that the replication user can connect from the specified IP range.
3. Create the Replication User
In PostgreSQL, a user is required to handle replication connections securely. This user needs specific permissions to read the WAL and perform replication tasks. Creating a dedicated replication user ensures that the replication process is secure and isolated from other database operations. This user will have the necessary permissions to perform replication but will not have broader administrative privileges.--pwprompt
prompts you to enter a password for the new user. In this tutorial, we will be setting the password to passw0rd
.
--replication
grants the replication privilege to the new user, allowing it to handle replication tasks.
4. Create Mock Table on Primary
Create a database:pg_search
on the primary, you can build a BM25 index over the table.
Standby Server Setup
Standby servers do not have any specific configuration necessary other than the tables in which you are ingesting logical replication must start off empty. Configuring replication consists of two steps:- Running pg_basebackup, which will create a snapshot of your primary onto your standby servers. This makes the initial replication much faster.
- Subscribing via logical replication, which will create a subscription that continuously propagates changes on the primary server to the standby.
1. Run pg_basebackup
You should runpg_basebackup
on your standby server to create a physical byte-for-byte replica of your primary cluster. The --pgdata
directory specifies the where the standby cluster will be created. The directory must exist, and must be empty.
2. Start PostgreSQL on Standby Server
Start PostgreSQL on standby servers:3. Verify Replication
Now you can verify the replication. The data inserted on the primary server should appear on the standby servers, confirming that streaming replication with ParadeDB is working correctly.Logical Replication Setup
Logical replication allows you to replicate changes to specific tables between PostgreSQL instances. After creating a physical replica withpg_basebackup
, you can set up a subscription to propagate changes on the primary server to the standby.