Here is how I copied a postgresql database from one Webfaction server to another. My original thought was to do this using Fabric. But on Webfaction, they do not recommend creating or dropping databases from the command line. Instead they recommend using the Webfaction Control Panel.
There are posts on Stackoverflow for copying a database without writing it to an intermediate file. I chose to not do that. The command for dumping the database was:
pg_dump -C -U username database_name > output_file
Next, I copied the file to the other server. After that, I created the new database on the new server using the Webfaction Control Panel. I named the new database the same name as the old. I am not sure if this is necessary. I did not run the Django command syncdb.
Then I ran:
psql -U username -d database_name -f file_name
Fabric
Getting it to work from Fabric was a little tricky because you need to get Fabric to talk to two different servers. The trick involved using the Fabric hosts decorator:
@hosts('webxxx.webfaction.com')
def copy_from():
your code here
Then run that function using “execute”:
def copy_db():
r = execute(copy_from) # execute returns a dict
return_value = r['webxxx.webfaction.com']
your code here