Replicate data 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

To enable logical replication in Neon:

1. Select your project in the Neon Console.
2. On the Neon Dashboard, select Settings.
3. Select Logical Replication.
4. Click Enable to enable logical replication.

You can verify that logical replication is enabled by running the following query:

SHOW wal_level;
 wal_level
-----------
 logical

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 users table.

1. Create the users table in your Neon database. You can do this via the Neon SQL Editor or by connecting to your Neon database from an SQL client such as psql.

   CREATE TABLE users (
     id SERIAL PRIMARY KEY,
     username VARCHAR(50) NOT NULL,
     email VARCHAR(100) NOT NULL
   );

2. To create a publication for the users table:

   CREATE PUBLICATION users_publication FOR TABLE users;

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 CLI, Console, or API are granted membership in the neon_superuser role, which has the required REPLICATION privilege.

neon roles create --name alex

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. For example, the following commands grant access to all tables in the public schema to Postgres role alex:

GRANT USAGE ON SCHEMA public TO alex;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alex;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO alex;

Granting 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.

When configuring logical replication in Postgres, the tables defined in the publication on the publisher must also exist on the subscriber, and they must have the same name and table schema. For the example setup described in this guide, a corresponding users table should exist in the subscriber database. You can use the CREATE TABLE statement used previously to create the users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

Create a subscription

1. Use psql or another SQL client to connect to your subscriber Postgres database.

2. Create the subscription using the using a CREATE SUBSCRIPTION statement. This example creates a subscription for the user table publication (users_publication) that you created previously.

   CREATE SUBSCRIPTION users_subscription
   CONNECTION 'postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname'
   PUBLICATION users_publication;

   • 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.

3. Verify the subscription was created by running the following command:

   SELECT * FROM pg_stat_subscription;

   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:

1. Connect to your Neon database (the publisher) and perform an INSERT operation. For example:

   INSERT INTO users (username, email) VALUES ('new_user', 'new_user@example.com');

2. After making changes, query the users table on the publisher to confirm your INSERT:

   SELECT * FROM users;

   Note the changes you made for comparison with the subscriber's data.

3. Now, connect to your subscriber database on your standalone Postgres instance:

   psql -h [server_IP_or_hostname] -U [username] -d [database] -W

4. Query the users table in the subscriber database:

   SELECT * FROM users;

   Compare the results with what you observed on the publisher.

5. On the subscriber, you can also check the status of the replication:

   SELECT * FROM pg_stat_subscription;

   Look for the last_msg_receive_time to confirm that the subscription is active and receiving data.