Last updated October 06, 2026

Replicate data between a Heroku Postgres Advanced and an external database, or between Heroku Postgres Advanced databases with managed logical replication with the Heroku CLI.

Use Cases

Use logical replication with your Heroku Postgres Advanced to:

• Replicate data from your Heroku Postgres Advanced database to an external Postgres database.
• Replicate data from an external Postgres database to your Heroku Postgres Advanced database.
• Replicate data between different Heroku Postgres Advanced databases.

Publications

A publication defines a set of tables whose data changes replicate through logical replication.

To set up publications for managed logical replication:

1. Enable publishing on the source database.
2. Create a publication on the source database.
3. Get the publisher connection info on the source database.
4. Mirror your publisher schema on the source database.

After setting up your publications, manage them by:

• Updating your publications
• Viewing your publications
• Destroying your publications
• Monitoring your publications

Enable Publishing

Before creating a publication for logical replication, enable your source database for publishing with the heroku data:pg:logical-replication:publishing:enable command:

$ heroku data:pg:logical-replication:publishing:enable DATABASE -a example-app

Enabling logical replication publishing for advanced-horizontal-01234... requested

Wait for advanced-horizontal-01234 to finish updating before creating publications. Use heroku data:pg:info --app example-app to track progress.

Create a Publication

A publication defines a set of tables whose data changes replicate through logical replication. On the source (publisher) database, create a publication for the tables you want to replicate with the heroku data:pg:logical-replication:publications:create command.

To publish a specific list of tables, use the --table flag with the table names to publish. You can add multiple tables. This example creates a publication named orders with the public.orders and public.products tables:

$ heroku data:pg:logical-replication:publications:create DATABASE --name orders --table public.orders --table public.products -a example-app

Creating publication orders on advanced-horizontal-01234...done

To publish all tables for a specific schema, use the --schema flag with the schemas to publish. You can add multiple schemas. This example creates a publication named sales with the public and inventory schemas:

$ heroku data:pg:logical-replication:publications:create DATABASE --name sales --schema public --schema inventory -a example-app

Creating publication sales on advanced-horizontal-01234...done

You can also create a publication with all the schemas on a database with the --all-schemas flag.

Get the Publisher Connection Info

After enabling publication access on your Heroku Postgres Advanced database, Heroku creates a new publisher role with logical replication privileges. You must use the connection credentials for the publisher role to create your subscriptions in the next steps.

If you’re replicating data from an Advanced database, use the heroku data:pg:credentials:url command to obtain the credential details for the publisher role:

$ heroku data:pg:credentials:url postgresql-symmetric-12345 -n publisher -a example-app
=== Connection information for publisher credential:

Connection info string:
"dbname=d1j7osp… host=cebsj0tnotue9r.cluster-c3qe86y12345.eu-west-1.rds.amazonaws.com port=5432 user=publisher password=p76251… sslmode=require"

Connection URL:
postgres://publisher:p76251…@cebsj0tnotue9r.cluster-c3qe86y12345.eu-west-1.rds.amazonaws.com:5432/d1j7osp…

The Connection info string shows the connection details in the libpq connection string format that you can use directly to create a subscription.

Alternatively, you can get the publisher connection details from the Heroku Dashboard:

1. Open your publisher Heroku Postgres Advanced database from the Datastores tab on the dashboard.
2. Select the Attachments tab.
3. Click the publisher replication credential to open the credentials detail page.

Update a Publication

Update the targets on the source database for the publication with the heroku data:pg:logical-replication:publications:update command:

$ heroku data:pg:logical-replication:publications:update DATABASE --name orders --table public.sales -a example-app

Updating publication orders on advanced-horizontal-01234...done

View Your Publications

To see all the logical replication publications on a Postgres Advanced database, run the heroku data:pg:logical-replication:publications command:

$ heroku data:pg:logical-replication:publications DATABASE -a example-app

Name    New Tables   Owner   Target
------- ------------ ------  --------------------
orders  Not Included u12345  tables: public.orders
orders  Not Included u12345  tables: public.products
sales   Included     u67890  schemas: public
sales   Included     u67890  schemas: inventory

To get info on an individual logical replication publication, run the heroku data:pg:logical-replication:publications:info command:

$ heroku data:pg:logical-replication:publications:info DATABASE --name orders -a example-app

Name:                orders
Owner:               ubitise4r2c94d
Target:              schemas: public
Current Tables:      public.bar, public.baz, public.foo, public.pgbench_accounts, public.pgbench_branches, public.pgbench_history, public.pgbench_tellers, public.test
New Tables:          Included / Not Included

Destroy a Publication

To destroy a logical replication publication, run the heroku data:pg:logical-replication:publications:destroy command:

$ heroku data:pg:logical-replication:publications:destroy DATABASE --name orders -a example-app --confirm example-app

Destroying publication orders on advanced-horizontal-01234... done

Mirror the Source Schema

Postgres’ logical replication doesn’t automatically replicate the publisher database schema when you create a subscription. You must create the necessary tables, indexes, constraints, and views on the subscriber database before replication starts.

Use Postgres’ pg_dump tool to export the database schema from your publisher database with the --schema-only option. You can use further options from pg_dump to export only the specific tables (--table) or schemas (--schema) that you want to replicate. For example:

$ pg_dump \
    --schema-only \
    --no-acl \
    --no-owner \
    --no-comments \
    --schema=public \
   "postgres://uc33au4:pfead6…@publisher.hostname:5432/dam1…" \
    > schema_dump.sql

Then, review the resulting schema dump and apply it to your subscriber database to mirror the source schema:

$ psql -f schema_dump.sql  "postgres://um65ju7…:p123hj…@subscriber.hostname:5432/abc2…"

Subscriptions

A subscription defines the connection to the publisher database through logical replication.

To set up subscriptions for managed logical replication:

1. Enable subscribing on the subscribing database.
2. Mirror the source schema on the subscribing database.
3. Create a subscription on the subscribing database.

Enable Subscribing

Before the subscriber database can subscribe for logical replication, enable subscribing with the heroku data:pg:logical-replication:subscribing:enable command:

$ heroku data:pg:logical-replication:subscribing DATABASE -a example-app

Enabling logical replication subscribing for advanced-horizontal-01234... requested

Wait for advanced-horizontal-01234 to finish updating before creating subscriptions. Use heroku data:pg:info --app example-app to track progress.

Create a Subscription

On the subscriber database, use the CREATE SUBSCRIPTION command to create a subscription.

Set the CONNECTION parameter to the libpq-formatted connection string that you got in the Get the Publisher Connection Info step, in a key-value format. If you’re replicating data from an external Postgres database, get the connection string for the publisher role with logical replication capabilities on your source database.

Set the PUBLICATION parameter to the name of the publication you created in the Create a Publication step.

CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=<host> port=5432 dbname=<dbname> user=publisher password=<password> sslmode=require'
  PUBLICATION my_publication;

After you create a subscription, Postgres performs an initial table sync and copies existing data from the publisher to the subscriber before streaming data changes. The initial table data load can take significant time for large tables.

See Monitor Replication for details on monitoring replication progress from the subscriber.

Monitor Replication

Postgres provides views with subscription and publication-related details to monitor logical replication.

Monitoring from the Publisher

Monitoring from the Subscriber

Caveats and Limitations

• The database schema doesn’t replicate automatically from the publisher to the subscriber database. You must create tables, schemas, indexes, constraints, and views on the subscriber database before you create the subscription. See Mirror the Source Schema for details.
• Schema changes don’t replicate. DDL operations, such as CREATE, ALTER, DROP, don’t stream through logical replication. Apply schema changes to both the publisher and subscriber.
• Sequence data doesn’t replicate. The data in serial or identity columns that use sequences replicates as part of the table. The start and next values of the sequence itself doesn’t update with replication. If your app depends on sequences being in sync, for example if you’re replicating data to fail over or cutover to the subscriber database, use setval() to advance your sequences manually.
• A published table must have a replica identity configured to replicate UPDATE and DELETE operations. Make sure that your published tables have a primary key or a unique index to act as their replica identity.

See Logical Replication Restrictions for more details.

When using logical replication, keep in mind:

• Replication slots retain WAL on the publisher database. A subscriber that disconnects holds the publisher’s WAL until it reconnects or drops the slot. This WAL accumulation can fill the publisher’s database disk and cause operational issues. Drop unused subscriptions promptly and monitor restart_lsn to verify the oldest WAL still required by replication slots.
• Make sure that your subscriber can connect to the publisher database over the network. Verify the network configuration and database access or firewall rules that apply to both the subscriber and the publisher if the connection fails.
• External publishers require further manual configuration. If your publisher isn’t a Heroku Postgres Advanced database, set wal_level = logical on the publisher and adjust its max_replication_slots and max_wal_senders settings.