Postgresql – Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

I'm only familiar with SQL Server:

Each operation is atomic. If you run a delete, and it cascades to other tables, those records are gone, too, as soon as the statement is over. They don't magically come back into existence unless the transaction is rolled back.

If you're relying on the ID values and don't want to cascade the related tables, consider switching to a merge strategy (where you use UPDATE or, preferably MERGE) instead of blowing everything away and starting from scratch.

I suppose you could try something like disabling the relationships during data loading, but given what I think you're trying to do, that seems fraught with peril. Definitely try a merge strategy first.