What the f*** is Logical Replication?

Problem: we have data. Lots and lots of data. We’re not talking random, pointless data. This data includes how much YOU are supposed to get paid, whether or not we actually paid you, how much we’re supposed to bill clients, email attachments… etc.

When it works, replication is awesome. You get instantaneous, accurate, FREE data copy from Prod to your analytics warehouse!

• However, replication does take work to setup.
• And if you make mistakes and don't fix them in time, you will break PROD. So you need to be careful and thorough.
• The main failure modes on setup are:

1. mismatched enums between Prod and DW, and
2. invalid replica identities.

• Problem #1 causes immediate failures on initial sync. That's quick to catch and fix, if you're watching the DW logs on ScaleGrid.
• Problem #2 is much worse. It doesn't pop up until AFTER the initial sync. You'll see it the first time PROD tries to send a changed row to an existing replicated table. Once that happens, it blocks all further replication to DW, AND it blocks PROD services from updating that specific table. */

How to create logical replication?

1. Create a publication - run this on the publisher’s DB e.g DAL, FLOW, SEATED:

   create publication dal_dw_push2

   • Before creating a new publication, you can check what publications exist in the database:

   select * 
   from pg_catalog.pg_publication_tables ppt 
   order by 1,3;
   

2. Create subscription : run on warehouse:

   create subscription dal_dw_gulp
   connection 'host=SG-dal-2449-pgsql-master.servers.mongodirector.com dbname=d8679uc3c9vepn port=5432 user=sgpostgres password=#kIMvv5ZFhs3tZ19 sslmode=require'
   publication dal_dw_push2;
   

   • Before creating a new subscription, you can check what subscriptions & tables are being replicated:

   select subs.*,relmap.relname 
   from pg_catalog.pg_subscription_rel subs
   join pg_catalog.pg_class relmap
   on subs.srrelid = relmap.oid
   order by 3
   

3. Add tables to publication: run this on the publisher’s DB e.g DAL, FLOW, SEATED:

   **alter** **publication** dal_dw_push2
   **add** **table** public."_companyToClient"
   

4. Truncate table if it already exists (in the warehouse):

   **truncate** **table**
   public."_companyToClient"
   

5. Create table by generating sql DDL and run script generated in the warehouse:

   

6. Refresh publication- run on warehouse:

   alter subscription dal_dw_gulp2 refresh publication;

7. Grant permission on table to 3 roles in db- run on warehouse:

   GRANT DELETE, UPDATE, TRUNCATE, REFERENCES, TRIGGER, SELECT, INSERT ON TABLE public."_companyToClient" TO analyst;
   GRANT DELETE, UPDATE, TRUNCATE, REFERENCES, TRIGGER, SELECT, INSERT ON TABLE public."_companyToClient" TO metabase;
   GRANT DELETE, UPDATE, TRUNCATE, REFERENCES, TRIGGER, SELECT, INSERT ON TABLE public."_companyToClient" TO reporting;
   

8. Log onto ScaleGrid → Warehouse → Logs to see if replication error is fixed.

Problems:

Problem - Table in the warehouse is out of sync