1

I am trying to restore PostgreSQL 12.2 to lower PostgreSQL 9.4.12 I encountered problem with restoring triggers using "FOR EACH ROW EXECUTE FUNCTION", since pg 12 is using EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) while pg 9.4 EXECUTE PROCEDURE function_name ( arguments )

I tried to get all triggers from original db by:

SELECT pg_get_triggerdef(f.oid)
FROM pg_catalog.pg_trigger f

then replace FUNCTION -> PROCEDURE and finally create new triggers. However for some triggers i get error that the trigger already exists (despite not being listed). Apart from that there are some auto-generted(?) triggers with names like "RI_ConstraintTrigger_c_121395" that i cannot see in the original database.

I am not sure if I can just somehow drop all triggers from my newly restored database and than create them again with a script that i pasted above.

I am not sure if it matters but to restore i used: pg_restore -d database -v -h host -p port -U postgres dumpFileName

Stile
  • 31
  • 6
  • There is no better way. Downgrading PostgreSQL is not supported, so you have to edit the dump until it fits the old version. Press the owners into upgrading, since running an old, unsupported version jeopardizes the data. – Laurenz Albe Jun 29 '20 at 18:45

0 Answers0