This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here.

I have a app that requires two databases –– one production and one for staging. Do I need 2 database servers, or should I use one server and simply keep them in different databases?

On the one hand, you can create a new server using Docker containers so easily. I've been wondering if keeping the data for two environments on the same database is a safe approach.

  • what is your uptime requirement? – Neil McGuigan Sep 8 '17 at 18:44

You need at least two. Don't mess around in production.

  • Seconded - mess around all you like in Dev, Test, UAT and Staging - keep Prod clear from everything and anything that could slow_it/bring_it down. – Vérace Jul 5 '14 at 23:05
  • Absolutely, never do anything in production besides production. Staging, QA, UAT, Dec, etc. can all be VMs, even if production is a physical server. – mrdenny Jul 6 '14 at 4:26

You need at least three servers in your scenario - Production, DR and Staging/UAT, all physically separate and sharing no infrastructure. Not even for basic services like DNS, LDAP, etc. Trust me on this.

A good strategy is to replicate Prod to DR, then backup DR to tape (offloading the work of the backup from Prod) then use that tape backup to refresh Staging, which gives you confidence that your tapes work and that your operators are very familiar with the restore process should it ever be needed. Every 6 months, swap Prod and DR around.

  • Please define DR for us. And not everyone needs a separate DR server, though I would also recommend it as a simple strategy, especially with streaming replication working out of the box. – Colin 't Hart Jul 6 '14 at 11:08
  • Disaster recovery also known as business continuity is having the ability to continue to operate even after the catastrophic failure of your main systems. Is that what you mean? IMHO if you have something that you call "production" then needing DR is a given. Or it's not "production" at all, just a toy. – Gaius Jul 6 '14 at 13:14
  • 1
    Ah, no. Besides the cost side - even in large businesses not every application may qualify for DR budgets. A "toy" may well be a secondary application that is not business critical. The larger the company the more likely there are a significant number of applications that just can go down for a day or two (backup instead of DR capabilities) without impacting anything.Production does not necessarily mean you can not deal with a couple of days downtime. – TomTom Sep 8 '17 at 7:59

I have a app that requires two databases –– one production and one for staging. Do I need 2 database servers, or should I use one server and simply keep them in different databases?

You can certainly run them on the same server without a problem. If you're afraid of taxing out IO, store the development database on a separate disk by using the TABLESPACE functionality. Create a tablespace on a different disk and run createdb -D myDevTablespace. The only way a single box could be a problem from there, is if you tax out the CPU (unlikely). But, if you're going to invest in CPUs, I would want them shared.

Every company I've ever been at has had staging/beta services run on the same database server -- developers still develop on their own boxes, but the forward facing test machines merely connect to a staging database.

  • 2
    Well, I have seen it all, from one system to two systems to full DR with separate hardware - it depends on the application. Generally this sounds like a smaller one - so I agree. – TomTom Sep 8 '17 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.