Postgresql – How trigger update in replica database on Postgres

postgresqlreplicationtrigger

In both servers (master and replica) I have two tables like this:

CREATE TABLE my_table
(
    id SERIAL,
    name TEXT,
    "timestamp" timestamp without time zone,
    CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

In my master server I created a publication with:

CREATE PUBLICATION sync_publication;
ALTER PUBLICATION sync_publication ADD TABLE my_table;

Then I created a use (named syncguy):

CREATE ROLE syncguy WITH REPLICATION LOGIN PASSWORD 'root';

After I granted privileges:

GRANT ALL PRIVILEGES ON DATABASE my_database TO syncguy;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO syncguy;

In postgresql.conf setup wal_level to logical:

wal_level = logical

In postgresql.conf setup wal_level to replica:

wal_level = replica

Then I insert in Master:

insert into public.my_table (id, name, "timestamp") values (
    uuid_generate_v4(),
    'foo',
    current_timestamp
);

Then in both server in my_table i get:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | foo  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

Then I update "name col" in Master to bar value, and I get in both server this:

+--------------------------------------+------+----------------------------+
| id                                   | name | timestamp                  |
+--------------------------------------+------+----------------------------+
| 157b1849-53a5-4206-9f8b-961c1485d20d | bar  | 2020-02-04 10:07:28.048819 |
+--------------------------------------+------+----------------------------+

All of this worked well, my problem is when I create a trigger, I'm using this function and trigger on replica server (I don't use triggers in "master server"):

CREATE FUNCTION public.my_func() RETURNS trigger
    LANGUAGE plpgsql
    AS $$DECLARE
    partition_date TEXT;
    partition TEXT;
BEGIN
    partition_date := to_char(NEW.timestamp, 'YYYY_MM_DD');
    partition := 'messages_' || partition_date;

    EXECUTE 'CREATE TABLE IF NOT EXISTS ' || partition || ' (CONSTRAINT ' || partition || '_pkey UNIQUE (id)) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;';

    RETURN NULL;
END;$$;

ALTER FUNCTION public.my_func() OWNER TO postgres;

CREATE TRIGGER my_trigger BEFORE INSERT ON public.my_table FOR EACH ROW EXECUTE FUNCTION public.my_func();

ALTER TABLE public.my_table ENABLE REPLICA  TRIGGER my_trigger;

So, my objective with this trigger is to partition my replicated data, when I insert in master with trigger in replicate server it's works, is create a table like this messages_2020_02_04

The messages_2020_02_04 contains my replicated data and my_table in replica server is just "referenced", as I said, this works well with INSERT (I tested sending several, all went to the tables generated by the trigger), however when executing "UPDATE" in master the lines are not updated in replica.

Is it possible to work on master updates with replica and triggers in "replica server"?

Best Answer

The problem with updates in your scenario is that my_table on the standby server does not contain the row being updated, so that the UPDATE does nothing and no row level trigger is fired. Since nothing was inserted into my_table, an UPDATE cannot be replicated.

If you want to replicate from a non-partitioned to a partitioned table with logical replication, use PostgreSQL v13 or better, where that is supported. You will bot be able to automatically generate partitions that way, but it shouldn't be a problem to create the partitions before you start replicating.