Logical replication tips

The following tips are based on actual customer data migrations to Neon using logical replication:

• If you are replicating a large dataset to Neon, check whether your indexes exceed 20 GB in size. To estimate the total size of your indexes, run the following query on the source database:

  SELECT pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size FROM pg_stat_user_indexes;

  If the size of your indexes exceeds 20 GB, contact Neon Support to request an increase in the fixed disk size for your Neon compute. A larger disk size will help prevent no space left on device errors. Neon Support may also make additional modifications to your Neon compute to prepare for replicating a large dataset.

• Initial data copying during logical replication can significantly increase the load on both the publisher and subscriber. For large data migrations, consider increasing compute resources (CPU and RAM) for the initial copy. On Neon, you can do this by enabling autoscaling and selecting a larger maximum compute size. The publisher (source database instance) typically experiences higher load, as it serves other requests while the subscriber only receives replicated data.

• For large datasets, avoid creating indexes when setting up the schema on the destination database (subscriber) to reduce the initial data load time. Indexes can be added back after the data copy is complete.

• If you encounter replication timeout errors, consider increasing wal_sender_timeout on the publisher and wal_receiver_timeout on the subscriber to a higher value, such as 5 minutes (default is 1 minute). On Neon, adjusting these settings requires assistance from Neon Support.

• To minimize storage consumption during data replication to Neon, reduce your history retention setting. For example, set it to 1 hour or 0 during the initial copy, and restore it to the desired value afterward.

• Ensure that any Postgres extensions that you depend on are also supported by Neon. For extensions and extension versions supported by Neon, see Supported Postgres extensions. If you find that support is missing for a particular extension or extension version that would prevent you from migrating your data to Neon, please reach out to Neon Support.

• Avoid defining publications with FOR ALL TABLES if you want to add or drop tables from the publication later. It is not possible to add or drop tables from a publication defined with FOR ALL TABLES.

  ALTER PUBLICATION test_publication ADD TABLE users;
  ERROR:  publication "my_publication" is defined as FOR ALL TABLES
  DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.
  
  ALTER PUBLICATION test_publication DROP TABLE products;
  ERROR:  publication "my_publication" is defined as FOR ALL TABLES
  DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

  Instead, you can create a publication for a specific table using the following syntax:

  CREATE PUBLICATION my_publication FOR TABLE users;

  To create a publication for multiple tables, specify a comma-separated list of tables:

  CREATE PUBLICATION my_publication FOR TABLE users, departments;

  For syntax details, see CREATE PUBLICATION, in the PostgreSQL documentation.

If you have logical replication or data migration tips you would like to share, please let us know via the Feedback form in the Neon Console or our feedback channel on Discord.