I am fetching PostgreSQL(13) data with some modifications from remote database, pushing it to another database, I want rollback first one if second one failed to commit (like lost connection), and vise versa, my problem if first one commit success, second one failed, how to roll back comited first one?
Is there any technique in Postgresql to do that?
Since you want to perform a distributed transaction, you need the two-phase commit protocol.
You start transactions on both databases as usual, but instead of committing them, you run
PREPARE TRANSACTION 'some_name';
This performs everything that might fail during a commit and persists the transactions. Once that has succeeded, you run the following on both databases:
COMMIT PREPARED 'some_name';
to commit the transactions.
If anything fails during the PREPARE TRANSACTION, you run the following to get rid of already prepared transactions:
ROLLBACK PREPARED 'some_name';
Note that you need transacrion manager software if you use prepared transactions, so that any prepared transactions that are left behind after a crash or other unexpected problem are reliably cleaned up. Prepared transactions that are not committed or rolled back stay around for ever and will with absolute certainty break your database.