Re: WAL Archiving and base backup

[Torsten Förtsch <tfoertsch123@xxxxxxxxx>]

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