Postgresql – Should the Postgres backup server be as beefy (IO, processor, RAM) as the master

backuppostgresqlreplicationUbuntu

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.

Best Answer

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.

Related Question