Sync AWS RDS postgres db with writable RDS postgres db

Question

I would like to have a Postgres database which is in sync with my production database like a read-replica, but I would also like to write to that database. AWS provides read-replicas to be writable for MySQL and MariaDB but not Postgres. Is there any other way to achieve this?

Answer 1

Well, by definition, read replicas are not writable, so I'm afraid I don't think you'll have much luck with that approach.

Amazon themselves state that read replicas are for read only traffic:

You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput.

Now, as you say, for MySQL read replicas can be promoted to masters (and therefore become writable), but pay special attention to the "when needed" below:

Read replicas can also be promoted when needed to become standalone DB instances.

However, RDS itself does not support multi-master deployments for MySQL.

For PostgreSQL things are even "worse". AWS RDS for Postgres does not (at the time of writing this) support automatic promotion of read-replicas, leaving you with Multi-AZ as your only option.

Outside RDS, multi-master deployments of PostgreSQL (which sounds like what you're looking for) require an even more elaborate setup. You can find more information in the clustering section of their wiki.

As a general note, horizontally scaling relational / SQL databases is probably not something you'll have a lot of fun with and you're bound to run into problems along the way.

That's because they were simply not designed for horizontal scaling the same way that newer "NoSQL" databases are (take a look at MongoDB or Cassandra, etc.). You are far better off scaling them vertically, for as far as that will take you (and it will take you quite some way).

The only relational database that I know of that's (being) built to scale out is CockroachDB, but albeit a very promising solution, that's still in beta -- there's no 1.0 release of it yet.