Cron job on postgres server to backup sqlfiddle database to s3. Closes jakefeasel/sqlfiddle2/#10

5 files changed, 49 insertions, 8 deletions

diff --git a/README.md b/README.md
index 55f76ed..680311b 100644
--- a/README.md
+++ b/README.md
@@ -46,4 +46,11 @@ To run the commercial database software (Microsoft SQL Server 2014 Express, Orac
 
 ### Oracle 11g R2 XE
 1) "system" password is "password"
-2) Follow instructions in vagrant_scripts/idm_bootstrap.sh for details on what must be done to obtain and use the JDBC driver
\ No newline at end of file
+2) Follow instructions in vagrant_scripts/idm_bootstrap.sh for details on what must be done to obtain and use the JDBC driver
+
+
+## Running on AWS
+
+With a bit of preparations, you should be able to deploy the whole app into Amazon Web Services. See the comments in Vagrantfile for example config that you can fill in with your own AWS account details. For MS SQL and Oracle support, you will have to create your own AMI with Windows, following the same steps mentioned above.
+
+You may also wish to have automated backups of your sqlfiddle database to S3. If so, you will need to add a .s3cfg file under vagrant_scripts. This file will not be added to the git repo (it is ignored) but if present the PostgreSQL server will automatically schedule backups to write to your account. The .s3cfg file is produced by "s3cmd" - check this site for more details: http://s3tools.org/s3cmd-howto
\ No newline at end of file
diff --git a/Vagrantfile b/Vagrantfile
index 323587a..e18e746 100644
--- a/Vagrantfile
+++ b/Vagrantfile
@@ -83,6 +83,8 @@ Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
         'Ebs.VolumeType' => 'io1',
         'Ebs.Iops' => 500
       }]
+
+      override.vm.provision :shell, :path => "vagrant_scripts/pg93_aws.sh"
     end
 
     postgresql93.vm.provision :shell, :path => "vagrant_scripts/pg93_bootstrap.sh"
diff --git a/vagrant_scripts/.gitignore b/vagrant_scripts/.gitignore
index 0d13a98..cbbea4b 100644
--- a/vagrant_scripts/.gitignore
+++ b/vagrant_scripts/.gitignore
@@ -1 +1,2 @@
-openvpn
\ No newline at end of file
+openvpn
+.s3cfg
\ No newline at end of file
diff --git a/vagrant_scripts/pg93_aws.sh b/vagrant_scripts/pg93_aws.sh
new file mode 100755
index 0000000..9850923
--- /dev/null
+++ b/vagrant_scripts/pg93_aws.sh
@@ -0,0 +1,28 @@
+#!/bin/bash
+
+if [ -e "/vagrant/vagrant_scripts/.s3cfg" ]
+then
+    apt-get --yes --force-yes install s3cmd
+
+    chmod 600 /vagrant/vagrant_scripts/.s3cfg
+    cp /vagrant/vagrant_scripts/.s3cfg ~
+
+
+#########################################
+cat << EOF > ~/backup.sh
+
+# nice -n 19 makes these backups be as low priority as possible, so as to not interfere as much with the DB process
+nice -n 19 pg_dump -U postgres sqlfiddle | nice -n 19 gzip > sqlfiddle.sql.gz
+s3cmd --no-progress --force put sqlfiddle.sql.gz s3://sqlfiddle/sqlfiddle.sql-tmp.gz
+s3cmd --no-progress --force cp s3://sqlfiddle/sqlfiddle.sql-tmp.gz s3://sqlfiddle/sqlfiddle.sql.gz
+s3cmd --no-progress --force del s3://sqlfiddle/sqlfiddle.sql-tmp.gz
+
+EOF
+#########################################
+
+    chmod +x ~/backup.sh
+
+    # run backups every four hours
+    echo "0 */4 * * *       /root/backup.sh >> /root/backup.out 2>&1" | crontab
+
+fi
\ No newline at end of file
diff --git a/vagrant_scripts/pg93_bootstrap.sh b/vagrant_scripts/pg93_bootstrap.sh
index 0899af1..6214f9d 100755
--- a/vagrant_scripts/pg93_bootstrap.sh
+++ b/vagrant_scripts/pg93_bootstrap.sh
@@ -1,5 +1,13 @@
 #!/bin/bash
 
+# create a 512mb swapfile
+dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
+chown root:root /swapfile1
+chmod 0600 /swapfile1
+mkswap /swapfile1
+swapon /swapfile1
+echo "/swapfile1 none swap sw 0 0" >> /etc/fstab
+
 export LANGUAGE="en_US.UTF-8"
 export LANG="en_US.UTF-8"
 export LC_ALL="en_US.UTF-8"
@@ -8,7 +16,7 @@ echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" > /etc/apt
 wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
 apt-get --yes update
 apt-get --yes upgrade
-apt-get --yes --force-yes install postgresql-9.3 postgresql-contrib-9.3
+apt-get --yes --force-yes install postgresql-9.3 postgresql-contrib-9.3 s3cmd
 
 pg_dropcluster --stop 9.3 main
 echo "listen_addresses = '*'" >> /etc/postgresql-common/createcluster.conf
@@ -32,8 +40,3 @@ psql -U postgres sqlfiddle < /vagrant/src/main/resources/db/sqlfiddle/data.sql
 # initialize the openidm repository
 psql -U postgres < /vagrant/src/main/resources/db/openidm/createuser.pgsql
 psql -U openidm < /vagrant/src/main/resources/db/openidm/openidm.pgsql
-
-if ping -c 1 10.0.0.113 &> /dev/null
-then
-    psql -U postgres sqlfiddle < /vagrant/src/main/resources/db/sqlfiddle/migrate.sql
-fi
\ No newline at end of file