Working with PostgreSQL and MySQL Read Replicas

Amazon RDS uses the MySQL and PostgreSQL (version 9.3.5 and later) DB engines' built-in
replication functionality to create a special type of DB instance called a Read Replica from
a source DB instance. Updates made to the source DB instance are asynchronously copied to
the Read Replica. You can reduce the load on your source DB instance by routing read queries
from your applications to the Read Replica. Read Replicas allow you to elastically scale out
beyond the capacity constraints of a single DB instance for read-heavy database
workloads.

Note that the information in this topic applies to creating Amazon RDS Read Replicas, either in
the same region as the source DB instance, or in a separate region for MySQL Read Replicas.
This topic does not apply to setting up replication with an instance that is running on an
Amazon EC2 instance or that is on-premises.

When you create a Read Replica, you first specify an existing DB instance as the source.
Then, Amazon RDS takes a snapshot of the source instance and creates a read-only instance from
the snapshot. Amazon RDS then uses the asynchronous replication method for the DB engine to
update the Read Replica whenever there is a change to the source DB instance. The Read
Replica operates as a DB instance that allows only read-only connections; applications can
connect to a Read Replica the same way they would to any DB instance. Amazon RDS replicates all
databases in the source DB instance.

Amazon RDS sets up a secure communications channel between the source DB instance and a
Read Replica. Amazon RDS establishes any AWS security configurations, such as adding
security group entries, needed to enable the secure channel. MySQL DB instances use public
key encryption between the source DB instance and the Read Replica. PostgreSQL DB instances
use a secure connection that you can encrypt by setting the ssl
parameter to 1 for both the source and the replica instances.

Amazon RDS Read Replica Overview

There are a variety of scenarios where deploying one or more Read Replica for a given
source DB instance might make sense. Common reasons for deploying a Read Replica include
the following:

Scaling beyond the compute or I/O capacity of a single DB instance for
read-heavy database workloads. This excess read traffic can be directed to one
or more Read Replicas.

Serving read traffic while the source DB instance is unavailable. If your
source DB instance cannot take I/O requests (for example, due to I/O suspension
for backups or scheduled maintenance), you can direct read traffic to your Read
Replica(s). For this use case, keep in mind that the data on the Read Replica
might be "stale" because the source DB instance is unavailable.

Business reporting or data warehousing scenarios where you might want business
reporting queries to run against a Read Replica, rather than your primary,
production DB instance.

By default, a Read Replica is created with the same storage type as the source DB
instance. However, you can create a Read Replica that has a different storage type from
the source DB instance based on the options listed in the following table.

Source DB Instance Storage Type

Source DB Instance Storage Allocation

Read Replica Storage Type Options

PIOPS

100 GB - 3 TB

PIOPS | GP2 | Standard

GP2

100 GB - 3 TB

PIOPS | GP2 | Standard

GP2

Less than 100 GB

GP2 | Standard

Standard

100 GB - 3 TB

PIOPS | GP2 | Standard

Standard

Less than 100 GB

GP2 | Standard

Amazon RDS does not support circular replication. You cannot configure a DB instance
to serve as a replication source to an existing DB instance; you can only create a new
Read Replica from an existing DB instance. For example, if MyDBInstance replicates to
ReadReplica1, you cannot configure ReadReplica1 to replicate back to MyDBInstance. From
ReadReplica1, you can only create a new Read Replica, such as ReadReplica2.

Differences Between PostgreSQL and
MySQL Read Replicas

Because the PostgreSQL and MySQL DB engines implement replication differently,
they have several significant differences you should know about:

Feature/Behavior

PostgreSQL

MySQL

What is the replication method?

Physical replication.

Logical replication.

How are transaction logs purged?

PostgreSQL has a parameter, wal_keep_segments,
that dictates how many Write Ahead Log (WAL) files are kept to
provide data to the Read Replicas. The parameter value species
the number of logs to keep.

Amazon RDS won't delete any binary logs that have not been
applied.

Can a replica be made writable?

No. A PostgreSQL Read Replica is a physical copy and
PostgreSQL doesn't allow for a Read Replica to be made
writeable.

Yes. You can enable the MySQL Read Replica to be writable.

Can backups be performed on the replica?

Yes, you can create a snapshot of a PostgreSQL Read Replica,
but you cannot enable automatic backups.

Yes. You can enable automatic backups on a MySQL Read
Replica.

Can you use parallel replication?

No. PostgreSQL has a single process handling replication.

Yes. MySQL version 5.6 allows for parallel replication
threads.

PostgreSQL Read Replicas (version 9.3.5 and
later)

Amazon RDS PostgreSQL 9.3.5 and later uses PostgreSQL native streaming replication to
create a read-only copy of a source (a "master" in Postgres terms) DB instance. This
Read Replica (a "standby" in Postgres terms) DB instance is an asynchronously created
physical replication of the master DB instance. It is created by a special connection
that transmits WAL data between the source DB instance and the Read Replica where
PostgreSQL asynchronously streams database changes as they are made.

PostgreSQL uses a "replication" role to perform streaming replication. The role is
privileged, but, can not be used to modify any data. PostgreSQL uses a single process
for handling replication.

Creating a PostgreSQL Read Replica does not require an outage for the master DB
instance. Amazon RDS sets the necessary parameters and permissions for the source DB
instance and the Read Replica without any service interruption. A snapshot is taken of
the source DB instance and this snapshot becomes the Read Replica. No outage occurs when
you delete a Read Replica either.

You can create up to five Read Replicas from one source DB instance. For replication
to operate effectively, each Read Replica should have the same amount of compute and
storage resources as the source DB instance. If you scale the source DB instance, you
should also scale the Read Replicas.

Amazon RDS will override any incompatible parameters on the Read Replica if it
prevents the Read Replica from starting. For example, if the
max_connections parameter value is higher on the source DB instance
than on the Read Replica, Amazon RDS will update the parameter on the Read Replica to be
the same value as that on the source DB instance.

Here are some important facts about PostgreSQL Read Replicas:

You can create PostgreSQL Read Replicas only in the same region as the source DB
instance.

You can promote a PostgreSQL Read Replica to be a new source DB instance. Note that the Read
Replica does not become the new source DB instance automatically. The Read
Replica, when promoted, stops receiving WAL communications and is no longer a
read-only instance. You must set up any replication you intend going forward
because the promoted Read Replica is now a new source DB instance.

A PostgreSQL Read Replica will report a replication lag of up to five minutes if there are no
user transactions occurring on the source DB instance.

Before a DB instance can serve as a source DB instance, you must enable automatic backups on
the source DB instance by setting the backup retention period to a value other
than 0.

Situations That Break PostgreSQL Replication

There are several situations where a PostgreSQL source DB instance can
unintentionally break replication with a Read Replica. These situations include the
following:

The max_wal_senders parameter is set too low to provide enough data to the number
of Read Replicas. This situation causes replication to stop.

The PostgreSQL parameter, wal_keep_segments, dictates how many Write Ahead Log
(WAL) files are kept to provide data to the Read Replicas. The parameter
value species the number of logs to keep. If you set the parameter value too
low, you can cause a Read Replica to fall so far behind that streaming
replication stops. In this case, Amazon RDS will report a replication error
and begin recovery on the Read Replica by replaying the source DB instance's
archived WAL logs. This recovery process continues until the Read Replica
has caught up enough to continue streaming replication. For more information
on this process and how to determine the appropriate parameter setting,
see Troubleshooting a PostgreSQL Read Replica Problem.

A PostgreSQL Read Replica will require a reboot if the source DB
instance endpoint changes.

When the WAL stream that provides data to a Read Replica is broken, PostgreSQL
switches into recovery mode to restore the Read Replica by using archived WAL files.
Once this process is compete, PostgreSQL will attempt to re-establish streaming
replication.

MySQL Read Replicas

Before a MySQL DB instance can serve as a replication source, you must enable automatic
backups on the source DB instance by setting the backup retention period to a value
other than 0. This requirement also applies to a Read Replica that is the source DB
instance for another Read Replica. Automatic backups are supported only for Read
Replicas running MySQL 5.6, not 5.1 or 5.5.

You can create up to five Read Replicas from one DB instance. In order for replication to
operate effectively, each Read Replica should have as much compute and storage resources
as the source DB instance. If you scale the source DB instance, you should also scale
the Read Replicas.

If a Read Replica is running MySQL 5.6, you can specify it as the source DB instance for
another Read Replica. For example, you can create ReadReplica1 from MyDBInstance, and
then create ReadReplica2 from ReadReplica1. Updates made to MyDBInstance are replicated
to ReadReplica1 and then replicated from ReadReplica1 to ReadReplica2. You cannot have
more than three instances involved in a replication chain. For example, you can create
ReadReplica1 from MySourceDBInstance, and then create ReadReplica2 from ReadReplica1,
but you cannot create a ReadReplica3 from ReadReplica2. To enable automatic backups on
an Amazon RDS MySQL version 5.6 Read Replica, first create the Read Replica, then modify the
Read Replica to enable automatic backups.

Read Replicas are designed to support read queries, but there may be a need for
occasional updates, such as adding an index to speed the specific types of queries
accessing the replica. You can enable updates by setting the read_only parameter to 0 in the DB
parameter group for the Read Replica.

You can run multiple concurrent Read Replica create or delete actions that reference the
same source DB instance, as long as you stay within the limit of five Read Replicas for
the source instance.

You can create a Read Replica from either Single-AZ or Multi-AZ DB instance deployments.
You use a Multi-AZ deployment to improve the durability and availability of a critical
system, but you cannot use the Multi-AZ secondary to serve read-only queries. You must
create Read Replicas from a high-traffic, Multi-AZ DB instance to offload read queries
from the source DB instance. If the source instance of a Multi-AZ deployment fails over
to the secondary, any associated Read Replicas will be switched to use the secondary as
their replication source. It is possible that the Read Replicas cannot be switched to
the secondary if some MySQL binlog events are not flushed during the failure. In this
case, you must manually delete and recreate the Read Replicas. You can reduce the chance
of this happening in MySQL 5.1 or 5.5 by setting the sync_binlog=1 and innodb_support_xa=1
dynamic variables. These settings may reduce performance, so test their impact before
implementing the changes to a production environment. These problems are less likely to
occur if you are using MySQL 5.6. For instances running MySQL 5.6, the parameters are
set by default to sync_binlog=1 and innodb_support_xa=1.

You must explicitly delete Read Replicas, using the same mechanisms for deleting a DB
instance. If you delete the source DB instance without deleting the replicas, each
replica is promoted to a standalone, Single-AZ DB instance.

If you promote a MySQL Read Replica that is in turn replicating to other Read
Replicas, those replications stay active. Consider an example where MyDBInstance1
replicates to MyDBInstance2, and MyDBInstance2 replicates to MyDBInstance3. If you
promote MyDBInstance2, there will no longer be any replication from MyDBInstance1 to
MyDBInstance2, but MyDBInstance2 will still replicate to MyDBInstance3.

Creating a Read Replica

You can create a Read Replica from an existing MySQL or PostgreSQL DB instance using
the AWS Management Console, CLI, or API. You create a Read Replica by specifying the
SourceDBInstanceIdentifier, which is the DB instance identifier of the
source DB instance from which you wish to replicate.

When you initiate the creation of a Read Replica, Amazon RDS takes a DB snapshot of your
source DB instance and begins replication. As a result, you will experience a brief I/O
suspension on your source DB instance as the DB snapshot occurs. The I/O suspension
typically lasts about one minute and can be avoided if the source DB instance is a
Multi-AZ deployment (in the case of Multi-AZ deployments, DB snapshots are taken from
the standby). An active, long-running transaction can slow the process of creating the
Read Replica, so wait for long-running transactions to complete before creating a Read
Replica. If you create multiple Read Replicas in parallel from the same source DB
instance, Amazon RDS takes only one snapshot at the start of the first create action.

When creating a Read Replica, there are a few things to consider. First, you must
enable automatic backups on the source DB instance by setting the backup retention
period to a value other than 0. This requirement also applies to a Read Replica that is
the source DB instance for another Read Replica. For MySQL DB instances, automatic
backups are supported only for Read Replicas running MySQL 5.6 but not for MySQL
versions 5.1 or 5.5. To enable automatic backups on an Amazon RDS MySQL version 5.6 Read
Replica, first create the Read Replica, then modify the Read Replica to enable automatic
backups.

Preparing MySQL DB Instances That Use MyISAM

If your MySQL DB instance uses a non-transactional engine such as MyISAM, you will
need to perform the following steps to successfully set up your Read Replica. These
steps are required to ensure that the Read Replica has a consistent copy of your
data. Note that these steps are not required if all of your tables use a
transactional engine such as InnoDB.

Stop all DML and DDL operations on non-transactional tables in the source DB instance and wait
for them to complete. SELECT statements can continue running.

Flush and lock the tables in the source DB instance.

Create the Read Replica using one of the methods in the following sections.

Check the progress of the Read Replica creation using, for example, the
DescribeDBInstances API operation. Once the Read
Replica is available, unlock the tables of the source DB instance and resume
normal database operations.

AWS Management Console

To create a Read Replica from a source MySQL or PostgreSQL DB
instance

In the My DB Instances pane, right click the MySQL
or PostgreSQL DB instance that you want to use as the source for a Read
Replica and select Create Read Replica.

In the DB Instance Identifier text box, type a name for the Read
Replica. Adjust other settings as needed.

In the Destination Region box, specify the region for the Read Replica if
it is different than the region of the source DB instance.

In the Destination DB Subnet Group box, specify a DB subnet group
associated with a VPC if you want the Read Replica to be created in that
VPC. Leave the box empty if you want the Read Replica to be created outside
of any VPC. The VPC and DB subnet group must exist in the destination
region. Within a given region, all Read Replicas created from the same
source DB instance must be either:

All created in the same VPC.

All created outside of any VPC.

Click Yes, Create Read Replica.

CLI

To create a Read Replica from a source MySQL or PostgreSQL DB
instance

API

Promoting a Read Replica to Be a DB Instance

You can promote a MySQL or PostgreSQL Read Replica into a standalone, Single-AZ DB
instance. When you promote a Read Replica, the DB instance will be rebooted before it
becomes available.

There are several reasons you might want to convert a Read Replica into a Single-AZ DB
instance:

Perform DDL operations (MySQL only) : DDL operations, such as
creating or re-building indexes, can take time and impose a significant
performance penalty on your DB instance. You can perform these operations on a
MySQL Read Replica once the Read Replica is in sync with its source DB instance.
Then you can promote the Read Replica and direct your applications to use the
promoted instance.

Sharding: Sharding embodies the "share-nothing" architecture
and essentially involves breaking a large database into several smaller
databases. Common ways to split a database include 1)splitting tables that are
not joined in the same query onto different hosts or 2)duplicating a table
across multiple hosts and then using a hashing algorithm to determine which host
receives a given update. You can create Read Replicas corresponding to each of
your “shards” (smaller databases) and promote them when you decide to convert
them into “standalone” shards. You can then carve out the key space (if you are
splitting rows) or distribution of tables for each of the shards depending on
your requirements.

Implement Failure Recovery - You can use Read Replica
promotion as a data recovery scheme if the source DB instance fails; however, if
your use case requires synchronous replication, automatic failure detection, and
failover, we recommend that you run your DB instance as a Multi-AZ deployment
instead. If you are aware of the ramifications and limitations of asynchronous
replication and you still want to use Read Replica promotion for data recovery,
you would first create a Read Replica and then monitor the source DB instance
for failures. In the event of a failure, you would do the following:

Promote the Read Replica.

Direct database traffic to the promoted DB instance.

Create a replacement Read Replica with the promoted DB instance as its source.

The new DB instance that is created when you promote a Read Replica retains the backup
retention period, backup window period, and parameter group of the former Read Replica
source. The promotion process can take several minutes or longer to complete, depending
on the size of the Read Replica. Once you promote the Read Replica into a Single-AZ DB
instance, it is just like any other Single-AZ DB instance. For example, you can convert
the new DB instance into a Multi-AZ DB instance, and you can create Read Replicas from
it. You can also take DB snapshots and perform point-in-time restore operations. Because
the promoted DB instance is no longer a Read Replica, you cannot use it as a replication
target. If a source DB instance has several Read Replicas, promoting one of the Read
Replicas to a DB instance has no effect on the other replicas.

We recommend that you enable automated backups on your Read Replica before promoting the
Read Replica. This ensures that no backup is taken during the promotion process. Once
the instance is promoted to a primary instance, backups are taken based on your backup
settings.

The following steps show the general process for promoting a Read Replica to a Single-AZ
DB instance.

Stop any transactions from being written to the Read Replica source DB instance, and then wait
for all updates to be made to the Read Replica. Database updates occur on the
Read Replica after they have occurred on the source DB instance, and this
replication "lag" can vary significantly. Use the Replica Lag metric to
determine when all updates have been made to the Read Replica.

(MySQL only) If you need to make changes to the MySQL Read Replica, you must the set the
read_only parameter to 0 in the DB parameter group for the Read Replica. You
can then perform all needed DDL operations, such as creating indexes, on the
Read Replica. Actions taken on the Read Replica do not affect the performance of
the source DB instance.

The promotion process takes a few minutes to complete. When you promote a Read Replica,
replication is stopped and the Read Replica is rebooted. When the reboot is
complete, the Read Replica is available as a Single-AZ DB instance.

CLI

API

Replicating a Read Replica Across Regions (MySQL
only)

You can create a MySQL Read Replica in a different region than the source DB instance
to improve your disaster recovery capabilities, scale read operations into a region
closer to end users, or make it easier to migrate from a data center in one region to a
data center in another region. Creating a MySQL Read Replica in a different region than
the source instance is very similar to creating a replica in the same region. You run
the create Read Replica command in the region where you want the Read Replica, and
specify the Amazon Resource Name (ARN) of the source DB instance.

Cross-Region Replication
Considerations

All of the considerations for performing replication within a region apply to
cross-region replication. The following extra considerations apply when replicating
between regions:

You can only replicate between regions when using Amazon RDS DB instances of
MySQL 5.6.

You can only cross one regional boundary in a given replication chain. You
can create a cross-region Amazon RDS Read Replica from:

A source Amazon RDS DB instance that is not a Read Replica of another
Amazon RDS DB instance.

An Amazon RDS DB instance that is a Read Replica of an on-premises or
Amazon EC2 instance of MySQL that is not in Amazon RDS.

You cannot set up a replication channel into or out of the AWS GovCloud (US)
Region.

You should expect to see some higher level of lag time for any Read
Replica that is in a different region than the source instance, due to the
longer network channels between regional data centers.

Within a region, all cross-region replicas created from the same source DB
instance must either be in the same Amazon VPC or be outside of a VPC. For those
Read Replicas, any of the create Read Replica commands that specify the
--db-subnet-group-name parameter must specify a
DB subnet group from the same VPC.

You can create a cross-region Read Replica in an VPC from a source DB
instance that is not in an VPC. You can also create a cross-region Read
Replica that is not in an VPC from a source DB instance that is in a
VPC.

You can reduce your data transfer costs by reducing the number of cross region
Read Replicas you create. For example, if you have a source DB instance in one
region and want to have three Read Replicas in another region, only create one of
the Read Replicas from the source DB instance, and then create the other two
replicas from the first Read Replica instead of the source. For example, if you have
source-instance-1 in one region, you can:

Create read-replica-1 in the new region, specifying
source-instance-1 as the source.

Create read-replica-2 from read-replica-1.

Create read-replica-3 from read-replica-1.

In this example, you will only be charged for the data transferred from
source-instance-1 to read-replica-1. You will not be
charged for the data transferred from read-replica-1 to the other two
replicas because they are all in the same region. If you created all three replicas
directly from source-instance-1, you would be charged for the data
transfers to all three replicas.

Examples

Example Create Cross Region Read Replica Outside of any VPC

This is an example of creating a Read Replica in us-west-2 from a source DB
instance in us-east-1. The Read Replica is created outside of a VPC:

Cross Region Replication Process

Amazon RDS uses the following process to create a cross region Read Replica. Depending
on the regions involved and the amount of data in the databases, this process could
take hours to complete. You can use this information to determine how far the
process has proceeded when you create a cross region Read Replica:

Amazon RDS begins configuring the source DB instance as a replication source and sets the status to
modifying.

Amazon RDS begins setting up the specified Read Replica in the destination region and sets the
status to creating.

Amazon RDS creates an automated DB snapshot of the source DB instance in the source region. The
format of the DB snapshot name is rds:<InstanceID>-<timestamp>, where
<InstanceID> is the identifier of the source instance, and <timestamp>
is the date and time the copy started. For example,
rds:mysourceinstance-2013-11-14-09-24 was created from the
instance mysourceinstance at 2013-11-14-09-24.
During this phase, the source DB instance status remains
modifying, the Read Replica status remains
creating, and the DB snapshot status is
creating. The progress column of the DB snapshot
page in the console reports how far the DB snapshot creation has progressed.
When the DB snapshot is complete, the status of both the DB snapshot and
source DB instance are set to available.

Amazon RDS begins a cross region snapshot copy for the initial data transfer. The snapshot copy is
listed as an automated snapshot in the destination region with a status of
creating. It has the same name as the source DB snapshot. The progress column of
the DB snapshot display indicates how far the copy has progressed. When the copy
is complete, the status of the DB snapshot copy is set to available.

Amazon RDS then uses the copied DB snapshot for the initial data load on the Read Replica. During
this phase, the Read Replica will be in the list of DB instances in the
destination, with a status of creating. When the load
is complete, the Read Replica status is set to
available, and the DB snapshot copy is
deleted.

When the Read Replica reaches the available status, Amazon RDS starts by replicating the changes
made to the source instance since the start of the create Read Replica
operation. During this phase, the replication lag time for the Read Replica
will be greater than 0. You can monitor this in Amazon CloudWatch by viewing the
Amazon RDS ReplicaLag metric. The ReplicaLag metric reports the
value of the Seconds_Behind_Master field of the MySQL
SHOW SLAVE STATUS command. For more information, see SHOW
SLAVE STATUS. When the ReplicaLag metric reaches 0,
the replica has caught up to the source DB instance. If the
ReplicaLag metric returns -1, then replication is currently
not active. ReplicaLag = -1 is equivalent to
Seconds_Behind_Master = NULL. Common causes
for ReplicaLag returning -1 are the following:

A network outage.

Writing to tables with indexes on a Read Replica. If the
read_only parameter is not set to 0 on the Read
Replica, it can break replication.

Using a non-transactional storage engine such as MyISAM.
Replication is only supported for the InnoDB storage
engine.

Monitoring Read Replication

You can monitor the status of a Read Replica in several ways. The Amazon RDS console shows
the status of a Read Replica; you can also see the status of a Read Replica using the
CLI command rds-describe-db-instances or the API action
DescribeDBInstances.

The status of a Read Replica can be one of the following:

Replicating—The Read Replica is
replicating successfully.

Error—An error has occurred with the
replication. Check the Replication Error field in the Amazon RDS
console or the event log to determine the exact error. For more information about
troubleshooting a replication error, see Troubleshooting a MySQL Read Replica
Problem.

Stopped—(MySQL only) Replication has
stopped because of a customer initiated request.

Terminated—The Read Replica has
lagged the source DB instance for more than the backup retention period due to
replication errors and is terminated. The Read Replica is still accessible for
read operations but cannot synchronize with the source instance.

If replication errors occur in a Read Replica for more than the backup retention
period, replication is terminated to prevent increased storage requirements and long
failover times. Broken replication can effect storage because the logs can grow in size
and number due to the high volume of errors messages being written to the log. Broken
replication can also affect failure recovery due to the time Amazon RDS requires to maintain
and process the large number of logs during recovery.

You can monitor how far a MySQL Read Replica is lagging the source DB instance by
viewing the Seconds_Behind_Master data returned by the
MySQL Show Slave Status command, or the CloudWatch Replica Lag statistic. If a replica lags too far behind for
your environment, consider deleting and recreating the Read Replica. Also consider
increasing the scale of the Read Replica to speed replication.

You can monitor PostgreSQL Read Replica lag by viewing the CloudWatch Replica Lag statistic or by running the following command from the
PostgreSQL source DB instance:

select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Troubleshooting a MySQL Read Replica
Problem

MySQL's replication technology is asynchronous. Because it is asynchronous,
occasional BinLogDiskUsage increases on the source DB instance and
ReplicaLag on the Read Replica are to be expected. For example, a high
volume of writes to the source DB instance can occur in parallel, while writes to the
Read Replica are serialized using a single I/O thread, can lead to a lag between the
source instance and Read Replica. For more information about read-only replicas in the
MySQL documentation, see Replication Implementation Details.

There are several things you can do to reduce the lag between updates to a source DB
instance and the subsequent updates to the Read Replica, such as:

Sizing a Read Replica to have a storage size and DB instance class comparable
to the source DB instance.

Ensuring that parameter settings in the DB parameter groups used by the source
DB instance and the Read Replica are compatible. For more information and an
example, see the discussion of the max_allowed_packet parameter
later in this section.

Amazon RDS monitors the replication status of your Read Replicas and updates the
Replication State field of the Read Replica instance to
Error if replication stops for any reason, such as DML queries being
run on your Read Replica that conflict with the updates made on the source DB instance.
You can review the details of the associated error thrown by the MySQL engine by viewing
the Replication Error field. Events that indicate the status of the Read
Replica are also generated, including RDS-EVENT-0045, RDS-EVENT-0046, and RDS-EVENT-0047. For more
information about events and subscribing to events, see Using Amazon RDS Event Notification. If the MySQL error message is returned, review the
error number in the MySQL error
message documentation.

One common issue that can cause replication errors is when the value for the
max_allowed_packet parameter for a Read Replica is less than the
max_allowed_packet parameter for the source DB instance. The
max_allowed_packet parameter is a custom parameter that you can set in
a DB parameter group that is used to specify the maximum size of DML that can be
executed on the database. If the max_allowed_packet parameter value in the
DB parameter group associated with a source DB instance is smaller than the
max_allowed_packet parameter value in the DB parameter group associated
with the source's Read Replica, the replication process can throw an error (Packet
bigger than 'max_allowed_packet' bytes) and stop replication. You can fix the error by
having the source and Read Replica use DB parameter groups with the same
max_allowed_packet parameter values.

Other common situations that can cause replication errors include:

Writing to tables on a Read Replica. If you are creating indexes on a Read Replica, you need
to have the read_only parameter set to 0 to create the indexes. If you are writing to tables on the
Read Replica, it may break replication.

Using a non-transactional storage engine such as MyISAM. Read replicas
require a transactional storage engine. Replication is only supported for the
InnoDB storage engine.

If you decide that you can safely skip an error, you can follow
the steps described in the section Skipping the Current Replication Error. Otherwise, you can delete
the Read Replica and create a instance using the same DB instance identifier so that the
endpoint remains the same as that of your old Read Replica. If a replication error is
fixed, the Replication State changes to
replicating.

Troubleshooting a PostgreSQL Read Replica Problem

The PostgreSQL parameter, wal_keep_segments, dictates how many Write Ahead
Log (WAL) files are kept to provide data to the Read Replicas. The parameter value
species the number of logs to keep. If you set the parameter value too low, you can
cause a Read Replica to fall so far behind that streaming replication stops. In this
case, Amazon RDS will report a replication error and begin recovery on the Read Replica
by replaying the source DB instance's archived WAL logs. This recovery process continues
until the Read Replica has caught up enough to continue streaming replication.

The PostgreSQL log will show when Amazon RDS is recovering a Read Replica that is this
state by replaying archived WAL files.

Once Amazon RDS has replayed enough archived WAL files on the replica to catch up and
allow the Read Replica to begin streaming again, PostgreSQL will resume streaming and
write a similar line to the following to the log file:

2014-11-07 19:41:36 UTC::@:[24714]:LOG: started streaming WAL from primary at 1B/B6000000
on timeline 1

You can determine how many WAL files you should keep by looking at the checkpoint
information in the log. The PostgreSQL log shows the following information at each
checkpoint. By looking at the "# recycled" transaction log files of these log
statements, a user can understand how many transaction files will be recycled during a
time range and use this information to tune the wal_keep_segments
parameter.

For example, if the PostgreSQL log shows that 35 files are recycled from the "checkpoint
completed" log statements within a 5 minute time frame, we know that with this usage
pattern a Read Replica relies on 35 transaction files in 5 minutes and could not survive
5 minutes in a non-streaming state if the source DB instance is set to the default
wal_keep_segments parameter value of 32.