On Wed, Jan 12, 2022 at 1:22 PM Issa Gorissen <issa-goris...@usa.net> wrote:
> 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?
