I have a few questions about streaming replication.
We have a database of nearly 300 GB. We are using Postgres 8.3.1 and Slony for replication on Ubuntu 10.04.
We have 4 Postgres servers PGMaster1, PGSlave1, PGMaster2 and PGSlave2 on 2 distant sites (distance between the 2 sites is nearly 1000 kms).
PGMaster1 and PGSlave1 are in the site S1, and PGMaster2 and PGSlave2 are in the other site S2.
Using Slony, we are replicating as following :
PGSlave1 has subscribed as a slave of PGMaster1 (local replication in S1) PGMaster2 has subscribed as a slave of PGMaster1 (distant replication from S1 to S2) PGSlave2 has subscribed as a slave of PGMaster2 (local replication in S2; this is a cascaded replication)
All the SQL command UPDATE, INSERT and DELETE are sent to PGMaster1 and the data modifications are then propagated to the other servers by Slony. Read are load-balanced between the 4 servers
We often need to do switchovers between PGMaster1 and PGMaster2 (for maintenance operations, upgrade, ...).
We would like to upgrade to Postgres 9, and use the streaming replication of PG9
Question 1 : is it possible to have such a replication configuration with the streaming replication of PG9 (cascaded replication) ?
Question 2 : All the procedures I have seen describing a switchover between 2 PG servers require to copy (or rsync) the database from the new master (old slave) to the new slave (old master). Is it possible to do switchover between sites (between PGMaster1 and PGMaster2) whithout copying all the database from the new PG master to the new PG slave ? If it is not possible yet, shall it be possible in future releases ?