PostgreSQL – Is Shared Storage Safe if All Servers Are Read-Only?

I'm going to highlight one of the products listed in the wiki link provided by Jack Douglas.

Galera is a product I've been hearing a lot about recently, but have not had a cause to implement it yet into a production environment.

Back in July, Percona wrote a brief blog about implementing it, but mentioned there was no 'production' release at that time.

They've since released the first major version, v1.0. And Vladimir has been shedding more light on it this month. And with some benchmarks

Due to your write-heavy environment, I highlight this use-case:

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

_{Disclaimer: I'm not affiliate with Galeria or Percona, and have not used it personally...yet.}

Before you set it to read_only=0 and point connections to the Slave (new master) run a

show master status;

and take a note with those numbers.

When you recovered your master server you can run

CHANGE MASTER TO MASTER_HOST=myslavehost.example.com, MASTER_LOG_FILE=[take it from your notes],MASTER_LOG_POS=[take it from your notes], MASTER_USER='replication_user', MASTER_PASSWORD='replication_password';
START SLAVE;

Then the old master should start to replicate from the new master and catch up.

A couple of things to keep in mind:

• Make sure your expire_logs_days settings is set to more days than your recovery takes otherwise you would lose the binlogs and you need to set up the old master from scratch

• Make sure you know the replication user and password. If you don't you can recover it from:

  • the repository table (in case of master_info_repository = TABLE)

    select User_name, User_password from mysql.slave_master_info;

  • the master.info file (5th and 6th line)

• If your master has crashed chances are your slave is a bit behind so you will have some autoincrement conflict after you set the master back to replicate from the slave. pt-table-sync can you remedy that situation.