Replicate data from Postgres to Neon
Learn how to replicate data from a local Postgres instance or another Postgres provider to Neon
Neon's logical replication feature allows you to replicate data from a local Postgres instance or another Postgres provider to 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 local Postgres instance or Postgres instance hosted on another provider 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:
-
A destination Neon project. For information about creating a Neon project, see Create a project.
-
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 Postgres database
This section describes how to prepare your source Postgres database (the publisher) for replicating data to your destination Neon database (the subscriber).
Enable logical replication in the source Neon project
On your source database, enable logical replication. The typical steps for a local Postgres instance are shown below. If you run Postgres on a provider, the steps may differ. Refer to your provider's documentation.
Enabling logical replication requires changing the Postgres wal_level
configuration parameter from replica
to logical
.
-
Locate your
postgresql.conf
file. This is usually found in the PostgreSQL data directory. The data directory path can be identified by running the following query in your PostgreSQL database: -
Open the
postgresql.conf
file in a text editor. Find thewal_level
setting in the file. If it is not present, you can add it manually. Setwal_level
tological
as shown below: -
After saving the changes to
postgresql.conf
, you need to reload or restart PostgreSQL for the changes to take effect. -
Confirm the change by running the following query in your PostgreSQL database:
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. For example:
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
:
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:
important
Avoid defining publications with FOR ALL TABLES
if you want the flexibility to add or drop tables from the publication later. It is not possible to modify a publication defined with FOR ALL TABLES
to include or exclude specific tables. For details, see Logical replication tips.
To create a publication for a specific table, you can use the following syntax:
To create a publication for multiple tables, provide a comma-separated list of tables:
For syntax details, see CREATE PUBLICATION, in the PostgreSQL documentation.
Prepare your Neon destination database
This section describes how to prepare your destination Neon 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 a subscription
After creating a publication on the source database, you need to create a subscription on the destination database.
-
Use the Neon SQL Editor,
psql
, or another SQL client to connect to your destination database. -
Create the subscription using the using a
CREATE SUBSCRIPTION
statement.subscription_name
: A name you chose for the subscription.connection_string
: The connection string for the source Postgres database where you defined the publication.publication_name
: The name of the publication you created on the source Postgres database.
-
Verify the subscription was created by running the following command:
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.
-
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: -
Perform a row count on the source and destination databases to make sure the result matches.
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.
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.
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.