Merge pull request alphagov#134 from alphagov/add-postgres-sync-command

elliotcm · elliotcm · commit 2a2a5f22fe41 · 2015-06-09T14:59:11.000+01:00
Add a postgres sync command
diff --git a/README.md b/README.md
@@ -73,3 +73,15 @@ and ensure that you do not have the following option anywhere in your
 `~/.ssh/config`:
 
     ForwardAgent yes
+
+## Syncing postgres machines
+
+An example
+
+`fab <env> -H '<src_db>' postgresql.sync:<db_name>,<dst_db> -A`
+
+the -A must be specified to forward the agent
+
+This will sync the specified database `<db_name>` from the machine with the
+hostname of `<src_db>` to the machine with hostaname `<dst_db>`. It will destroy
+data on the destination db
diff --git a/fabfile.py b/fabfile.py
@@ -30,6 +30,7 @@
 import nagios
 import nginx
 import ntp
+import postgresql
 import puppet
 import rabbitmq
 import rkhunter
diff --git a/postgresql.py b/postgresql.py
@@ -0,0 +1,16 @@
+from fabric.api import task, sudo, env, settings, run
+from fabric.utils import abort
+
+@task
+def sync(database, dest_machine):
+    # the agent forwarding is required for the ssh command to the destination
+    # postgres server to work - this saves copying a temporary file down and
+    # back up again. We cant use fabrics sudo context manager because the
+    # command after the pipe should not run as the postgres user.
+    # The combination of "--schema public" and "--clean" in the pg_restore
+    # command clears all existing tables in the public schema before restoring
+    # the tables and data
+    with settings(forward_agent=True):
+        run('sudo -iupostgres pg_dump -Fc {0} | ssh {1} '
+            '"sudo -upostgres pg_restore --clean --single-transaction '
+            '--schema public --dbname {0}"'.format(database, dest_machine))
