Synchronize a postgresql database to a different user for demo purposes

I have found a rather nice way to "synchronize" a database to a new database owned by a different user and perform a minor update on it.

The background: WheelCMS has a demo version which runs on the same content as the "real" site but can be edited by visitors. This also means the content must be synchronized occasionally.

The requirements:

  • Full copy of all content (sql and media)
  • "wheeldemo" user must have access to the content
  • tables must be owned by "wheeldemo" database user

I've come up with the following script:

#!/bin/sh

sudo cp -a /srv/sites/www.wheelcms.io/wheelsite/media/* /srv/sites/demo.wheelcms.io/wheeldemo/media/
dropdb wheeldemo
createdb -O wheeldemo -T wheelsite wheeldemo
psql wheeldemo -c "reassign owned by wheelsite to wheeldemo"
psql wheeldemo -c "update auth_user set is_active=true where username='wheeldemo'"

 

The media copy is pretty obvious. Creating the database from the original by using the original as the template is a nice trick, and so is the simple "reassign" statement to change ownership without having to explicit list (or query) which tables/sequences, and so on.

Lastly, creating a new user with specific properties (in this case add it to a managers group) is tricky. So I've made sure the user already exists in the main database,  but is disabled. All that is left to do is to enable the user on the synced database.

 

 

Last updated Nov. 6, 2013, 9:58 a.m. | filed under django | django wheelcms postgresql
comments powered by Disqus