Creating a Sandbox version of my application. DB replication? Or another way

I'm planning on creating a sandbox system of a production rails app for potential clients to mess around with.

The production system is continuo开发者_如何转开发usly being updated with new content which I'd like to bring over to the sandbox to make it as real as possible. However, I don't want to nuke the sandbox by doing a full db dump / restore as I'd like to preserve the user accounts on this system (just update with the new content)

I've checked out a couple of options but I could use some advice...

1- DB Dump / Restore

While it's simple in terms of how it works it's a bit of a pain to configure with cron jobs and transferring the whole db between servers even though I may not need it. I guess I can just dump certain tables but this will still probably transfer more data than required.

2- DB Replication

Setting up master-slave replication on a per table basis. From what I can see, this might be want I want to do. Replicate just the new content tables and ignore account tables.

3- Another way?

I'm not sure... is there a better way to be doing this?

But wait, there's more!

The sandbox system is not always running (it's only available during certain hours) so I need something that won't get too upset if it can't connect. Just quietly fail and try again later.

I'm pretty new to replication so does anyone have a recommandation of where to get started? Slony-I, Bucardo, rubyrep?

Details

• Rails 3 app
• PostgreSQL DB
• Ubuntu server

---

You should only do a dump/restore. A master/slave setup will not work because the slave will be in a read-only state. There are two ways you could do this:

1) Drop the database via the -c flag, then restore via psql:

pg_dump -c ... | psql some_host dba_user staging_db

2) Load in to a temporary database, then do a ALTER DATABASE ... RENAME TO.

pg_dump ... | psql -c some_host dba_user staging_db_tmp
# In psql while connected to template1 (and no one is connected to prod):
ALTER DATABASE staging_db RENAME TO staging_db_tmp;
ALTER DATABASE staging_db_tmp RENAME TO staging_db;

In either case, they're pretty easy to script. Look at the '-1' or '--single-transaction' command for psql.