How to set up logical replication in PostgreSQL 16

Configure a publisher and subscriber to replicate data changes using publication and subscription objects in PostgreSQL 16. These steps target PostgreSQL 16.x on Linux systems with the default configuration.

Prerequisites

• Two PostgreSQL 16 instances (local or remote).
• Network connectivity between the publisher and subscriber ports (default 5432).
• System user with sudo privileges to restart services or manage files.
• Access to the pg_hba.conf and postgresql.conf files on both servers.
• The pgoutput protocol must be enabled in postgresql.conf on the publisher.

Step 1: Configure the publisher

Edit the postgresql.conf file on the publisher server to enable the pgoutput protocol. This setting allows other servers to subscribe to changes made in the database.

listen_addresses = '*'
wal_level = replica
max_replication_slots = 10
max_wal_senders = 10
wal_keep_size = 64MB

Restart the PostgreSQL service to apply these changes. Run the following command to restart the service on Ubuntu or Debian systems.

sudo systemctl restart postgresql

Ensure the pg_hba.conf file allows connections from the subscriber's IP address. Add a line at the end of the file to permit the subscriber to connect to the publisher.

host    replication     repuser     192.168.1.0/24            md5

Restart the service again to load the new authentication rules.

sudo systemctl restart postgresql

Step 2: Create a publication on the publisher

Connect to the publisher database using psql and create a publication. A publication defines which tables and columns will be replicated. Use the CREATE PUBLICATION command to define the scope.

CREATE PUBLICATION my_pub FOR TABLE employees, departments;

Verify that the publication was created successfully by querying the pg_publication system catalog.

\l+

The output should list my_pub with a status of enabled.

 List of publications
   Name   | Owner | Enabled | Pubtablespace | Pubalgorithm | Puballtables | Pubinsert | Pubupdate | Pubdelete | Pubtruncate | Pubslotname | Pubslottype
-----------+-------+---------+---------------+--------------+--------------+-----------+-----------+-----------+-------------+-------------+-------------
 my_pub   | pg    | on      |               | logical      | f            | t         | t         | t         | f           |             |
(1 row)

Step 3: Configure the subscriber

Edit the postgresql.conf file on the subscriber server. Ensure that listen_addresses is set to * or the specific IP address of the publisher. Restart the PostgreSQL service to apply the changes.

sudo systemctl restart postgresql

Edit the pg_hba.conf file on the subscriber to allow connections from the publisher. Add a line to permit the publisher's IP address to connect to the replication slot.

host    replication     pubuser     192.168.1.0/24            md5

Restart the service to load the new authentication rules.

sudo systemctl restart postgresql

Step 4: Create a subscription on the subscriber

Connect to the subscriber database using psql and create a subscription. A subscription connects the subscriber to the publisher and begins replicating data. Use the CREATE SUBSCRIPTION command with the connection parameter pointing to the publisher.

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher_ip dbname=postgres user=repuser password=rep_password'
  PUBLICATION my_pub;

Verify that the subscription was created successfully by querying the pg_subscription system catalog.

\l+

The output should list my_sub with a status of enabled.

 List of subscriptions
   Name   | Owner | Enabled | Subscribedb | Subconninfo | Subslotname | Subslottype | Subenabled
-----------+-------+---------+-------------+-------------+-------------+-------------+------------
 my_sub   | pg    | on      | postgres    | host=...    |             |             |
(1 row)

Verify the installation

Insert a new row into the employees table on the publisher and check if it appears on the subscriber. This confirms that logical replication is functioning correctly.

-- On the publisher
INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'Engineering');

Query the employees table on the subscriber to see the new row.

-- On the subscriber
SELECT * FROM employees;

The output should show the new row with id 1.

 id | name  | department
----+-------+------------
  1 | Alice | Engineering
(1 row)

Troubleshooting

If the subscription fails to start, check the pg_subscription_rel table for errors. A common error is subscription "my_sub" is not enabled. This happens if the pg_subscription system catalog entry is missing or the enabled flag is false.

\d+ pg_subscription

Ensure that the pg_hba.conf file on the publisher allows the subscriber to connect. If the connection is rejected, verify the pg_hba.conf file and restart the service.

sudo systemctl restart postgresql

If the subscriber cannot connect to the publisher, check the network connectivity and firewall settings. Ensure that port 5432 is open between the two servers.

telnet publisher_ip 5432

If the pgoutput protocol is not enabled, the subscription will fail. Verify that wal_level is set to replica and pgoutput is set to on in postgresql.conf.

SHOW wal_level;
SHOW pgoutput;

If the subscription is enabled but data is not replicating, check the pg_replication_slots system catalog. Ensure that the slot is active and not in an error state.

\d+ pg_replication_slots

Restart the subscriber service to reset the replication state if necessary.

sudo systemctl restart postgresql

Finally, ensure that the pg_stat_subscription view shows no errors. Query the view to see the current status of the subscription.

SELECT * FROM pg_stat_subscription;

Review the output for any warnings or errors. Fix the underlying issue and restart the service to resume replication.