Promote a Database Replica

The database promotion feature allows you to demote a running database master. You can now change the size (volume or instance) of your database master or run a long migration on a replica and make it your new master. This feature comes with a few caveats. Read all sections carefully before you start a promotion:

• Use Cases
• Prerequisites
• How to Promote your Database Replica
• Post-Promotion Tasks
• More Information

Important: Do not initiate an application master takeover if you have initiated a database replica promotion. Initiating two major environment configurations simultaneously may leave your environment in an erroneous state. For example, the dashboard may show that an app slave has been successfully promoted when it has not. Wait until one takeover process has thoroughly completed before initiating a new one.

Use Cases

Recommended use-cases for replica promotion:

• Replacing a defective or unresponsive database master where suitable - see Important Note below.
• Changing your database instance size (by creating a replica of the preferred instance size and promoting it).
• Increasing the size of your database volume (by creating a replica with a larger /db volume and promoting it).
• (MySQL only) Performing long migrations on a replica, then replacing the current database master with the migrated replica.

You can not undo a database promotion. Make sure you are promoting a database for the right reasons. In general, do not use the database promotion feature as a solution to fix the following problems:

• High load - it is typically best to solve the cause of the load to prevent it from becoming a bigger concern later.
• Table locks
• InnoDB lock waits or dead locks
• A full disk (100% utilized) on the database master (contact support for assistance in resolving this issue without data loss)
• Swap

If you are unsure if a database replica promotion is right for you, open a ticket with Engine Yard Support.

Prerequisites

• You have an environment with a database master and at least one healthy database replica that you have designated for promotion.
• Your database replica should be current with the database master. We check for concurrency before initiating the promotion. See About Database Replica Promotion to understand our definition of current. To verify that the replica is current, execute the following commands:

  MySQL

  mysql -u root -e"SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master"

  PostgreSQL

  psql -U postgres -t -c "SELECT (now() - pg_last_xact_replay_timestamp()) AS time_lag;"
  

  Note: If your application is not very active it is possible that the pg_last_xact_replay_timestamp() is very old. One way to check this is to perform a small write on the master and re-check this value. The promotion process does use this "trick" to double check for replica lag and will not promote an unhealthy replica.
  Important Note: If your master instance is inaccessible at the time of promotion then this concurrency check is not possible and is skipped, resulting in potential data loss. Replication failure time can be seen via the Environment alerts. For failed master situations recovery paths other than promotion are the recommended first solution and Support should be contacted.
• Your application must be placed in maintenance mode to ensure that all traffic has stopped going to your application and database.

How to Promote your Database Replica

Engine Yard recommends that you perform these promotion and post-promotion tasks during your low-traffic hours or your typical maintenance window.

To promote your database replica:

1. Log in to your Engine Yard account and navigate to the relevant environment.
2. Cancel traffic to your app, workers or background and cron jobs (don't stop the environment). Then, enable your maintenance page using one of the following methods:
   • Navigate to the relevant environment, select Maintenance from the More Options area, and make the necessary configuration changes.
   • Use the ey web disable command on the Engine Yard CLI.
3. Verify that there are no remaining database connections. See Checking for Connections regarding how to verify the connection.
4. Click the Promote button associated with the database replica designated for promotion.

   

   A confirmation page is displayed.

5. Click the Promote Replica button and enter your Engine Yard password to proceed.

   Note: The default environment configuration has the Promotion Protection feature enabled, which requires you to re-enter your password.

   Your database master is being locked for writes and your replica is being promoted.

   

The promotion task is successful when you see that your database replica is now the new master. However, you must perform the post-promotion tasks to connect your application to the new database master.

You can see all promotion history by clicking the Promotions link in the More Options area of your environment.

Post-Promotion Tasks

These post-promotion tasks are critical for restoring functionality to your application. When you are ready, do the following:

1. Redeploy your application servers to re-generate connectivity information to the new database master.
2. Take your application out of maintenance mode (which will re-enable your application) using one of the following methods:
   • Navigate to the relevant environment, select Maintenance from the More Options area, and make the necessary configuration changes.
   • Use the ey web enable command on the Engine Yard CLI.

   Your application is live and available to your customers after you take it out of maintenance mode.

Note: The original database master and its replicas remain in your environment as a detached utility instance. You can delete this instance or open a ticket with Engine Yard Support to initiate a root cause analysis of the failed instance.

More information

This table provides other resources related to database promotion.

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.