I solved the problem. We use bucardo to replicate our postgresql database. This caused my problem. In postgresql logs, I saw the error log as follows:
ERROR: cannot PREPARE a transaction that has executed LISTEN, UNLISTEN or NOTIFY
In this blog post, the reason for the problem is explained:
The problem is that the Postgres LISTEN / NOTIFY system cannot be used with prepared transactions. Bucardo uses a trigger for source tables that issues NOTIFY to let the main Bucardo daemons know that something has changed and needs to be replicated. However, their application betrayed the PREPARATION TRANSFER as a partial part of its work. That way, they would update the table that fires the trigger that sends NOTIFY. Then the application issues PREPARE TRANSACTION, as a result of which the above error was made. Bucardo is set to deal with this situation; Instead of using notification triggers, the Bucardo daemon can be configured to search for any changes at a given interval. The steps to change the Bucardo behavior for this synchronization are simple:
The blog solution did not work for us. We could afford not to replicate the database causing the error. Therefore, we removed replication using the following commands:
[ root@Baskan config]
Since the triggers are not deleted automatically, they must be deleted manually: In our case, there were three triggers. Their names were: bucardo_delta, bucardo_kick_synclrms, bucardo_note_trunc_synclrms bucardo_note_trunc_synclrms
To reset a trigger, use the following command:
drop TRIGGER trigger_name on table_name;
Just in case, there may be other triggers in the table placed by bucardo, you can use the following command in postgresql to see all the triggers in the table:
\dS table_name;
After these steps, the system began to work normally, without throwing any exceptions.