Syncing postgres dev and production databases...

[rwr....@gmail.com]

I'm very new to Mezzanine. Been working with it for about a week and a half now. And let me say, once you get your head around the structure and methodology, it's just an awesome framework! 
So nice to break away from the standard CMS offerings and gain full control.

I've setup a local dev environment and a remote Linode production server. it took a minute but I got deployment through Fabric worked out and it's now working well, and repeatable.
I'm using postgres as my database platform and the one thing that was giving me a bit if a fit is syncing the local and remote databases. I've  tried the django and fab bacup and restore
utilities but they are prone to a number of permissions issues. These could be resolved but it would require a change in my established project structure, or relatively extensive changes to
the dev and production environments or postgres servers. I wanted something that required fewer changes to the default environments. I finally came up with a method that is working well, at least for now.
It could no doubt be automated via a set of Fabric tasks. But i thought I'd share my "manual" method for those having the same issue. It may be a little unorthodox but it's working well, and is repeatable.

Here it s...

Keeping in mind that this will delete any db based content or user data on the remote db. But for initial development it's great! And I believe it could be easily modified to backup and restore only those tables required for the site structure and content.
I suppose you could also do this in reverse to sync the dev db to the production db for backup and to make sure you're developing and testing against real world data.

On development server:
sudo -u postgres pg_dump <db name> > dump.sql

sed -i -e 's/<development db role>/<production db role>/g' dump.sql (NOTE: This is only required if the local and remote db roles are not the same.)

scp ./dump.sql <remote user created during 'fab secure'>@<server IP>:<path to where the dump file should be placed on production server>/dump.sql

On production server: NOTE
dropdb -h localhost -U <db role> <db name>
sudo -u postgres createdb -O <role> <db name>
sudo -u postgres <db name> < <path of dump file on production server>dump.sql

I'm certain there are many other methods to get this don but this is what I came up with.
I've developed this under the conditions specific to my dev and production environments so any comments, suggestions or questions are more than welcome.

Cheers! :)

[Ken Bolton]

I want to re-emphasize that this should only be done before you are live in production, and then only once! Your customers will hate you if you blow away their prod database after they've started blogging.

The automated process goes in the other direction, allowing us to take a dump of production and migrate it back to qa, dev, and local. For that, I've created DEV_HOSTS and QA_HOSTS in settings.FABRIC and used `fab backup` to create the backup, a custom rsync command to move the file to the hosts, and a custom `fab restore`-like command to restore the database to dev and qa.

-ken

--
You received this message because you are subscribed to the Google Groups "Mezzanine Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mezzanine-users+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rwr....@gmail.com]

Absolutely Ken. What I've posted is only for initial development.

I'm looking into your production backup solution now. Thanks for the info!

To unsubscribe from this group and stop receiving emails from it, send an email to mezzanine-use...@googlegroups.com.

[Rick Reynolds]

Noticed an problem in my script.


The last line should be changed from...

sudo -u postgres <db name> < <path of dump file on production server>dump.sql

To...

sudo -u postgres psql <db name> < <path of dump file on production server>dump.sql