So I have this question, how to script the making of base backup for
transfer on the slave server when the two SQL functions must be called
in the same connection, in Bash for example; is this doable?
Not sure if I understand the problem correctly but if you are asking how to open a connection to the database and keep it open while doing something else in bash, then you could use "coproc" for instance. I sometimes use this function:
coproc psql -XAtF $'\t' service="$srv"
pg () {
local sql exp
sql="$1"
[ "$2" ] && { sql="$2"; exp="$1"; }
echo "$sql" >&${COPROC[1]}
read -u ${COPROC[0]} || return
[ "$exp" ] || return 0
[ "$REPLY" = "$exp" ] || return 64
return 0
}
And here is some usage
local TMOUT=1
pg BEGIN 'BEGIN;'
This sends a BEGIN command and expects the word BEGIN as reply.
if pg 'LOCK TABLE' '
LOCK TABLE some_table
IN SHARE ROW EXCLUSIVE MODE NOWAIT;
';
then
:
elif (( $? > 128 )); then # read timeout exceeded
die "Cannot lock some_table";
else
die "Unexpected error while locking some_table";
fi
In the example above a table is locked with NOWAIT. Bash's read timeout is set to 1sec. If that's exceeded because the lock is not obtained, read comes back with status>128.
unset TMOUT
pg '
SELECT coalesce(min(id), -1)
, coalesce(max(id), -1)
FROM some_table'\;
Now we want to read some data. So, TMOUT is unset. The REPLY variable will have the answer.
IFS=$'\t' read mn mx <<<"$REPLY"
And this is how to split the reply into 2 bash variables, mn and mx.
At the end of the transaction then
pg 'COMMIT' 'COMMIT;'
And send \q to finish psql. If "set -e" mode is active, make sure to negate the result.
# expecting read to fail after \q. Hence the negation.
! pg '\q'
In simpler cases, when you just want to push commands to psql, you can also use this:
exec {PSQL}> >(psql ...)
Note there is a blank between the 2 >. This is important.
Then
echo >&$PSQL 'create table tf ();'
echo >&$PSQL 'drop table tf;'
Does this help?