Search code examples
sqlgoogle-cloud-platformgoogle-cloud-sqlblue-green-deployment

SQL Database Blue / Green deployments

I am dealing with the infrastructure for a new project. It is a standard Laravel stack = PHP, SQL server, and Nginx. For the PHP + Nginx part, we are using Kubernetes cluster - so scaling and blue/green deployments are taken care of.

When it comes to the database I am a bit unsure. We don't want to use Kubernetes for SQL, so the current idea is to go for Google Cloud SQL managed service (Are the competitors better for blue/green deployment of SQL?). The question is can it sync the data between old and new versions of the database nodes?

Let's say that we have 3 active Pods and at least 2 active database nodes (and a load balancer).

So the standard deployment should look like this:

  1. Pod with the new code is created.
  2. New database node is created with current data.
  3. The new Pod gets new environment variables to connect to the new database.
  4. Database migrations are run on the new database node.
  5. Health check for the new Pod is run, if it passes Pod starts to receive traffic.
  6. One of the old Pods is taken offline.

It should keep doing this iteration until all of the Pods and Database nodes are replaced.

The question is can this work with the database? Let's imagine there is a user on the website that is using the last OLD database node to write some data and when switched to the NEW database node the data are simply not there until the last database node is upgraded. Can they be synced behind the scenes? Does Google Cloud SQL managed service provide that?

Or is there a completely different and better solution to this problem?

Thank you!

Solution

  • I'm not 100% sure if this is what you are looking for, but for my understanding, Cloud SQL replicas would be a better solution. You can have read replicas [1], that are a copy of the master instance and have different options [2]

    A read replica is a copy of the master that reflects changes to the master instance in almost real time. You create a replica to offload read requests or analytics traffic from the master. You can create multiple read replicas for a single master instance.

    or a failover replica [3], that in case the master goes down, the data continue to be available there.

    If an instance configured for high availability experiences an outage or becomes unresponsive, Cloud SQL automatically fails over to the failover replica, and your data continues to be available to clients. This is called a failover.

    You can combine those if you need.