I want to migrate some data from Postgres database A to database B. I found Foreign Data Wrappers that allow you to connect to a table in B while in database A. I have a table in A that I want to move to B and clear out the records in A.
insert into b_table select * from a_table;
delete from a_table;
Will a postgres transaction provide me with all expected error recovery in the event of a crash or disconnect between the two SQL lines even though I'm working with 2 different databases?
I tried an experiment where I used the Foreign Data Wrapper and executed SQL like I had above. In between the insert and delete lines I put a sleep and then killed the process while it was sleeping. No data was committed as a consequence of the insert until it actually hit the connection.commit() line.
So, to answer my own question, it should be safe.
FDW does not use 2-phase commit, so there is always some possibility of a discrepancy.
If you do those in separate transactions (on the master), the discrepancy would be that the data is in two places because the deletion failed to commit. Presumably you could detect it if you are on the look-out for it and fix it, although that could be difficult if other sessions are trying to change the table at the same time.
If you do it in one apparent transaction on the master, then based on inspecting the WAL logs with pg_waldump it appears to to commit the insert before committing the delete, so there would still be some possibility of data duplication.
