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 theUPDATE
does nothing and no row level trigger is fired. Since nothing was inserted intomy_table
, anUPDATE
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.