Backups, chapter 2

Backups, chapter 2

Overview

It was bound to happen as I thought more about it, but I’ll leave the previous post up for reasons of posterity.

So since I last wrote, I’ve migrated my diaspora* instance to a new, Debian 11.4.0 server VM (from Ubuntu 22.04) and also started afresh with a new PostgreSQL database. I knew practically nothing about PostgreSQL when I started, and know only a tiny bit more now.

I was pleased that I didn’t have to do any of the fannying around with different versions of OpenSSL with the Debian install. The instrucitons were perfectly simple to follow and the base install was easy to complete. Again, I have the HAProxy package looking after reverse proxying and SSL on my pfSense router.

I didn’t manage to convert my existing MySQL diaspora_production database to PostgreSQL. I’ve been informed that I probably didn’t import my indexes but I have no idea how I would have done that anyway. There doesn’t seem to be one single (free) tool to accomplish a satisfactory migration for my use case.

Anyhow, I’m here to talk about my backup process now that I have my diaspora* instance in a good state. I wanted an automated process to backup:

  • my diaspora_production database
  • the whole /home/diaspora folder (excepting /home/diaspora/backups)

to cover all my bases.

I adapted the backup script from the diaspora* project wiki and apated it to suit my requirements.

The backup process is run on the hour from a cron script.

Off-site backup platforms

I have both a storage box from Hetzner and a Backblaze B2 account.

Backup Script

/home/diaspora/diaspora/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
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

#!/bin/bash

# ensure right directory
#cd $(dirname $0)/..
USER='diaspora'
cd /home/$USER/diaspora
pwd

if [ "$(cat .ruby-gemset)" != "diaspora" ]; then
	  echo -e "Unexpected directory"
	    exit 1
fi

DBCONFIG=$( /home/$USER/.rvm/rubies/ruby-2.7.6/bin/ruby -r yaml -e "puts YAML.load_file('config/database.yml')['production'].values_at('adapter','host','port','username','password','database','encoding').join(';')" )

IFS=';' read -a DBCONFIG_ARRAY <<< "$DBCONFIG"

BACKUP_FOLDER=/home/$USER/backups/diaspora
ADAPTER=${DBCONFIG_ARRAY[0]}
HOST=${DBCONFIG_ARRAY[1]}
PORT=${DBCONFIG_ARRAY[2]}
USERNAME=${DBCONFIG_ARRAY[3]}
PASSWORD=${DBCONFIG_ARRAY[4]}
DATABASE=${DBCONFIG_ARRAY[5]}
ENCODING=${DBCONFIG_ARRAY[6]}
export BORG_PASSPHRASE='[REDACTED]'

DUMP_FILE_NAME=${ADAPTER}_${DATABASE}"_"`date +%F_%H:%M:%S`
DATABASE_ARCHIVE_NAME="diaspora_"$(hostname)"_"`date +%F_%H:%M:%S`".tgz"
HOME_ARCHIVE_NAME=$BACKUP_FOLDER"/home-"$USER"_"`date +%F_%H:%M:%S`".tgz"

echo -e "\n:`date +%F_%H:%M:%S` ℹ️  Creating diaspora* backup...\n"

# Database dump

case $ADAPTER in
  postgresql)
    echo -e "`date +%F_%H:%M:%S`: 🕒 Running PostgreSQL dump..."
    TMP_FILE=$( mktemp )
    env PGPASSWORD=$PASSWORD pg_dump -h $HOST -p $PORT -U $USERNAME -E $ENCODING $DATABASE > $TMP_FILE
    status=$?
    if [ $status -ne 0 ]; then
      echo -e "`date +%F_%H:%M:%S`: ❌ Error running dump command: $status"
      exit $status
    fi
    gzip -n $TMP_FILE
    mv $TMP_FILE.gz $BACKUP_FOLDER/$DUMP_FILE_NAME.sql.gz
    DUMP_FILE_NAME=$BACKUP_FOLDER/$DUMP_FILE_NAME".sql.gz"
    echo "`date +%F_%H:%M:%S`: ✅ Created $DUMP_FILE_NAME"
    ;;
  mysql2)
    echo -e “`date +%F_%H:%M:%S`: 🕒 Running MySQL dump…”
    TMP_FILE=$( mktemp )
    mysqldump -u$USERNAME -p$PASSWORD $DATABASE > $TMP_FILE
    status=$?
    if [ $status -ne 0 ]; then
      echo -e “`date +%F_%H:%M:%S`: ❌ Error running dump command: $status”
      exit $status
    fi
    gzip -n $TMP_FILE
    mv $TMP_FILE.gz $BACKUP_FOLDER/$DUMP_FILE_NAME.sql.gz
    DUMP_FILE_NAME=$BACKUP_FOLDER/$DUMP_FILE_NAME".sql.gz"
    echo "`date +%F_%H:%M:%S`: ✅ Created $DUMP_FILE_NAME"
;;
  *)
    echo -e "Unknown adapter $ADAPTER"
    exit 1
esac

# Create /home/$USER folder archive

echo -e '\n'`date +%F_%H:%M:%S`': 🕒 Creating archive '$HOME_ARCHIVE_NAME'...'
tar -zcf $HOME_ARCHIVE_NAME --exclude={"/home/$USER/backups","/home/$USER/.eye/sock","/home/$USER/diaspora/log"} /home/$USER
status=$?
if [ $status -eq 0 ]; then
  echo -e "`date +%F_%H:%M:%S`: ✅ Successfully created archive $DATABASE_ARCHIVE_NAME."
  echo -e `date +%F_%H:%M:%S`': ✅ Successfully created archive '$HOME_ARCHIVE_NAME'.\n'
else
  echo -e "`date +%F_%H:%M:%S`: ❌ Error $status running tar. Archive wasn't created."
  exit 1
fi

# Borg backup
echo -e `date +%F_%H:%M:%S`': 🕒 Performing borg backup of '$BACKUP_FOLDER...'\n'
borg create -v --list --stats /mnt/storagebox/borgbackup/pod.thewalkingdeaf.net::`date +"%Y-%m-%d_%I-%M-%S"` $BACKUP_FOLDER

# Belt-and-braces duplicati backup

status=$?
if [ $status -eq 0 ]; then
  echo -e `date +%F_%H:%M:%S`': ✅ Successfully backed up.\n'
else
  echo -e `date +%F_%H:%M:%S`': ❌ Error '$status' running borg backup. Transfer wasn''t done.\n'
  exit 1
fi

echo -e `date +%F_%H:%M:%S`': 🕒 Copying archive files in '$BACKUP_FOLDER' to duplicati server...\n'
rsync -av $BACKUP_FOLDER/* $USER@10.0.0.5:/home/$USER/backups/pod-thewalkingdeaf-net

status=$?
if [ $status -eq 0 ]; then
  echo -e `date +%F_%H:%M:%S`': ✅ Successfully rsynced files to duplicati server.\n'
else
  echo -e `date +%F_%H:%M:%S`': ❌ Error '$status' running rsync. Transfer wasn''t done.\n'
  exit 1
fi

echo -e `date +%F_%H:%M:%S`': Removing contents of local '$BACKUP_FOLDER'...'
rm $BACKUP_FOLDER/*

echo -e `date +%F_%H:%M:%S`': Backup complete.'

exit $status

Example script output

/home/diaspora/backups/diaspora/backup.log

 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

/home/diaspora/diaspora

:2022-08-07_15:00:01 ℹ️  Creating diaspora* backup...

2022-08-07_15:00:01: 🕒 Running PostgreSQL dump...
2022-08-07_15:00:02: ✅ Created /home/diaspora/backups/diaspora/postgresql_diaspora_production_2022-08-07_15:00:01.sql.gz

2022-08-07_15:00:02: 🕒 Creating archive /home/diaspora/backups/diaspora/home-diaspora_2022-08-07_15:00:01.tgz...
tar: Removing leading `/' from member names
2022-08-07_15:01:30: ✅ Successfully created archive diaspora_pod.thewalkingdeaf.net_2022-08-07_15:00:01.tgz.
2022-08-07_15:01:30: ✅ Successfully created archive /home/diaspora/backups/diaspora/home-diaspora_2022-08-07_15:00:01.tgz.

2022-08-07_15:01:30: 🕒 Performing borg backup of /home/diaspora/backups/diaspora...

Creating archive at "/mnt/storagebox/borgbackup/pod.thewalkingdeaf.net::2022-08-07_03-01-30"
A /home/diaspora/backups/diaspora/postgresql_diaspora_production_2022-08-07_15:00:01.sql.gz
A /home/diaspora/backups/diaspora/home-diaspora_2022-08-07_15:00:01.tgz
d /home/diaspora/backups/diaspora
------------------------------------------------------------------------------
Archive name: 2022-08-07_03-01-30
Archive fingerprint: 2d08e5f65c17a6b58d9a9a887fbc273796836a363dd83403e1db94569fe98dab
Time (start): Sun, 2022-08-07 15:01:36
Time (end):   Sun, 2022-08-07 15:05:02
Duration: 3 minutes 26.17 seconds
Number of files: 2
Utilization of max. archive size: 0%
------------------------------------------------------------------------------
                       Original size      Compressed size    Deduplicated size
This archive:              580.62 MB            581.26 MB            581.26 MB
All archives:               12.82 GB             12.84 GB             10.23 GB

                       Unique chunks         Total chunks
Chunk index:                    4107                 5152
------------------------------------------------------------------------------
2022-08-07_15:06:00: ✅ Successfully backed up.

2022-08-07_15:06:00: 🕒 Copying archive files in /home/diaspora/backups/diaspora to duplicati server...

sending incremental file list
home-diaspora_2022-08-07_15:00:01.tgz
postgresql_diaspora_production_2022-08-07_15:00:01.sql.gz

sent 580,754,153 bytes  received 54 bytes  61,132,021.79 bytes/sec
total size is 580,612,149  speedup is 1.00
2022-08-07_15:06:09: ✅ Successfully rsynced files to duplicati server.

2022-08-07_15:06:09: Removing contents of local /home/diaspora/backups/diaspora...
2022-08-07_15:06:09: Backup complete.

The local /home/diaspora/backups/diaspora folder is cleared down after the last step.

Also, the duplicati backup (that runs on the half-hour) cleans up the folder on that host too.