Required reading
PostgreSql command line utilities without provide password
Sending messages to slack with bash script
Let’s go then
Recently i need to build some bash scripts to implement a automated backups for my production enviroment with PostgreSQL. I develop a script for backup database in production enviroment and save the backup on staging server for later restore it. Using cronjob you habe an automation of backups an restores. The scripts produces to formats of backups:
- Plain Backup: This produces a gzipepd version of SQL script
- Custom Backup: This produces a compressed custom-format archive suitable for input into pg_restore.
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
#!/bin/bash
##############################
## POSTGRESQL BACKUP CONFIG ##
##############################
# Optional hostname to adhere to pg_hba policies. Will default to "localhost" if none specified.
HOSTNAME=localhost
# Optional username to connect to database as. Will default to "postgres" if none specified.
USERNAME=postgres
# This dir will be created if it doesn't exist. This must be writable by the user the script is
# running as.
BACKUP_DIR=/home/jcottobboni/backups/database/postgresql/
SUFFIX="-daily"
FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`$SUFFIX/"
# Database name to backup
DATABASE=db_name
# 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=15
#### SETTINGS FOR COPY BACKUPS ####
STAGING_ADDRESS='user@ip'
STAGING_FOLDER="$FINAL_BACKUP_DIR"
###########################
### INITIALISE DEFAULTS ###
###########################
if [ ! $HOSTNAME ]; then
HOSTNAME="localhost"
fi;
if [ ! $USERNAME ]; then
USERNAME="postgres"
fi;
###########################
#### START THE BACKUPS ####
###########################
function perform_backups()
{
./slack-cli chat send "Starting backup to database ${DATABASE}" '##slack-channel-name'
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
./slack-cli chat send "Finishing Backup Produção for database: ${DATABASE}" '#slack-channel-name'
echo -e "\nDatabase backup complete!"
}
function copy_backups_to_staging_server()
{
echo "Making backup directory in $FINAL_BACKUP_DIR on Staging Server"
if ! ssh $STAGING_ADDRESS "mkdir -p $FINAL_BACKUP_DIR"; then
echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2
exit 1;
fi;
scp $FINAL_BACKUP_DIR$DATABASE.sql.gz $STAGING_ADDRESS:$STAGING_FOLDER
}
# 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
copy_backups_to_staging_server
Save this content in file called pg_backup.sh, and give permission to execute
1
chomd +x pg_backup.sh
The scp comands sends a copy from db to another server, in this sample i send to my staging server, but you can send for any machine you want, for this command works without ask a password you need to copy your ssh key to the server with this command:
1
ssh-copy-id -i ~/.ssh/id_rsa.pub "user"@"server-address-or-ip"
To enable automate for you script open crontab
1
crontab -e
and write like this:
1
2
# PostgreSQL Backup
00 00 * * * /path/to/script/pg_backup.sh