Postgresql pg_dumpall backup with compression
Last updated: Jul 01, 2022
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>
- Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR
- 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 FILE_NAME="full_cluster_`date "+%Y_%m_%d"`"
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.