Re: WAL Archiving and base backup

On Wed, Jan 12, 2022 at 1:22 PM Issa Gorissen <issa-gorissen(at)usa(dot)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?