Postgresql – Push replicas from master to slave in postgresql

A ring can be a very good idea under certain conditions

For servers A, B, C

Server A

• Master of B
• Slave of C

Server B

• Master of C
• Slave of A

Server C

• Master of A
• Slave of B

All three servers have 3 databases (db1, db2, and db3)

Here is the only setup that protects the three databases

• restrict all reads and writes to db1 on Server A
• restrict all reads and writes to db2 on Server B
• restrict all reads and writes to db3 on Server C

Replication would do the following in respone

• backup database db1 by replicating to db2 and db3
• backup database db2 by replicating to db3 and db1
• backup database db3 by replicating to db1 and db2

IMHO performing writes (INSERTs,UPDATEs,DELETEs) for db1 to server A,B,C makes all auto_increment values separate and distinct. This would make it brittle to restore data to other boxes.

More to come...

UPDATE

I have another crazy idea

Have you ever heard of a star topology? I posted stuff earlier on this subject

• https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
• https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

Create a master with two slaves

For servers A, B, C

Server A

• Master of B and C
• All tables use storage engine BLACKHOLE

Server B

• Slave of A
• All user tables use storage engine InnoDB

Server C

• Slave of A
• All user tables use storage engine InnoDB

Benefits

• Post all writes (INSERTs, UPDATEs, DELETEs) to serve A
• Writes should be fast becasue BLACKHOLE tables map to /dev/null
• Replication actual posts real data on Slaves (Server B and Server C)
• no actual data will reside in server A, only binary logs
• All SELECTs can go between Server B and Server C via a Load Balancer
• Slaves would virtually be identical into terms of content

Drawbacks

• Once you set this up you cannot freely use ALTER TABLE statements
• If replication breaks at Server A, it stops updating the Slaves

Since "native" MySQL uses asynchronous replication, there is no system-wise way to guarantee nor force a slave to apply a master's statement at any given time.

Semi-sync replication shortens this lag time by at least ensuring your statement is recognized by the slave -- but does not go as far as making sure it has been applied on your slave.

This is a common issue. Ultimately, your application needs to be replication-aware, such that your query "knows" it is being executed on master or on slaves. In your case, the common solution is to make sure a SELECT query which immediately follows a write operation is executed on master.

You may choose to use synchronous solutions: MySQL NDB Cluster or Galera Replication. Even with Galera you would need a special configuration to make sure a consistent write-and-read is successful.