Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've set up a Postgres database on a server with 2 CPU cores, 4GB of RAM and an SSD volume (on top of RAID 10 of course). This is our (sort-of) "beefy" production database server (I can easily add more RAM, etc. when the time comes).

Now it's time to set up a backup Postgres server (not used for reads or anything else, just strictly a replication/backup). I don't want to have to fork out another $150/mo for the same exact setup for my backup server, and I know that a lot of the server resources used for a production database have to do with complicated queries, sequential scans, etc., so my thinking was that I could get away with a 512MB server with about 1/6 the I/O performance, and 1 CPU core.

Is that thinking correct, or does a replication/backup Postgres server typically need nearly the same specs as a production Postgres server?

Note: I'm using Postgres 9.2.4 with streaming (non-synchronous) replication on an Ubuntu 12.04.2 server.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

It depends a great deal on your workload - the write volumes and write patterns on the master.

The standby is essentially doing continuous crash recovery. It reads write-ahead logs containing the changes the master made to the tables and applies that to its own tables. It does this using a single worker, so it doesn't benefit significantly from I/O subsystem concurrency.

The main thing a replica server needs is good random I/O performance. It doesn't need fast fsync() as much as the master does, unless you plan to fail over to it.

I'd start with a small machine, knowing that it's always easy to upgrade. If you do this, it's vital that you carefully monitor replication to make sure the standby isn't falling too far behind, or you make sure you have WAL archiving set up with abundant space to allow the standby to cope if it falls too far behind the master. Otherwise your replica will fall far enough behind that the master no longer has a record of all the changes required to get from where the standby is to the current master's state, and you have to re-initialise the standby. If you're using it for backups, you don't want this to happen.

share|improve this answer
    
Thanks, man. That makes 3 times you've been a terrific help. –  orokusaki Jun 17 '13 at 1:03
    
@orokusaki Yeah, Craig tends to be a terrific help quite often :) You must be aware of the half sentence 'unless you plan to fail over to it'. If you decide to do this at a later point you will have to beef up the slave to about the same level the master is. –  dezso Jun 17 '13 at 6:36
    
@dezso - thanks. I do intend to fail over to this additional server, but to do so manually (so I'll be able to upgrade the server and move the data into an SSD volume in about 30 min). It's not perfect, but it's I worry about all the things that can go wrong with fully automated failover. Down the road I'll automate the process when I have the resources and knowledge to do so comfortably, and when I have the mullah to run identical master/slave servers (could get expensive as I grow). –  orokusaki Jun 17 '13 at 12:43
    
@orokusaki You're right to worry about all the things that can go wrong with fully automated failover. IMO using fully automated failover is crazy unless you do it as a routine part of your operations like Netflix does, forcing failovers during day-to-day operations. Otherwise it will hurt you when that under-tested fail-over script triggers. –  Craig Ringer Jun 17 '13 at 23:01
    
That makes sense, and I'm ultra paranoid about data loss. –  orokusaki Jun 18 '13 at 1:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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