Taken from Joey Rivera's tutorial on Phing in the December 2013 issue of Web & PHP Magazine. For more info, see http://webandphp.com/

gistfile1.xml

<?xml version="1.0" encoding="UTF-8"?>

<project name="DB Post Deploy Restore" default="run">
	<property file="properties" />
	
	<!-- find out what db's need to be restored and loop through the process for each -->
	<target name="run">
		<input propertyname="production.user">(Required) Please enter your ssh user name:</input>
		
		<foreach list="${dbs}" param="db" target="prepare" />
	</target>
	
	<target name="prepare">
		<property file="${db}.properties" />
        <property name="dump.path" value="${dump.folder}${dump.file}" />

        <echo msg="Cleaning the  environment" />
        <delete dir="${dump.folder}" includeemptydirs="true" verbose="false" failonerror="false" />
        <mkdir dir="${dump.folder}" />
        <exec command="chmod 775 ${dump.folder}" checkreturn="true" />
        <echo msg="Fetching DB backup" />

		<echo>Getting latest db backup</echo>
		<exec command="scp ${production.user}@${production.db.host}:${production.db.backup} ${dump.path}" checkreturn="true" outputProperty="output.dump" />
        <exec command="chmod 775 ${dump.path}" />
		
		<!-- turn off all sites using postgres so we don't get traffic that will break the restore process -->
		<foreach list="${sites}" param="site" target="sites.off" />
		
		<!-- kick people off the server -->
		<echo>Restarting db service</echo>
		<exec command="ssh ${production.user}@${db.host} 'sudo /sbin/service postgresql restart'" checkreturn="true" outputProperty="output.restart" logoutput="true" />
		
        <foreach list="${db.to.restore}" param="env" target="restore" />
        
        <!-- now lets turn the sites back on -->
		<foreach list="${sites}" param="site" target="sites.on" />
    </target>
    
    <target name="sites.off">
    	<!-- add maintenance file -->
    	<echo>Adding maintenance file to ${site}/${maintenance.file}</echo>
    	<touch file="${site}/${maintenance.file}" />
    	
    	<echo>Turning off site ${site}</echo>
		<exec command="${phpctl.folder} stage php stop ${site}" checkreturn="true" outputProperty="output.sites.${site}.off" logoutput="true" />
    </target>

	<target name="sites.on">
		<echo>Turning on site ${site}</echo>
		<exec command=" ${phpctl.folder} stage php start ${site}" checkreturn="true" outputProperty="output.sites.${site}.on" logoutput="true" />
		
		<echo>Removing maintenance file ${site}/${maintenance.file}</echo>
		<delete file="${site}/${maintenance.file}" />
	</target>
	
    <target name="restore">
        <echo>${env}</echo>

        <property name="host" value="${db.host}" />
        <property name="name" value="${${env}.db.name}" />
        <property name="user" value="${${env}.db.user}" />

        <exec command="psql -h ${host} -U ${user} -d ${db.login.default} -c 'drop database ${name}'" checkreturn="true" outputProperty="output.delete" />
        <echo msg="${output.delete}" />

        <if>
                <equals arg1="${env}" arg2="staging" />
                <then>
                        <exec command="psql -h ${host} -U ${user} -c 'create database ${name} owner ${db.group}' -d ${db.login.default}" checkreturn="true" outputProperty="output.create" />
                        <echo msg="${output.create}" />

                        <exec command="pg_restore -h ${host} -U ${user} -d ${name} ${dump.path}" checkreturn="true" outputProperty="output.restore" />
                        <echo msg="DB restored" />
                </then>
        <else>
                <exec command="psql -h ${host} -U ${user} -c 'create database ${name} owner ${db.group} template ${db.template}' -d ${db.login.default}" checkreturn="true" outputProperty="output.create" />
                <echo msg="${output.create}" />
        </else>
        </if>

        <!-- Now check if we need to manipulate data -->
        <available file="${name}.sql" property="script.found" type="file" filepath="${script.folder}" />
        <if>
                <equals arg1="${script.found}" arg2="true" />
                <then>
                        <property name="script.path" value="${script.folder}${name}.sql" />

                        <echo msg="Script found for ${name}, executing" />
                        <exec command="psql -h ${host} -U ${user} -f ${script.path} ${name}" checkreturn="true" outputProperty="output.file" />
                        <echo msg="${output.file}" />
                </then>
        </if>
    </target>
</project>

gistfile2.txt

// the properties file looks something like this...

dbs=myfirstdb,anotherdb
dump.folder=/path/to/dump/folder/
script.folder=/scripts/to/run/on/individual/db/
phpctl.folder=/bin/ctl.ksh
maintenance.file=MAINT.MODE
db.host=10.10.10.10
db.login.default=dbuser
production.db.host=10.10.10.11