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 and restores for my production and staging enviroments with PostgreSQL. I develop a script for backup database in production enviroment and save the backup on staging server. And now i developed one to take the uploaded files and restore the staging db. Using cronjob you habe an automation of restores.
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
#!/bin/bash
##############################
## POSTGRESQL RESTORE 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
# Number of days to keep daily backups
DAYS_TO_KEEP=15
###########################
### INITIALISE DEFAULTS ###
###########################
if [ ! $HOSTNAME ]; then
HOSTNAME="localhost"
fi;
if [ ! $USERNAME ]; then
USERNAME="postgres"
fi;
###########################
#### START THE RESTORE ####
###########################
function perform_restore()
{
./slack-cli chat send "Starting restore to database: ${DATABASE}" '#slack-channel-name'
echo "Making restore of files in directory $FINAL_BACKUP_DIR"
echo -e "
Performing full Restore of $DATABASE"
echo -e "--------------------------------------------
"
psql -U postgres -h localhost -c "drop database $DATABASE";
psql -U postgres -h localhost -c "create database $DATABASE";
gzip -d "$FINAL_BACKUP_DIR$DATABASE.sql.gz"
/usr/bin/pg_restore --host $HOSTNAME --port 5432 --username $USERNAME --dbname $DATABASE "$FINAL_BACKUP_DIR$DATABASE.sql.gz"
./slack-cli chat send "Finishing Restore for database: ${DATABASE}" '#slack-channel-name'
echo -e "
Database restore complete!"
}
# DAILY RESTORES
# Delete daily restores older than DAYS_TO_KEEP
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*-daily" -exec rm -rf '{}' ';'
perform_restore
Save this content in file called pg_restore.sh, and give permission to execute
1
chmod +x pg_restore.sh
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_restore.sh