incrementally periodically dump a postgresql database to google drive using borg, zstandard & rclone

README.org

prerequisites

sudo apt install libssl-dev acl-dev python3-pip && pip3 install borgbackup

borg

• sudo apt install acl-dev

sudo apt install -y make gcc && wget ‘https://github.com/facebook/zstd/releases/download/v1.4.2/zstd-1.4.2.tar.gz’ && tar -zxvf zstd-1.4.2.tar.gz && cd zstd-1.4.2 && make && sudo make install

zstd

• rclone
  • wget ‘https://downloads.rclone.org/rclone-current-linux-amd64.deb’ && sudo dpkg -i rclone-current-linux-amd64.deb ; rm rclone-current-linux-amd64.deb

pg_backup.sh

export BORG_PASSCOMMAND='/bin/echo CHANGEME'
export DBNAME='PGDATABASETOBACKUP'
export DRIVENAME='RCLONEDRIVENAME' # must be in ~/.config/rclone/rclone.conf
export TDIR='TARGETDIRWITHINRCLONEDRIVE'
export MNTFOLDER=~/mnt/$DRIVENAME # where to locally temporarily mount the rclone drive

export DFN="pg_dump/data-"$(hostname)"-"$(date +'%Y-%m-%d').sql

function mnt() {
    mkdir -p $MNTFOLDER && rclone mount --allow-non-empty --daemon --vfs-cache-mode full $DRIVENAME:/ $MNTFOLDER && \
        #echo 'sleeping 5' && \                                                                                                                                                                                                                                                                                                                   
        sleep 5
    }
function umnt() {
    #echo 'unmounting' && \                                                                                                                                                                                                                                                                                                                       
        fusermount -u $MNTFOLDER
}
function restore_gdrive() {
    mnt && \
        restore $1 $2
    umnt
}
function restore() {
    borg extract $MNTFOLDER/$TDIR::$1 --stdout $2
}
function list_gdrive() {
    mnt && \
        borg list $MNTFOLDER/$TDIR
    umnt
}
function borg_init() {
    borg init -e repokey  $MNTFOLDER/$TDIR
}
function do_dump() {
    pg_dump $DBNAME | borg create --compression zstd -v --stats $MNTFOLDER/$TDIR/::$(hostname)"-"$(date +'%Y-%m-%d')"$1" -
}

function rclone_sync() {
    rclone sync -v $MNTFOLDER/$TDIR $DRIVENAME:/$TDIR
    }
#RCLONEPID=$!                                                                                                                                                                                                                                                                                                                                     
function run() {
    echo 'mounting remote backup folder' && \
        echo 'initializing backup repo' && \
        borg_init ||:
    echo 'dumping & backuping' && \
        do_dump "$1" && \
        # echo 'cleaning up' && \                                                                                                                                                                                                                                                                                                                 
        # rm $DFN && \                                                                                                                                                                                                                                                                                                                            
        echo 'rclone sync' && \
        rclone_sync && \
        echo 'all done!'
}
if [[ "$(basename -- "$0")" == "pg_backup.sh" ]]; then
    echo "RUNNING ; $0";
    run $1
fi