Minetest backup postgresql->sqlite w/ dedupe optimization

backup-excerpt.sh

#!/bin/sh
set -ex

(
	set -ex

	rsync -rlt -f 'H /restart' -f 'H /online' --delete-after /src/worlds/world/ /tmp/backup/

	cd /tmp/backup

	rm -rf profiler

	# Encrypted backup contains full auth; public copy has password hashes cleared.
	gpg --import /static/pgpkey.txt ||:
	time gpg --encrypt --armor --trust-model always -r "$PGPID" <auth.sqlite >auth.sqlite.asc
	time sqlite3 auth.sqlite 'update auth set password = null; vacuum'

	# Instead of relying on built-in migration, try to do a simple upsert
	# on top of any past snapshot, to try to make minimal incremental
	# changes in a single pass.
	rm -f map.sqlite
	cp /backup/map.sqlite map.sqlite ||:
	(
		echo 'begin transaction;'
		echo 'create table if not exists blocks (pos int primary key, data blob);'
		pg_dump -p 5432 -h pgsql -U "$PGUSERNAME" -w -a -t public.blocks "$PBDATABASE" |\
		perl -ne '
			chomp;
			my($x, $y, $z, $hex) = split("\t");
			$hex or next;
			print "update blocks set data=X'\''" . substr($hex, 3) . "'\''"
				. " where pos=" . (16777216 * $z + 4096 * $y + $x)
				. " and data<>X'\''" . substr($hex, 3) . "'\'';\n";
			print "insert or ignore into blocks values("
				. (16777216 * $z + 4096 * $y + $x)
				. ",X'\''" . substr($hex, 3) . "'\'');\n";
		'
		echo 'commit;'
	) | time sqlite3 map.sqlite

	rm -f players.sqlite
	time minetestserver --migrate-players sqlite3 --world .

	grep -v '^pgsql_' <world.mt |\
	sed 's/backend = postgresql/backend = sqlite3/' >new.mt &&\
	mv new.mt world.mt

	# May contain PII
	rm -f ipban.txt

	# Keep backup clean of journal files
	find . -type f -name '*.sqlite' | while read X; do
		sqlite3 "$X" 'PRAGMA journal_mode=delete;'
	done
	
	# Apparently mounting individual files inside another bind mount
	# causes docker to do insane things, so mount them separately
	# and compose them manually.
	( cd /motd && tar cf - . ) | tar xf -

	time /mapper/minetestmapper \
		--drawalpha \
		--colors /static/colors.txt \
		--geometry -992:-992+2000+2000 \
		-i . \
		-o map.png \
		--min-y -64 \
		--max-y 1024 \
		--bgcolor \#000000
	time optipng -o 1 -strip all map.png
) 2>&1 |\
while read X; do
	echo "[`date --iso-8601=seconds`] $X"
done >/tmp/log-$$.txt

mv /tmp/log-$$.txt /tmp/backup/backup-log.txt

time rsync -rlt --delay-updates --delete-delay /tmp/backup/ /backup/