RDS - Postgres to Aurora high level question re replica's


Morning all, So I have a single instance RDS (multi-az) postgres database. We want to migrate over to Aurora for several reasons, and so this is where I am at. Since it's a mult-az, from the reading I can create an aurora replica with no downtime. So that was great, I did that and it build a "replica cluster" and had no downtime.

I noticed both a read and writer endpoint, so moved a few test queries over that was read only and things worked perfect. So the question is the next step(s) and what I can expect

  • If I start using the writer endpoint, I would assume that does not sync back to the master potsgres right?

If no, then the next step I would assume would be to select that aurora, say "promote" while moving DNS on my connection to that writer endpoint. The DNS would be seconds and then just the TTL, but is there "downtime" when its being promoted (meaning once DNS changes, will the writes just start working, is there any reboots, etc.). I would equate it to EC2 machines spinning up, the console says "initializing" but before the available I am usually able to SSH in. I am just trying to know how long all writes will be failing for (estimate of course).


asked a month ago121 views
4 Answers

Did you get a chance to look at this blog post Improve application availability on Amazon Aurora, if not please go through once. It covers the scenario described in your question as well.

It is very recent and also does have very useful insights in practical manner.

Hope you find this useful.

Should you have any questions further, happy to be of help.


profile pictureAWS
answered a month ago
  • Thanks, posted a little more directed question below as that thread didn't really answer.


Hi there.

The cluster has two endpoints as you saw. The reader endpoint must be used for read operations only, while the write endpoint can be used for inserting, updating, and deleting data. Now, to answer your question, using these endpoints will correctly point to the right node in the cluster, the write endpoint to the write (primary) node, and the read endpoint to the read replicas (I mean plural because you can have multiple read replicas). Writing data using the write endpoint will keep the read replicas in sync. As long as you use them like that and not directly using the individual node endpoints, you should be fine.

I hope this helps.

profile pictureAWS
answered a month ago
  • Thanks, unfortunately the post didn't answer the 2 core quesitons, so posted a more clarified question below.


Thanks guys, I did look at that post however neither of the 2 core questions were answered, however I will put them direct here with my comment. I am not looking for that replica to be a long term solution so that thread was of no help. So the 2 core questions are;

  1. Now that I have an aurora read (and write) replica. If I use the writer, insert data into that writer, will that insert make it's way back to the primary postgres.
  • I don't think so as when it was created, it was under a create read replica, but just want to make sure.
  1. If the above is no, my migratrion plan would be;
  • Move DNS to aurora writable endpoint
  • Promote that replica
  • DNS update is minutes if not quicker, so when I hit promote and make that change, is there any downtime, reboots, etc. on the aurora replica cluster? I am trying to estimate when we push that promote button how long an outage to expect.

Neither that article or reply addressed, so that is what I am mainly looking for.

answered a month ago
  1. RDS to Aurora replication would be single direction. Also, Aurora replicas are read only so you wont be able to write before promoting it. In my test environment I was not able to insert data to Aurora Replica using Cluster Writer endpoint as well as instance endpoint. ERROR: cannot execute INSERT in a read-only transaction SQL state: 25006

  2. Migration Path: Stop the application --> Make sure replica lag is zero --> Move DNS to aurora writable endpoint--> Promote Aurora replica --> Change DNS to Aurora Writer end point.

3.In my test environment promoting Aurora Replica took approx. 2 minutes. Lost the connectivity to Aurora for few seconds during the process.

Ref Doc: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Migrating.html#AuroraPostgreSQL.Migrating.RDSPostgreSQL.Replica

"Migrating from an RDS for PostgreSQL DB instance to an Aurora PostgreSQL DB cluster is a multistep procedure. First, you create an Aurora read replica of your source RDS for PostgreSQL DB instance. That starts a replication process from your RDS for PostgreSQL DB instance to a special-purpose DB cluster known as a Replica cluster. The Replica cluster consists solely of an Aurora read replica (a reader instance).

Once the Replica cluster exists, you monitor the lag between it and the source RDS for PostgreSQL DB instance. When the replica lag is zero (0), you can promote the Replica cluster. Replication stops, the Replica cluster is promoted to a standalone Aurora DB cluster, and the reader is promoted to writer instance for the cluster. You can then add instances to the Aurora PostgreSQL DB cluster to size your Aurora PostgreSQL DB cluster for your use case. You can also delete the RDS for PostgreSQL DB instance if you have no further need of it."

answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions