Streaming Replication on Two Postgres Systems?

Question

There's two servers, a Primary Server and a Backup Server. Both have Postgres installed. The Primary Server has one database, and the Backup Server has two - one for a warm backup/standby of the Primary Server's database, and another for Training and whatnot.

I need a way to maintain a warm standby on a single database, the Backup Database, without using some sort of log-shipping method that will overwrite all the data on the Training Database.

I'm thinking about trying to use Streaming Replication. What I need to know is if I will be able to keep up a warm backup of the Primary Database on one database, without overwriting the Training Database on the machine.

Answer 1

Streaming Replication will only replicate your entire cluster, and not a single database. It will also make the slave database read only, so you can't use it as your "Training Database".

If you need to do that, you need to look at something like Slony, Bucardo or Londiste.

However, those are much more complex to set up and maintain. Another option is to run two completely different clusters ("instances") on the slave - one that is the slave and one that is the training. They'll then run on different ports, with different memory pools, etc, etc. It's not great for performance to do that, but if it's just a training database that may not matter, and it's a much easier replication system to work with.