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

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.

Abhishek

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.

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.

2. 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.

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."