Mercurial > public > ssdt-docker

annotate scripts/remote-backup-all.sh @ 426:bb2b3172d497

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
USASR-5154 updated email
author belknapSSDT <zach.belknap@mcoecn.org>
date Wed, 27 Sep 2023 11:22:56 -0400
parents 4285b8a2762e
children
rev   line source
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
1 #!/bin/bash
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
2 # Executes a database backup for the specified database container.
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
3 # The current directory is expected to contain a project configured
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
4 # as with SSDT conventions for an application database.
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
5 #
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
6 # When successful, the output file will be in ./backup with the
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
7 # container name and timestamp in the file.
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
8 # The file will also be placed on the specified remote target
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
9 # The format of the output is a compressed pg_dump (sql) format.
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
10 #
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
11 #
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
12 # This will backup both usasdb and uspsdb,
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
13 # along with the top level contents of the project directory
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
14 #Environment variables can be used for REMOTE_BACKUP_TARGET and REMOTE_USERNAME
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
15 remoteTarget=${1:-$REMOTE_BACKUP_TARGET}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
16 userName=${2:-$REMOTE_USERNAME}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
17 projectDir=${3:-$PWD}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
18
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
19
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
20 cd $projectDir
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
21
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
22 source "${SSDT_SCRIPTS:-$(dirname "${BASH_SOURCE[0]}")}/.functions.sh"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
23
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
24 set -o pipefail
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
25 mkdir -p ./backup
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
26
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
27 container1=usasdb
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
28 container2=uspsdb
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
29
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
30 project=$(composeGetProject)
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
31
320
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
32 #If the project is empty, we want to stop the process because this is being run from the wrong directory
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
33
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
34 if [ "$project" == "" ]; then
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
35 echo "no project available"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
36 exit 1
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
37 fi
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
38
320
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
39 ## first will try to get IRNfrom usasdb; if that is empty it will try to get it from uspsdb
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
40
314
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
41 IRN=$(docker-compose exec -T $container1 psql --username=postgres --dbname=$container1 -t -c 'select irn from organization')
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
42
318
c61524abdfd6 correcting adding project to remote backup scripts target
aldrich@ssdt-ohio.org
parents: 317
diff changeset
43 if [ "$IRN" == "" ]; then
314
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
44 IRN=$(docker-compose exec -T $container2 psql --username=postgres --dbname=$container2 -t -c 'select irn from organization')
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
45 fi
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
46
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
47 ##Trim function in postgres didn't work - so take out the extra space this way.
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
48 IRN=`echo $IRN | sed -e 's/^[[:space:]]*//'`
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
49
320
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
50 ## if both database are empty, it will put IRN 000000
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
51 if [ "$IRN" == "" ]; then
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
52 echo "no IRN set, using 000000"
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
53 IRN=000000
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
54 fi
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
55
314
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
56 echo "project is $project"
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
57 echo "Containers are $container1 and $container2"
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
58 echo "Remote target is $remoteTarget"
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
59 echo "Username is $userName"
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
60 echo "IRN is $IRN"
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
61
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
62
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
63 backupFile1=./backup/${IRN}${project}-${container1}.$(date +%Y-%m-%d-%H-%M-%S).backup
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
64 backupFile2=./backup/${IRN}${project}-${container2}.$(date +%Y-%m-%d-%H-%M-%S).backup
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
65 backupFile3=./backup/${IRN}${project}-directory.$(date +%Y-%m-%d-%H-%M-%S).directorycontents.tar.gz
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
66
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
67 #usasdb
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
68 echo "starting backup of $container1 for $project"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
69 docker-compose exec -T $container1 sh -c "gosu postgres pg_dump -Cc --if-exists --dbname=$container1 ; (exit $?) " > ${backupFile1}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
70
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
71 if [[ $( grep --count "CREATE TABLE" ${backupFile1} ) -lt 200 || $( grep --count "PostgreSQL database dump complete" ${backupFile1} ) -eq 0 ]]; then
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
72 echo "ERROR: backup verification FAILED"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
73 echo "ERROR: $(tail ${backupFile1})"
320
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
74 echo "Error: empty database"
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
75 #else We still gzip the backup file
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
76 fi
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
77 gzip ${backupFile1}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
78
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
79 echo "completed backup of $container1 for $project to ${backupFile1}"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
80
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
81
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
82
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
83 #####
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
84 #uspsdb
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
85
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
86 echo "starting backup of $container2 for $project"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
87 docker-compose exec -T $container2 sh -c "gosu postgres pg_dump -Cc --if-exists --dbname=$container2 ; (exit $?) " > ${backupFile2}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
88
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
89 if [[ $( grep --count "CREATE TABLE" ${backupFile2} ) -lt 200 || $( grep --count "PostgreSQL database dump complete" ${backupFile2} ) -eq 0 ]]; then
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
90 echo "ERROR: backup verification FAILED"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
91 echo "ERROR: $(tail ${backupFile2})"
320
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
92 echo "Error: empty database"
58c49a386a11 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 319
diff changeset
93 #else We still gzip the backup file
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
94 fi
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
95 gzip ${backupFile2}
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
96
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
97 echo "completed backup of $container2 for $project to ${backupFile2}"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
98
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
99
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
100
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
101 #######
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
102
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
103 #backup of all files in current directory
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
104 echo "starting backup of current directory for $project"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
105
314
d78b45c28205 backup script adding IRN to name
aldrich@ssdt-ohio.org
parents: 287
diff changeset
106 #tar -czf ${backupFile3} . --exclude=./backup
317
146c0c263cf9 adding project to remote backup scripts target
aldrich@ssdt-ohio.org
parents: 314
diff changeset
107 tar --exclude=./backup -czf ${backupFile3} .
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
108
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
109 echo "completed backup of all files for $project to ${backupFile3}"
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
110
319
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
111 #Create remote directory before scp
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
112 host=`echo $userName@$remoteTarget | sed -e 's/:.*//'`
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
113
319
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
114 target=`echo $remoteTarget | sed -e 's/.*://'`
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
115
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
116
319
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
117 ssh $host mkdir -p $target/$project
7c460a921709 making changes to remote backup scripts to create directory
aldrich@ssdt-ohio.org
parents: 318
diff changeset
118
322
4285b8a2762e making changes to remote backup scripts to account for IRN - tweaks
aldrich@ssdt-ohio.org
parents: 320
diff changeset
119 #SCP files
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
120 #
317
146c0c263cf9 adding project to remote backup scripts target
aldrich@ssdt-ohio.org
parents: 314
diff changeset
121 scp ${backupFile1}.gz ${backupFile2}.gz ${backupFile3} $userName@$remoteTarget/$project
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
122
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
123 echo " "
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
124
318
c61524abdfd6 correcting adding project to remote backup scripts target
aldrich@ssdt-ohio.org
parents: 317
diff changeset
125 echo "completed sending ${backupFile1}.gz, ${backupFile2}.gz, and ${backupFile3} to ${remoteTarget}/${project} as user $userName"
287
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
126
4cc087cde1d0 backup script additions and changes
aldrich@nwoca.org
parents:
diff changeset
127