PostgreSQL Tutorial: Automatically Backup Database

Summary: If you are a database administrator, chances are that you have backups of your data. And if you have backups of your data, chances are that you thought about how to automate your backup processes – this tutorial should act as a guide on how to do that with PostgreSQL.

Table of Contents

Choose a backup method

By default, as far as PostgreSQL is concerned, when backing up your data you have a couple of options:

• You can use pg_dump or pg_dumpall
• You can also use pg_basebackup

The main difference between those two tools offered by PostgreSQL is that pg_dump and pg_dumpall are logical backups, and pg_basebackup is a physical backup. There is also a difference between pg_dump and pg_dumpall. The first one is commonly used to backup a single PostgreSQL database and pg_dumpall is more commonly used in order to dump all of the PostgreSQL databases into a single script file. Here’s how you can use pg_dump to achieve your backup goals:

$ pg_dump -U postgres -W -F t database_name > database_name.tar

Here’s what the command above does:

• pg_dump invokes pg_dump.
• The -U option specifies the user.
• The -W option forces pg_dump to prompt for a password as soon as enter is clicked.
• The -F option specifies the file output format (the file format can be either c for custom-format, d for a directory-format archive, a t for a .tar file, or p for a plain file)
• database_name specifies the database name you want to backup data from.
• database_name.tar is the output file.

You can also backup all of your PostgreSQL databases into a single script file as already outlined above by using pg_dumpall. Here’s how to do that:

$ pg_dumpall -U postgres -f data.sql

The options of pg_dumpall are similar to that of pg_dump.

• You can also back up all of the objects in all databases, also roles, tablespaces, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges by using the command $ pg_dumpall --schema-only > schema.sql
• If you want to backup only roles, use the command $ pg_dumpall --roles-only > roles.sql

Finally, you can use pg_basebackup to create physical backups of your PostgreSQL database.

$ pg_basebackup -D /backups -x

The options are:

• -D is used to specify the path of the output directory
• -x will include the transaction logs in the backup folder

Automated backup on Linux

Here’s a simple guide on how to use cron jobs to automate your PostgreSQL backup processes. First, create a bash script performing backup, might named backup.sh:

#!/bin/bash

# Optional hostname to adhere to pg_hba policies. Will default to "localhost" if none specified.
BACKUP_HOST="192.168.89.128"

# Optional username to connect to database as. Will default to "postgres" if none specified.
BACKUP_USER=postgres

# backup directory infomation
BACKUP_DIR=/home/postgres/backup/
BACKUP_FILE="$BACKUP_DIR/db_backup_$(date +%Y%m%d).bak"

# Set the PGPASSWORD environment variable
export PGPASSWORD="test"

# Perform the backup
pg_dumpall -h $BACKUP_HOST -U $BACKUP_USER -f $BACKUP_FILE

After the script is complete, we can now schedule so that it can run every day at a particular time. For this tutorial, we are going yo schedule this program to run everyday at midnight.

we can open the crontab using the command crontab -e and enter the following command:

0 0 * * * bash /home/to/backup.sh >> /home/to/backup.log 2>&1

Below is an example of scheduling of the backup.sh script. we scheduled the file to run every day at midnight and send logs to the backup.log file.

0 0 * * * bash /home/postgres/backup/backup.sh >> /home/postgres/backup/backup.log 2>&1

After you have finished configuring the settings and scheduling your backup, your PostgreSQL backup should now be automated.

Automated backup on Windows

Create a batch script file, might named backup.bat. The file must be located in PostgreSQL backup directory not the bin folder.

@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
  set year=%%i
  set month=%%j
  set monthday=%%k
  set weekday=%%l
)
set datestr=%year%%month%%monthday%

set BACKUP_FILE=<BACKUP_DIR>\db_backup_%datestr%.bak
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=<Password>
echo on
<PGHOME>\bin\pg_dumpall -h <HostName> -U <UserName> -f %BACKUP_FILE%

You need to change the script file based on your production environment:

• Change <BACKUP_DIR> to a valid backup directory.

  Note: make sure there are no spaces after the word BACKUP_FILE, any spaces will cause this setting not to work.

• Change the <Password> setting above to the correct password for the backup users. Description of PGPASSWORD.

  Note: make sure there is no spaces after the word PGPASSWORD, any spaces will cause this setting not to work.

• Change <HostName> either to IP address or DNS name of the server hosting PostgreSQL.

• Change <UserName> to backup user, make sure this user has access to database for backup purposes.

• Change <PGHOME> to the PostgreSQL backup tool installation directory.

After the script is complete, create a task in the Windows Task Scheduler.

Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the backup is run and the files are stored, as a high level user name and password are stored in plain text. Another option is to modify the pg_hba.conf file adding the backup server as a trusted connection.

Summary

Automating PostgreSQL backups is not as hard as it might seem at first glance. When using either pg_dumpall, pg_basebackup, or pg_backrest, with the help of Linux crontab or Windows Task Scheduler, you can rest assured that your PostgreSQL data is in good hands. If you have any more questions, make sure to have a look through the backup tool documentation or contact support if you require further assistance.

See more

PostgreSQL Administration