fbpx

Backup

Automate Postgresql Daily Database Backup using Pgbackreast and bash

by
To automate daily backups of your PostgreSQL database using PGBackRest and bash, you can create a bash script that runs the pgbackrest command and schedule it to run daily using a cron job. Here’s an example script that performs a full backup and stores it in a local directory:
				
					#!/bin/bash

# Set the backup directory
BACKUP_DIR=/backups

# Set the PGBackRest configuration file path
PGCONF=/etc/pgbackrest.conf

# Set the date format for the backup file name
DATE=$(date +%Y-%m-%d)

# Create the backup directory if it doesn't exist
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p $BACKUP_DIR
fi

# Perform a full backup and store it in the backup directory
pgbackrest backup --type=full --target=$BACKUP_DIR/$DATE --config=$PGCONF

Save this script to a file (e.g., pgbackup.sh), and make it executable using the chmod command:

				
					chmod +x pgbackup.sh

Then, schedule the script to run daily using a cron job. To do this, open the crontab file using the following command:

				
					crontab -e

Add the following line to the crontab file to run the backup script at 2:00 AM every day:

				
					0 2 * * * /path/to/pgbackup.sh

Replace /path/to/pgbackup.sh with the full path to the backup script file.

Save and exit the crontab file. The backup script will now run daily at the scheduled time and store the backups in the specified directory.

Note that this is a basic example, and you may need to modify the script and cron job settings depending on your specific needs and environment. For more information on cron jobs and scheduling tasks in Linux, you can refer to the Linux man pages or online resources.

How to Take PostgGreSQL Database Backup on AWS S3 bucket using PGBackRest ?

by

To take a backup of your PostgreSQL database using PGBackRest and store it in an Amazon S3 bucket, you can follow these general steps:

  1. Set up an Amazon S3 bucket to store your backups. Make sure you have the necessary access keys and permissions to access the bucket.

  2. Create a PGBackRest configuration file (pgbackrest.conf) that specifies the S3 bucket as the backup storage location. Here’s an example configuration:

				
					[global]
log-level-console=info
repo1-path=/pgbackrest
repo1-retention-full=2
repo1-retention-archive=1
repo1-s3-bucket=<bucket_name>
repo1-s3-endpoint=<s3_endpoint>
repo1-s3-key=<access_key>
repo1-s3-key-secret=<access_secret>
repo1-s3-region=<region>
repo1-s3-uri-style=path

  • Replace <bucket_name> with the name of your S3 bucket, <s3_endpoint> with the endpoint URL for your S3 region, <access_key> and <access_secret> with your AWS access key and secret access key, and <region> with your S3 region.

  • Run the following command to perform a full backup and store it in the S3 bucket:

				
					pgbackrest backup --type=full --target=s3:<backup_directory>

    1. Replace <backup_directory> with the directory path in the S3 bucket where you want to store the backup.

    2. Monitor the backup progress by looking at the output of the command. PGBackRest provides detailed information about the backup progress, including the size of the backup, the number of files being backed up, and the backup speed.

    3. Once the backup is complete, you can verify it by running the pgbackrest info command with the --output=json option to display the backup information in JSON format.

    Note that these are general steps, and the exact command and options may differ depending on your specific needs and environment. For more detailed instructions and examples, you can refer to the PGBackRest documentation.

How to Take Full Database Backup using PGBackRest

by

To take a full database backup with PGBackRest, you can use the pgbackrest backup command. Here are the general steps:

  1. Make sure you have a valid PGBackRest configuration file (pgbackrest.conf) that specifies the database to back up and the backup storage location.

  2. Run the following command to perform a full backup:

				
					pgbackrest backup --type=full --db-include=<database_name> --target=<backup_directory>

        3. This command creates a full backup of the specified database and stores it in the backup storage location specified in the configuration file. The --type=full option specifies that a full backup should be taken.

        4.Monitor the backup progress by looking at the output of the command. PGBackRest provides detailed information about the backup progress, including the size of the backup, the number of files being backed up, and the backup speed.

        5.Once the backup is complete, you can verify it by running the pgbackrest info command. This command displays information about the available backups, including the type of backup, the backup size, and the backup timestamp.

Note that these are general steps, and the exact command and options may differ depending on your specific needs and environment. For more detailed instructions and examples, you can refer to the PGBackRest documentation.

PGBackRest backup for multiple Databases

 

To take a backup of multiple databases with PGBackRest, you can use the --db-include option to specify a comma-separated list of database names. Here are the general steps:

  1. Make sure you have a valid PGBackRest configuration file (pgbackrest.conf) that specifies the databases to back up and the backup storage location.

  2. Run the following command to perform a full backup of multiple databases:

				
					pgbackrest backup --type=full --db-include=<database1>,<database2>,<database3> --target=<backup_directory>

  1. Replace <database1>,<database2>,<database3> with a comma-separated list of the database names you want to back up, and <backup_directory> with the directory where you want to store the backup. The --type=full option specifies that a full backup should be taken.

  2. Monitor the backup progress by looking at the output of the command. PGBackRest provides detailed information about the backup progress, including the size of the backup, the number of files being backed up, and the backup speed.

  3. Once the backup is complete, you can verify it by running the pgbackrest info command. This command displays information about the available backups, including the type of backup, the backup size, and the backup timestamp.

Note that these are general steps, and the exact command and options may differ depending on your specific needs and environment. For more detailed instructions and examples, you can refer to the PGBackRest documentation.

What is the pgbackrest in PostGreSQL?

by

PGBackRest is an open-source backup and restore tool for PostgreSQL databases. It is designed to be reliable, fast, and easy to use, with features that help database administrators manage their backups and restores efficiently.

PGBackRest works by taking advantage of PostgreSQL’s built-in file system snapshots to create point-in-time backups of your database. This means that it can take backups without having to stop your database, and the backups are consistent with the state of your database at the time they were taken.

Some of the features of PGBackRest include incremental backups, parallelism, compression, and encryption. It also supports various backup storage options, such as local disk, network file systems, and cloud storage services.

PGBackRest is written in C and is compatible with various operating systems, including Linux, macOS, and Windows. It is widely used in production environments by PostgreSQL database administrators and is considered one of the most reliable and robust backup tools for PostgreSQL databases

Offical website

How to Install PgBackRest ?

The installation process for PGBackRest varies depending on your operating system and the package manager you’re using. Here are the general steps to install PGBackRest on Linux using the package manager:

  1. Add the PGBackRest repository to your system’s package manager. You can find the repository information on the official PGBackRest website.

  2. Install PGBackRest using your system’s package manager. For example, if you’re using Ubuntu, you can run the following command:

				
					sudo apt-get install pgbackrest

Verify that PGBackRest is installed correctly by running the following command:

				
					pgbackrest --version

  1. This should display the version of PGBackRest that you installed.

  2. Configure PGBackRest by creating a configuration file. The configuration file tells PGBackRest where to store backups, how to connect to your PostgreSQL server, and other settings. You can find examples of configuration files on the PGBackRest website.

  3. Start using PGBackRest to backup and restore your PostgreSQL databases. You can use the pgbackrest backup command to create backups and the pgbackrest restore command to restore backups.

Note that these are general steps, and the exact commands and configuration may differ depending on your operating system and package manager. For more detailed installation instructions, you can refer to the PGBackRest documentation.

How to Configure PGBackRest ?

Configuring PGBackRest involves creating a configuration file that specifies the settings and options for your backup and restore operations. Here are the general steps for configuring PGBackRest:

  1. Create a configuration file for PGBackRest. The configuration file should be named pgbackrest.conf and should be located in the directory where you plan to run PGBackRest. You can find an example configuration file on the PGBackRest website.

  2. Edit the configuration file to specify the settings and options for your backup and restore operations. Some of the key settings you’ll need to configure include:

    • global: This section contains global settings that apply to all databases and backups. You’ll need to specify the location of your backup storage, the compression and encryption settings, and other options.

    • db-name: This section contains settings that apply to a specific PostgreSQL database. You’ll need to specify the database name, the user and password to connect to the database, and other options.

    • stanza: This section contains settings that apply to a group of databases that are backed up together. You’ll need to specify the name of the stanza, the databases included in the stanza, and other options.

  3. Test your configuration by running the pgbackrest check command. This command checks your configuration file for errors and validates that PGBackRest can connect to your PostgreSQL server.

  4. Start using PGBackRest to perform backup and restore operations. You can use the pgbackrest backup command to create backups and the pgbackrest restore command to restore backups.

Note that these are general steps, and the exact configuration may differ depending on your specific needs and environment. For more detailed instructions and examples, you can refer to the PGBackRest documentation.

How to Take PostgreSQL Schema Backup

by

In some cases, we have to take PostgreSQL schema level backup. it’s like a logical backup where it just backup the schema objects like tables, indexes, and procedures. These backups are used in the condition where you need to restore just one schema backup, not the whole database. For daily backup, you can create a shell or bash script and schedule it using crontab or windows scheduler.

Script For Schema Backup 

				
					#!/bin/bash
mv /tmp/db_list.log /tmp/db_list.log_old
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > /tmp/db_list.log ; sed -i '/^$/d' /tmp/db_list.log
file=/tmp/db_list.log
#schema_name
#DB
date=$(date '+%Y-%m-%d')
bkp_path=/var/lib/pgsql

for DB in `cat $file`
do
echo "#################Starting the backup as on ${date} for ${DB}"
pg_dump -U postgres -v -n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog' $DB -Ft -f ${bkp_path}/${DB}_schema_bkp_${date}.tar > `${bkp_path}/${DB}_schema_bkp_${date}.log)`
echo "#################Backup Complited as on ${date} for ${DB}"
done

echo "listing files older than 15 Days"
find ${bkp_path} -name "*schem*.tar" -type f -mtime +1 > ${bkp_path}/old_file_list.log
find ${bkp_path} -name "*.log" -type f -mtime +1 > ${bkp_path}/old_file_list.log

echo "deleting backup/log file older than 15 Days"
find ${bkp_path} -name "*.tar" -type f -mtime +15 > ${bkp_path}/old_file_list.log -delete

In this script, we have excluded system schemas like ‘information_schema’ and many more. Please remove ‘-N and schema Name if you need a backup system schema.’ It will start including the schema backup in the tar file.

How to execute the Script ?

				
					chmod 777 backup_schema.sh
./backup_schema.sh

Read More...