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

Question

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

The production system is continuously 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

Can you isolate the user account tables in a different schema? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented May 18, 2011 at 22:25
@Catcall hmm... maybe, but I'd rather not have to make big schema changes as the sandbox is just a duplicate of the production app. Then again if I made the changes to both DBs... — Ganesh Shankar
– Ganesh Shankar, Commented May 19, 2011 at 0:25

Sean · Accepted Answer · 2011-05-18 17:39:21Z

1

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.

answered May 18, 2011 at 17:39

Sean

10.3k4 gold badges42 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related