Replicate to another Postgres database using Logical Replication
For this example, you will need:
- A Supabase project
- A PostgreSQL database (running v10 or newer)
You will be running commands on both of these databases to publish changes from the Supabase database to the external database.
- Create a
publicationon the Supabase database:
_10CREATE PUBLICATION example_pub;
- Also on the Supabase database, create a
replication slot:
_10select pg_create_logical_replication_slot('example_slot', 'pgoutput');
- Now we will connect to our external database and subscribe to our
publicationNote: ):
This will need a direct connection to your database and you can find the connection info in the Dashboard.
You will also need to ensure that ipv6 is supported by your replication destination.
If you would prefer not to use the postgres user, then you can run CREATE ROLE <user> WITH REPLICATION; using the postgres user.
_10CREATE SUBSCRIPTION example_sub_10CONNECTION 'host=db.oaguxblfdassqxvvwtfe.supabase.co user=postgres password=YOUR_PASS dbname=postgres'_10PUBLICATION example_pub_10WITH (copy_data = true, create_slot=false, slot_name=example_slot);
create_slot is set to false because slot_name is provided and the slot was already created in Step 2.
To copy data from before the slot was created, set copy_data to true.
- Add all the tables that you want replicated to the publication.
_10ALTER PUBLICATION example_pub ADD TABLE example_table;
- Check the replication status using
pg_stat_replication
_10select * from pg_stat_replication;