Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases? 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.