Streaming Replication on a Single Database
I have an environment set up like so: There is one primary server that has one database, and one backup server that has two databases - a database intended as a warm backup to the primary server, and a second database for use as a training database.
I want to set up a system where that's possible - having a db on the primary server, and a db on the backup server, and them being just about the same because the db on the backup needs to be a warm backup of the primary db.
However, I want the backup db and the primary db to be synced, WITHOUT losing any of the data for the training db. I know that doesn't work with log based file shipping, because that syncs two postgres systems, not two databases.
Does streaming replication make this possible?
I believe you can do what you are trying to do with streaming replication, though I'd have to try it. A second option you could try is to run two instances of Postgres on the backup machine....one as the streaming replication target and the other as your training database. It isn't ideal, but it should work.
Note that the OP was looking for a solution 2 years, 3 months, 4 weeks and 1 day ago.
\____________________ ____.-.____ ____________________/
\_____________\ -._)!(_.- /_____________/
\_______\. ~\ /~ ./_______/
Wow. I should have caught that. I was mistaken to begin with. You can do what he is wanting with Slony, but not with Streaming Replication.