Replicate to an external Postgres
Replicate changes from your Supabase database to a Postgres database outside Supabase, using logical replication with a publication and a subscription.
Prerequisites
For this example, you need:
- A Supabase project
- A Postgres database (running v10 or later)
Procedure
You run commands on both of these databases to publish changes from the Supabase database to the external database.
-
Create a
publicationon the Supabase database:CREATE PUBLICATION example_pub; -
Also on the Supabase database, create a
replication slot:select pg_create_logical_replication_slot('example_slot', 'pgoutput'); -
Connect to your external database and subscribe to the
publication:This needs a direct connection (not a Connection Pooler) to your database and you can find the connection info in the Connect panel in the
Direct connectionsection.You also need to ensure that IPv6 is supported by your replication destination (or you can enable the IPv4 add-on)
If you would prefer not to use the
postgresuser, then you can runCREATE ROLE <user> WITH REPLICATION;using thepostgresuser.CREATE SUBSCRIPTION example_subCONNECTION 'host=db.oaguxblfdassqxvvwtfe.supabase.co user=postgres password=YOUR_PASS dbname=postgres'PUBLICATION example_pubWITH (copy_data = true, create_slot=false, slot_name=example_slot);For projects running Postgres 17 or later, it is possible to subscribe to a Read Replica by using your Read Replica's connection string.
create_slotis set tofalsebecauseslot_nameis provided and the slot was already created in Step 2. To copy data from before the slot was created, setcopy_datatotrue. -
Back on the Supabase database, add all the tables that you want replicated to the publication:
ALTER PUBLICATION example_pub ADD TABLE example_table; -
Check the replication status using
pg_stat_replicationselect * from pg_stat_replication;You can add more tables to the initial publication, but you need to run a REFRESH on the subscribing database. See ALTER PUBLICATION.
Next steps
To go further with replication, see these pages:
- Read Replicas: subscribe to a Read Replica instead of the primary on Postgres 17 or later.
- IPv4 add-on: connect from a replication destination that doesn't support IPv6.