Logs

table-migration-plan-subscribers_subscriberupdateevent

3 min read

Table Migration Plan

This document describes the steps to migrate from the existing subscribers_subscriberupdateevent table to the new subscribers_subscriberupdateevent_v2 table. The steps include creating the new table on AWS and all on-prem locations, updating replication, and transitioning software to use the new table.

1. Create New Table on AWS and On-Prem Locations

Create the new table subscribers_subscriberupdateevent_v2 on AWS (RDS) and all on-premises locations.

  • AWS RDS:

    CREATE TABLE public.subscribers_subscriberupdateevent_v2 (
    id integer NOT NULL,
    subscriber_id bigint,
    is_active boolean,
    datetime timestamp with time zone NOT NULL,
    reset_for_channel bigint,
    reset_for_subscriber bigint,
    is_awake boolean
);
 
ALTER TABLE public.subscribers_subscriberupdateevent_v2 OWNER TO webpushdb;
 
CREATE SEQUENCE public.subscribers_subscriberupdateevent_v2_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
 
ALTER SEQUENCE public.subscribers_subscriberupdateevent_v2_id_seq OWNER TO webpushdb;
 
ALTER SEQUENCE public.subscribers_subscriberupdateevent_v2_id_seq OWNED BY public.subscribers_subscriberupdateevent_v2.id;
 
ALTER TABLE ONLY public.subscribers_subscriberupdateevent_v2 ALTER COLUMN id SET DEFAULT nextval('public.subscribers_subscriberupdateevent_v2_id_seq'::regclass);
 
ALTER TABLE ONLY public.subscribers_subscriberupdateevent_v2
    ADD CONSTRAINT subscribers_subscriberupdateevent_v2_pkey PRIMARY KEY (id);

  • On-Prem Locations: Execute the same commands on all on-premises servers:

    • bts00, bts01
    • de00, de01, de03
    • ca01, ca02, ca03
    • hel01

2. Add New Empty Table to Replication on Publisher

Add the new empty table subscribers_subscriberupdateevent_v2 to the publisher for replication.

  • Add Table to Publication (AWS RDS Publisher): 
    ALTER PUBLICATION worker_db ADD TABLE public.subscribers_subscriberupdateevent_v2;

3. Refresh Subscriptions on All On-Prem Servers

Refresh the subscriptions on all on-premises servers to ensure they start replicating the new table.

  • Refresh Subscription Command (on each on-prem server): 
    ALTER SUBSCRIPTION worker_db REFRESH PUBLICATION;

4. Software Update to Use New Table

Update the software to use the new subscribers_subscriberupdateevent_v2 table. This involves changing database queries in the application code.

  • Tasks:
    • Update all SQL queries to reference subscribers_subscriberupdateevent_v2.
    • Services that use SubscriberUpdateEvent model:
    • Deploy latest subscribers-cache on all remote clusters
    • !!! Since that moment we will stop receiving any updates regarding subscribers
    • Deploy latest subscribers-tasks and subscribers-errors on amazon cluster
    • We expect to see new records in v2 table
    • At this stage we are ready to enable replica
    • New records start to appear in v2 table on premises
    • SubscriberUpdatesState needs to be reseted on all remote clusters (setting last_update_event to 0)
    • !!! New events begin to process by subscribers-cache on remote clusters

5. Delete Old Table from Replication

After confirming that the software is successfully using the new table, delete the old table from replication.

  • Remove Table from Publication (AWS RDS Publisher): 
    ALTER PUBLICATION worker_db DROP TABLE public.subscribers_subscriberupdateevent;

6. Drop Old Table

Once replication has been updated and the software is fully operational with the new table, drop the old table to free up resources.

  • Drop Old Table: 
    DROP TABLE public.subscribers_subscriberupdateevent;

Graph View