During a query that references any remote tables on a foreign server, postgres_fdw opens a transaction on the remote server if one is not already open corresponding to the current local transaction. The remote transaction is committed or aborted when the local transaction commits or aborts. Savepoints are similarly managed by creating corresponding remote savepoints.
..."
Interesting, I'll work on a test case later!
I may be missing something, but why not reverse your original set up? Assuming transactional behavior works as expected something like:
1) Setup postgres_fdw in main database.
2) Create FOREIGN TABLE pointing to table in archive database.
3) INSERT INTO/SELECT from main table to archive table.
4) DELETE FROM main table.
I had considered this as well, as this would allow me to rollback the delete (assuming my intel on postgres_fdw transactions was correct, which it may not be after all). I wondered if a remote insert would be broken up into individual inserts like the remote delete was, as that would be equally unappealing for the same reasons. But obviously worth confirming.
