-
Notifications
You must be signed in to change notification settings - Fork 0
/
cold-backup.sh
executable file
·92 lines (68 loc) · 2.03 KB
/
cold-backup.sh
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
#!/bin/bash
set -e -o pipefail
if [ $# -ne 1 ]
then
echo "Usage: cold-backup.sh <environment>"
exit 1
fi
ENVIRONMENT=$1
PROJECT=dft-schemes-${ENVIRONMENT}
REGION=europe-west1
BACKUP_INSTANCE=schemes
RESTORE_INSTANCE=${BACKUP_INSTANCE}-backup
PGDATABASE=schemes
PGUSER=schemes
# Create Cloud SQL instance for backup
gcloud sql instances create ${RESTORE_INSTANCE} \
--project ${PROJECT} \
--region ${REGION} \
--database-version POSTGRES_16 \
--tier db-custom-1-3840 \
--edition enterprise
# Obtain latest backup id and timestamp
BACKUP=$(gcloud sql backups list \
--project ${PROJECT} \
--instance ${BACKUP_INSTANCE} \
--sort-by "~enqueuedTime" \
--limit 1 \
--format="value(id,enqueuedTime)"
)
BACKUP_ID=$(echo "${BACKUP}" | cut -f1)
BACKUP_TIMESTAMP=$(echo "${BACKUP}" | cut -f2)
ARCHIVE=${PGDATABASE}-${ENVIRONMENT}-$(date -d $BACKUP_TIMESTAMP -u +"%Y%m%dT%H%M%SZ").dump
# Restore latest backup to Cloud SQL instance
gcloud sql backups restore "${BACKUP_ID}" \
--project ${PROJECT} \
--backup-instance ${BACKUP_INSTANCE} \
--restore-instance ${RESTORE_INSTANCE} \
--quiet
# Start Cloud SQL Auth proxy
./proxy.sh ${ENVIRONMENT} ${RESTORE_INSTANCE} &
# Get database password
PGPASSWORD=$(gcloud secrets versions access latest \
--project ${PROJECT} \
--secret database-password)
# Dump database
# Redirect output within container to avoid pg_dump truncation bug
docker run --rm \
--network=host \
-e PGHOST=localhost \
-e PGUSER=${PGUSER} \
-e PGPASSWORD=${PGPASSWORD} \
-e PGDATABASE=${PGDATABASE} \
-v ${PWD}:/data \
-u "$(id -u):$(id -g)" \
postgres:16 \
sh -c "until pg_isready; do sleep 1; done && pg_dump --format custom --no-acl > /data/${ARCHIVE}"
# Compress archive
gzip ${ARCHIVE}
# Encrypt archive
bw get password "UYCS Database Backup Passphrase" \
| ( gpg --batch --symmetric --passphrase-fd 0 ${ARCHIVE}.gz && rm ${ARCHIVE}.gz )
# Stop Cloud SQL Auth proxy
docker stop cloud-sql-proxy
# Delete Cloud SQL instance for backup
gcloud sql instances delete \
--project ${PROJECT} \
--quiet \
${RESTORE_INSTANCE}