Manage logical replication in Neon
Learn how to manage logical replication in Neon
This topic provides commands for managing publications, subscriptions, and replication slots. It also includes information about logical replication specific to Neon, including known limitations.
For step-by-step setup instructions, refer to our logical replication guides.
This section outlines how to manage publications in your replication setup.
Create a publication
This command creates a publication named
my_publication that will track changes made to the
This command creates a publication that publishes all changes in two tables:
This command creates a publication that only publishes
UPDATE operations. Delete operations will not be published.
Add a table to a publication
This command adds a table to a publication:
Remove a table from a publication
This command removes a table from a publication:
Remove a publication
This command removes a publication:
Recreate a publication
This command recreates a publication within a single transaction:
This section outlines how to manage subscriptions in your replication setup.
Create a subscription
Building on the
my_publication example in the preceding section, here’s how you can create a subscription:
A subscription requires a unique name, a database connection string, the name and password of your replication role, and the name of the publication that it subscribes to.
In the example above,
my_subscription is the name of the subscription that connects to a publication named
my_publication. In the example above, you would replace the connection details with your Neon database connection string, which you'll find in the Connection Details widget on the Neon Dashboard.
Create a subscription with two publications
This command creates a subscription that receives data from two publications:
A single subscriber can maintain multiple subscriptions, including multiple subscriptions to the same publisher.
Create a subscription to be enabled later
This command creates a subscription with
enabled = false so that you can enable the scription at a later time:
Change the publication subscribed to
This command modifies an existing subscription to set it to a different publication:
Change the subscription connection
This command updates the connection details for a subscription:
Disable a subscription
This command disables an existing subscription:
Drop a subscription
This command drops an existing subscription:
Replication slots are created on the publisher database to track replication progress, ensuring that no data in the WAL is purged before the subscriber has successfully replicated it. This mechanism serves to maintain data consistency and prevent data loss in cases of network interruption or subscriber downtime.
Create a replication slot
Replication slots are typically created automatically with new subscriptions, but they can be created manually using the
pg_create_logical_replication_slot function. Some "subscriber" data services and platforms require that you create a dedicated replication slot. This is accomplished using the following syntax:
The first value,
my_replication_slot is the name given to the replication slot. The second value is the decoder plugin the slot should use.
max_replication_slots configuration parameter defines the maximum number of replication slots that can be used to manage database replication connections. Each replication slot tracks changes in the publisher database to ensure that the connected subscriber stays up to date. You'll want a replication slot for each replication connection. For example, if you expect to have 10 separate subscribers replicating from your database, you would set
max_replication_slots to 10 to accommodate each connection.
max_replication_slots configuration parameter on Neon is set to
10 by default.
Remove a replication slot
To drop a logical replication slot that you created, you can use the
pg_drop_replication_slot() function. For example, if you've already created a replication slot named
pg_create_logical_replication_slot(), you can drop it by executing the following SQL command:
This command removes the specified replication slot (
my_replication_slot in this case) from your database. It's important to ensure that the replication slot is no longer in use or required before dropping it, as this action is irreversible and could affect replication processes relying on this slot.
Data Definition Language (DDL) operations
Logical replication in Postgres primarily handles Data Manipulation Language (DML) operations like
DELETE. However, it does not automatically replicate Data Definition Language (DDL) operations such as
ALTER TABLE, or
DROP TABLE. This means that schema changes in the publisher database are not directly replicated to the subscriber database.
Manual intervention is required to replicate DDL changes. This can be done by applying the DDL changes separately in both the publisher and subscriber databases or by using third-party tools that can handle DDL replication.
To ensure that your logical replication setup is running as expected, you should monitor replication processes regularly. The pg_stat_replication view displays information about each active replication connection to the publisher.
It provides details like the state of the replication, the last received WAL location, sent location, write location, and the delay between the publisher and subscriber.
Additionally, the pg_replication_slots view shows information about the current replication slots on the publisher, including their size.
It's important to keep an eye on replication lag, which indicates how far behind the subscriber is from the publisher. A significant replication lag could mean that the subscriber isn't receiving updates in a timely manner, which could lead to data inconsistencies.
This section outlines information about logical replication specific to Neon, including known limitations.
Enabling logical replication in Neon
Once you enable logical replication in Neon, the setting cannot be reverted. Enabling logical replication also restarts all computes in your Neon project, meaning that active connections will be dropped and have to reconnect.
In Neon, logical replication is enabled from the console, by following these steps:
- Select your project in the Neon console.
- On the Neon Dashboard, select Project settings.
- Select Replication.
- Click Enable.
You can verify that logical replication is enabled by running the following query:
Logical replication and autosuspend
By default, Neon's Autosuspend feature suspends a compute after 300 seconds (5 minutes) of inactivity. In a logical replication setup, Neon does not autosuspend a compute instance that has an active connection from a logical replication subscriber. In other words, a compute instance with an active subscriber remains active at all times.
It is recommended that you create a dedicated Postgres role for replicating data. The role must have the
REPLICATION privilege. The default Postgres role created with your Neon project and roles created using the Neon Console, CLI, or API are granted membership in the neon_superuser role, which has the required
REPLICATION privilege. Roles created via SQL do not have this privilege, and the
REPLICATION privilege cannot be granted.
You can verify that your role has the
REPLICATION privilege by running the follow query:
A subscriber must be able to access the Neon database that is acting as a publisher. In Neon, no action is required unless you use Neon's IP Allow feature to limit IP addresses that can connect to Neon.
If you use Neon's IP Allow feature:
- Determine the IP address or addresses of the subscriber.
- In your Neon project, add the IPs to your IP Allow list, which you can find in your project's settings. For instructions, see Configure IP Allow.
Neon supports both
wal2json replication output decoder plugins.
pgoutput: This is the default logical replication output plugin for Postgres. Specifically, it's part of the Postgres built-in logical replication system, designed to read changes from the database's write-ahead log (WAL) and output them in a format suitable for logical replication.
wal2json: This is also a logical replication output plugin for Postgres, but it differs from
pgoutputin that it converts WAL data into
JSONformat. This makes it useful for integrating Postgres with systems and applications that work with
JSONdata. For usage information, see wal2json.
Dedicated replication slots
Some data services and platforms require dedicated replication slots. You can create a dedicated replication slot using the standard PostgreSQL syntax. As mentioned above, Neon supports both
wal2json replication output decoder plugins.
max_replication_slots configuration parameter settings on Neon are set to
max_wal_sendersparameter defines the maximum number of concurrent WAL sender processes that are responsible for streaming WAL data to subscribers. In most cases, you should have one WAL sender process for each subscriber or replication slot to ensure efficient and consistent data replication.
max_replication_slotsdefines the maximum number of replication slots which are used to manage database replication connections. Each replication slot tracks changes in the publisher database to ensure that the connected subscriber stays up to date. You'll want a replication slot for each replication connection. For example, if you expect to have 10 separate subscribers replicating from your database, you would set
max_replication_slotsto 10 to accommodate each connection.
If you require different values for these parameters, please contact Neon support.
Unused replication slots
Neon removes unused replication slots after a period of time to avoid unnecessary retention of write-ahead logs, which prevents removing data snapshots that are no longer required.
Neon is working toward removing the following limitations in future releases:
- Only your default Neon Postgres role and roles created via the Neon Console, CLI, or API have the
REPLICATIONprivilege. This privilege cannot be granted to other roles. You can expect this limitation to be lifted in a future release. Roles created via SQL do not have the
REPLICATIONprivilege, and this privilege cannot be granted.
max_slot_wal_keep_sizeis set to 1 GB, limiting the maximum size of WAL files that replication slots are allowed to retain in the
pg_waldirectory. This is a temporary limit that will be removed in a future release. The limit avoids an accumulation of WAL data at the publisher due to a lagging subscriber, which could cause a slow compute start.
Last updated on