4

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"?

12
  • 1
    Please show the CREATE TRIGGER statement. Feb 4, 2020 at 16:08
  • @LaurenzAlbe I create DECLATE by pgAdmin. But I edited question with "structure" generated by Backup. Feb 4, 2020 at 18:24
  • 1
    @LaurenzAlbe I had already tried, but this does not work in replication (apparently). Anyway I created everything from the beginning (databases, tables, trigger, etc) and applied: CREATE TRIGGER my_trigger BEFORE INSERT OR UPDATE ON public.my_table FOR EACH ROW EXECUTE FUNCTION public.my_function();, after I inserted a data in the master, the table with the prefix "messages_" was generated in the bank with the replica, then I executed "update" in the master for this data, but did not receive the update. Unfortunately it still doesn't work (note that I tried: ENABLE ALWAYS TRIGGER too) Feb 4, 2020 at 19:36
  • 1
    @LuanHuynh The replica does not have to do with the trigger, who makes the replica is a postgres system (postgresql.org/docs/10/logical-replication.html), so that without a trigger to replicate from master server => replica server it works normally, but as I need the tables in the separate replica to make analytical queries without loss of performance I need to separate the data into different tables. About my_func question, yes, i tried create INSERT OR UPDATE, but not work. Feb 4, 2020 at 20:58
  • 1
    @LuanHuynh Note that the trigger is not even executed in case of an update coming from the master, I put a RAISE NOTICE inside the function and it is only issued when I execute INSERT on the master, but when it is update it does not execute "notice". Note that if I execute the UPDATE directly on the replica server the trigger is executed normally. So the problem looks like something in the postgres replication mechanism. Feb 4, 2020 at 20:58

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.