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.
- 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:
sudo cp -a /srv/sites/www.wheelcms.io/wheelsite/media/* /srv/sites/demo.wheelcms.io/wheeldemo/media/
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.