Automated PostgreSQL backups

16 May 2017 POSTGRESQL CRON SHELL DEVOPS

Recently we migrated one of our applications from MongoDB to PostgreSQL (which is a story for another time) at work. Similar to the script I developed for MongoDB in my last blog post, I developed one for PostgreSQL. pg_dump utility of PostgreSQL provides some nifty options to create different formats to create backup files. My script produces two types of backup files:

Plain bakup - This produces a gzipepd version of SQL script file which you can execute to restore the database.
Custom backup - This produces a compressed custom-format archive suitable for input into pg_restore.

Without further ado, below is the script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
#!/bin/bash

##############################
## POSTGRESQL BACKUP CONFIG ##
##############################
 
# Optional hostname to adhere to pg_hba policies.  Will default to "localhost" if none specified.
HOSTNAME=127.0.0.1
 
# Optional username to connect to database as.  Will default to "postgres" if none specified.
USERNAME=pgusername
 
# This dir will be created if it doesn't exist.  This must be writable by the user the script is
# running as.
BACKUP_DIR=/var/backups/

# Database name to backup
DATABASE=mydb
 
# Will produce a custom-format backup if set to "yes"
ENABLE_CUSTOM_BACKUPS=yes
 
# Will produce a gzipped plain-format backup if set to "yes"
ENABLE_PLAIN_BACKUPS=yes

#### SETTINGS FOR ROTATED BACKUPS ####

# Number of days to keep daily backups
DAYS_TO_KEEP=14

 
###########################
### INITIALISE DEFAULTS ###
###########################
 
if [ ! $HOSTNAME ]; then
  HOSTNAME="localhost"
fi;
 
if [ ! $USERNAME ]; then
  USERNAME="postgres"
fi;
 
 
###########################
#### START THE BACKUPS ####
###########################
 
function perform_backups()
{
  SUFFIX=$1
  FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"
 
  echo "Making backup directory in $FINAL_BACKUP_DIR"
 
  if ! mkdir -p $FINAL_BACKUP_DIR; then
    echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
    exit 1;
  fi;
 
  echo -e "\n\nPerforming full backup of $DATABASE"
  echo -e "--------------------------------------------\n"
 
    if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
    then
      echo "Plain backup of $DATABASE"
 
      if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then
        echo "[!!ERROR!!] Failed to produce plain backup database $DATABASE" 1>&2
      else
        mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE".sql.gz
      fi
    fi
 
    if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
    then
      echo "Custom backup of $DATABASE"
 
      if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
        echo "[!!ERROR!!] Failed to produce custom backup database $DATABASE"
      else
        mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress $FINAL_BACKUP_DIR"$DATABASE".custom
      fi
    fi
 
  echo -e "\nDatabase backup complete!"
}
 
# DAILY BACKUPS
 
# Delete daily backups older than DAYS_TO_KEEP
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'

perform_backups "-daily"

Save this in a file named pgbackup.sh. After that you need to make the file executable by issuing following command:

1
chomd +x pgbackup.sh

You must be wondering about where to specify the database password. Well as per PostgreSQL documentation, you need to create password file .pgpass in user’s home directory and should contain lines of the following format:

1
hostname:port:database:username:password

Make sure the permissions on .pgpass must disallow any access to world or group. You can do this my issuing following command:

1
chmod 0600 ~/.pgpass

This password file is automatically referanced by pgdump utility whenever invoked. Finally you need to create a cron job to execute the pgbackup.sh script automatically. Issue following command to create a cron job:

1
crontab -e

Enter following:

1
2
# PostgreSQL Backup
00 00 * * * /path/to/script/pgbackup.sh

Save the file by pressing Escand then wq + Enter (similar to vi editor). This cron job will execute the backup script at 12 o’clock every night creating your database backups.

Pranav Prakash

Software Developer, Photographer, Computer Addict & Web Enthusiast

Approx 7 minutes read

Automated PostgreSQL backups