Postgresql pg_dumpall backup with compression

Colleagues hello to all.

In today's article, we'll talk about backing up a database in Postgresql with compression. In Postgresql, one of the utilities called pg_dumpall is responsible for backup. With this utility, you can backup the entire database cluster at once. I'll show you how to make a script, and we'll add this script to the task scheduler (crontab), so that the backup is performed automatically at the specified time.< /p>

Related article:

1. Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR
2. Postgresql - Automatic database start

First, we need to install the necessary package that will be responsible for compressing the backup. The package is called bzip2. To check if we have this package installed, use the command:

$. yum list installed | grep bzip2 

I already have it installed.

If your bzip2 package is not installed, then you can install it using the command:

$. yum install bzip2

The next step let's create an executable file in which we will have a script.

$. vim /home/postgres/postgresql_backup.sh

And in this file put:

#Backup All Database
export PGPASSWORD='Qwerty1'
export PGHOME=/app/postgresql
export LD_LIBRARY_PATH=/app/postgresql/lib
export PGDATA=/app/postgresql/pgdatabase/data
export BACKUPDIR="/app/postgresql_backup"
export FILE_NAME="full_cluster_`date "+%Y_%m_%d"`"
export PATH=$PGHOME/bin:$PGDATA:$PATH
pg_dumpall --username=postgres --if-exists --clean --verbose | bzip2 > $BACKUPDIR/$FILE_NAME.sql.bz2

In the next steps, we will assign the necessary rights to this file.

$. chmod +x postgresql_backup.sh

The last step we need to add this file to the task scheduler (crontab) so that it runs automatically at the time we specified.

$. crontab -e

Specifying when I want my script file to be executed.

30 23 * * * bash /home/postgres/admin_scripts/postgresql_backup.sh

I've set my script to run every day at 11:30 pm.

Thank you all, I hope that my article helped you in some way.