From 691b08034b7f0eb263df1c6cdf709b8cfef5565b Mon Sep 17 00:00:00 2001
From: Ant Zucaro <azucaro@gmail.com>
Date: Thu, 19 Jan 2017 16:51:07 -0500
Subject: [PATCH] Add a script to create a data snapshot of the DB.

---
 scripts/create_snapshot.shl | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)
 create mode 100755 scripts/create_snapshot.shl

diff --git a/scripts/create_snapshot.shl b/scripts/create_snapshot.shl
new file mode 100755
index 0000000..2f07d1a
--- /dev/null
+++ b/scripts/create_snapshot.shl
@@ -0,0 +1,28 @@
+#!/bin/bash
+
+# This script is used to create data snapshots that don't disclose any
+# identifying player or server information.
+
+psql -U xonstat -h localhost xonstatdb <<EOF
+-- remove email addresses
+update players set email_addr = NULL where email_addr IS NOT NULL;
+
+-- sanitize the server hashkeys
+update servers set hashkey = server_id where hashkey IS NOT NULL;
+
+-- create a sequence to sanitize the player hashkeys
+create sequence sanitize_seq;
+
+-- sanitize all player hashkeys
+update hashkeys set hashkey = nextval('sanitize_seq');
+
+-- get rid of the sequence
+drop sequence sanitize_seq;
+EOF
+
+# create a backup of the database
+DATE=$(/bin/date +'%Y%m%d')
+
+# backup to a compressed file loadable by the "drop_and_load.shl" script
+pg_dump -c -U xonstat -h localhost -n xonstat --format=custom --compress=9
+xonstatdb > ${DATE}_xonstatdb_snapshot.dmp
-- 
2.39.2