This script is a combination of tasks, including cleaning up old log and backup files, performing a PostgreSQL database dump, compressing the dump, and uploading files to an SFTP server. Make sure to replace SFTP_USER=user SFTP_PASSWORD=******** with your actual SFTP user name and password for security.

postgresql_backup.sh

#!/bin/bash

# ----------------------------------------------------------------------- #
# Automate PostgreSQL Database Backups to Secure SFTP Server.             #
# ----------------------------------------------------------------------- #
# This Bash script simplifies the process of backing up                   #
# your PostgreSQL database by automating the entire workflow.             #
# It cleans up old log and backup files, performs a secure database dump, #
# compresses the backup, and uploads it to a remote SFTP server.          #
# Ensure data integrity and easy retrieval with this efficient script.    #
# ----------------------------------------------------------------------- #

# Define variables for directories, SFTP connection information, and other settings.
DIR_BACKUP=~/14/backups # Directory where backups will be stored.
DIR_LOG=~/14/logs       # Directory where log files will be stored.
SFTP_USER=user          # SFTP username (Please replace with actual user name).
SFTP_PASSWORD=********  # SFTP password (Please replace with actual password).
SFTP_HOST=sftp://nagiyev.pro  # SFTP host or server address.
SFTP_PORT=22                  # SFTP port number.
SFTP_DIR=/postgre             # SFTP directory where backups will be uploaded.
DB_NAME=my_postgre_database   # Name of the PostgreSQL database to be backed up.
FILE_NAME=`date +%Y-%m-%d`    # Generate a timestamp for the backup file name (e.g., YYYY-MM-DD).
KEEP_DATES=10                 # Number of days to keep old log and backup files.

# Delete log files older than $KEEP_DATES days.
find $DIR_LOG -name "*.log" -type f -mtime +$KEEP_DATES -delete

# Delete backup files (with .gz extension) older than $KEEP_DATES days.
find $DIR_BACKUP -name "*.gz" -type f -mtime +$KEEP_DATES -delete

# Perform a PostgreSQL database dump and save it to a file.
/usr/pgsql-14/bin/pg_dump -U postgres $DB_NAME > $DIR_BACKUP/$DB_NAME-$FILE_NAME.sql 2>> $DIR_LOG/$DB_NAME-$FILE_NAME.bak.log

# Compress the PostgreSQL dump file using gzip.
gzip $DIR_BACKUP/$DB_NAME-$FILE_NAME.sql

# Use the lftp command to upload files to an SFTP server.
lftp -c "set net:timeout 5;set net:max-retries 3;set net:reconnect-interval-multiplier 1;set net:reconnect-interval-base 5;set sftp:auto-confirm yes;open -u $SFTP_USER,$SFTP_PASSWORD $SFTP_HOST -p $SFTP_PORT;mirror -X .* -X .*/ --parallel=10 --reverse --verbose --delete $DIR_BACKUP $SFTP_DIR;bye"

The command to make script runnable chmod +x postgresql_backup.sh

To run backup with crontab add the foloowing line to crontab -e
0 20 * * * /directory/to/postgresql_backup.sh 2> /directory/to/cron.log