0

I have created a publication and a subscription on two servers with wal_level = logical in my postgresql.conf. Replication is functioning normally. My problem is to track changes in the replicated table, so Ive created a function and a trigger on INSERTandUPDATE`. However my created trigger does not fire when changes are made in my replicated table.

I've found a similar problem and a solution in another question here, but when I do this:

ALTER TABLE <table_name> ENABLE ALWAYS TRIGGER <trigger_name>;

my replication process stops working and I see pg_replication_slots.active = f in my publisher server.

I would like to avoid solutions with pgAudit, Kafka and others like and stick with PostgreSQL tools if possible because I am a novice and I doubt that I can handle too many new tools at once.

I can provide you all the commands issued on both servers if that would be of any help. Thanks!

In my log file I see that the relation where the changes should be stored does not exist:

relation "mtable_history" does not exist at character 13

Character 13 is a position in a query:

INSERT INTO mtable_history VALUES ...

However, it does exist in \dt

0
0

The problem is that for security reasons, the search_path is set empty during replication replay. So you have to qualify the object names with the schema in the trigger function, for example public.mtable_history.

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.