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 (a Postgres destination other than Neon). If you're looking to replicate data from one Neon Postgres instance to another, see Replicate data from one Neon project to another.

Prerequisites

• A Neon project with a database containing the data you want to replicate. If you're just testing this out and need some data to play with, you can use the following statements to create a table with sample data:

  CREATE TABLE IF NOT EXISTS playing_with_neon(id SERIAL PRIMARY KEY, name TEXT NOT NULL, value REAL);
  INSERT INTO playing_with_neon(name, value)
  SELECT LEFT(md5(i::TEXT), 10), random() FROM generate_series(1, 10) s(i);

  For information about creating a Neon project, see Create a project.

• A destination Postgres instance other than Neon.

• Read the important notices about logical replication in Neon before you begin.

• Review our logical replication tips, based on real-world customer data migration experiences.

Prepare your source Neon database

This section describes how to prepare your source Neon database (the publisher) for replicating data to your destination Neon database (the subscriber).

Enable logical replication in the source Neon project

In the Neon project containing your source database, enable logical replication. You only need to perform this step on the source Neon project.

To enable logical replication:

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

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 replication_user

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 replication_user:

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

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.

Create a publication on the source database

Publications are a fundamental part of logical replication in Postgres. They define what will be replicated. To create a publication for all tables in your database:

CREATE PUBLICATION my_publication FOR ALL TABLES;

CREATE PUBLICATION my_publication FOR TABLE playing_with_neon;

CREATE PUBLICATION my_publication FOR TABLE users, departments;

Prepare your destination database

This section describes how to prepare your destination Postgres database (the subscriber) to receive replicated data.

Prepare your database schema

When configuring logical replication in Postgres, the tables in the source database you are replicating from must also exist in the destination database, and they must have the same table names and columns. You can create the tables manually in your destination database or use utilities like pg_dump and pg_restore to dump the schema from your source database and load it to your destination database. See Import a database schema for instructions.

If you're using the sample playing_with_neon table, you can create the same table on the destination database with the following statement:

CREATE TABLE IF NOT EXISTS playing_with_neon(id SERIAL PRIMARY KEY, name TEXT NOT NULL, value REAL);

Create a subscription

After creating a publication on the source database, you need to create a subscription on the destination database.

1. Use the Neon SQL Editor, psql, or another SQL client to connect to your destination database.

2. Create the subscription using the using a CREATE SUBSCRIPTION statement.

   CREATE SUBSCRIPTION my_subscription
   CONNECTION 'postgresql://neondb_owner:<password>@ep-cool-darkness-123456.us-east-2.aws.neon.tech/neondb'
   PUBLICATION my_publication;

   • subscription_name: A name you chose for the subscription.
   • connection_string: The connection string for the source Neon database where you defined the publication.
   • publication_name: The name of the publication you created on the source Neon database.

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

   SELECT * FROM pg_stat_subscription;

   The subscription (my_subscription) should be listed, confirming that your subscription has been created successfully.

Test the replication

Testing your logical replication setup ensures that data is being replicated correctly from the publisher to the subscriber database.

1. Run some data modifying queries on the source database (inserts, updates, or deletes). If you're using the playing_with_neon database, you can use this statement to insert some rows:

   INSERT INTO playing_with_neon(name, value)
   SELECT LEFT(md5(i::TEXT), 10), random() FROM generate_series(1, 10) s(i);

2. Perform a row count on the source and destination databases to make sure the result matches.

   SELECT COUNT(*) FROM playing_with_neon;
   
   count
   -------
   30
   (1 row)

Alternatively, you can run the following query on the subscriber to make sure the last_msg_receipt_time is as expected. For example, if you just ran an insert option on the publisher, the last_msg_receipt_time should reflect the time of that operation.

SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time FROM pg_catalog.pg_stat_subscription;

Switch over your application

After the replication operation is complete, you can switch your application over to the destination database by swapping out your source database connection details for your destination database connection details.

You can find the connection details for a Neon database on the Connection Details widget in the Neon Console. For details, see Connect from any application.