Replicate data to an external Postgres instance
Learn how to replicate data from Neon to an external Postgres instance
Neon's logical replication feature allows you to replicate data from Neon to external subscribers. This guide shows you how to stream data from a Neon Postgres database to an external Postgres database.
You will learn how to enable logical replication in Neon, create a publication, and configure an external Postgres database as a subscriber.
Enable logical replication
Enabling logical replication modifies the PostgreSQL
wal_level configuration parameter, changing it from
logical for all databases in your Neon project. Once the
wal_level setting is changed to
logical, it 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.
To enable logical replication in Neon:
- Select your project in the Neon console.
- On the Neon Dashboard, select Project settings.
- Select Beta.
- Click Enable.
You can verify that logical replication is enabled by running the following query:
After enabling logical replication, the next steps involve creating publications on your replication source database in Neon and configuring subscriptions on the destination system or service. These processes are the same as those you would perform in a standalone Postgres environment.
Create a publication
Publications are a fundamental part of logical replication in Postgres. They allow you to specify a set of database changes that can be replicated to subscribers. This section walks you through creating a publication for a
To create a publication for the
This command creates a publication named
users_publication, which will include all changes to the
users table in your replication stream.
With your publication created, you're now ready to configure a subscriber that will receive the data changes from this publication.
Create a Postgres role for replication
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
Grant schema access to your Postgres role
If your replication role does not own the schemas and tables you are replicating from, make sure to grant access. Run these commands for each schema:
SELECT ON ALL TABLES IN SCHEMA instead of naming the specific tables avoids having to add privileges later if you add tables to your publication.
Configure PostgreSQL as a subscriber
A subscriber is a destination that receives data changes from your publications.
This section describes how to configure a subscription on a standalone Postgres instance to a publication defined on your Neon database. After the subscription is defined, the destination Postgres instance will be able to receive data changes from the publication defined on your Neon database.
It is assumed that you have a separate Postgres instance ready to act as the subscriber. This must be a Postgres instance other than Neon, such as a local PostgreSQL installation. Currently, a Neon database cannot be defined as a subscriber. The PostgreSQL version of the subscriber should be compatible with the publisher. The primary (publishing) server must be of the same or a higher version than the replica (subscribing) server. For example, you can replicate from PostgreSQL 14 to 16, but not from 16 to 14. Neon supports Postgres 14, 15, and 16. The Postgres version is defined when you create a Neon project.
Create a subscription
psqlor another SQL client to connect to your subscriber Postgres database.
Create the subscription using the using a
CREATE SUBSCRIPTIONstatement. This example creates a subscription for the
usertable publication (
users_publication) that you created previously.
subscription_name: A name you chose for the subscription.
connection_string: The connection string for your Neon database, where you defined the publication.
publication_name: The name of the publication you created on your Neon database.
Verify the subscription was created by running the following command:
The subscription (
users_subscription) should be listed, confirming that your subscription has been successfully created.
Test the replication
Testing your logical replication setup ensures that data is being replicated correctly from the publisher to the subscriber (from your Neon database to your standalone Postgres instance).
First, generate some changes in the
users table on the publisher database to see if these changes are replicated to the subscriber:
Connect to your Neon database (the publisher) and perform an
INSERToperation. For example:
After making changes, query the
userstable on the publisher to confirm your
Note the changes you made for comparison with the subscriber's data.
Now, connect to your subscriber database on your standalone Postgres instance:
Compare the results with what you observed on the publisher.
On the subscriber, you can also check the status of the replication:
Look for the
last_msg_receive_timeto confirm that the subscription is active and receiving data.
Last updated on