Postgresql pg_dumpall backup with compression

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 

postgresql_backup

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

postgresql_backup

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

postgresql_backup

 

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

postgresql_backup

Specifying when I want my script file to be executed.

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

postgresql_backup

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.

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to find out the size of tables in a database
PostgreSQL - How to find out the size of tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more