I was very unhappy to find that RDS does not support cross-region read replication for Postgres, only for MySQL. We had made a significant investment to migrate from MySQL to Postgres but are now having to back out a lot of changes, because we need that redundancy.
Postgres is widely acknowledged as the leading open source database. It's frustrating that it remains a second class citizen on AWS.
Postgres is still comparatively new to AWS, whereas MySQL was one of the first (the first?) supported RDS engines way back in 2008/9 or thereabouts. I guess this is due in part to the relative latecoming of built-in streaming replication support in Postgres, it's only been since 9.0 that it's been a thing, and 9.1 that it's been production-ready, which coincides with it's rise in popularity over recent years (well, that and Oracle buying Sun..).
My money is on support for x-region replication being announced late this year; it's a feature I'm interested in for sure.
I definitely share your sentiment, but AWS is geared mostly towards developers, and mysql is the leading database in terms of users.. especially among the legacy development community. (those that did LAMP in the 00's and do not wish to relearn databases)
I'm not doubting you, but do you have any sources for user counts for different database types? I get the same sense, but I wonder what the numbers are really like.
one of the issues in open source is that the numbers are very hard to quantify.
I know, from personal experience that most large companies are using mysql, even if they use postgresql they're also using mysql for smaller things..
to give some examples the only company I ever worked in which used pgsql as a direct backing store and primary database still used mysql to manage it's internal documentation servers and caching systems (records used to keep track of what is cached inside of memcached).
simply due to the ubiquity of the tools developed around mysql and the notion that you will never have a hard time finding a person with mysql experience causes people to choose mysql when designing new things.
currently I'm getting a lot of force against postgresql adoption in my company, even though it makes complete sense for my project- the fact that "nobody understands it" and that "tooling exists behind mysql" is enough to keep the status-quo for a long time unless someone fights really hard (which I'm doing).
I'm just afraid the anecdotal evidence and the "conventional wisdom" of mysql being the defacto standard is perpetuating the myth and fighting against the adoption. My experience is more mixed - most of the large companies I have worked with have used either mssql or oracle (the idea that if you don't pay for it how can it be worth anything is very strong).
Scaling, backing up, and replicating database hardware is a huge pain. RDS does all of that. For small companies, the extra cost is more than worth the time savings.
Plus it's all hooked into the rest of the AWS system, which can be really helpful.
Sure, RDS reduces the operational overhead and I love it for that, but you make tradeoffs to gain those benefits. For a lot of businesses they may not matter, but in your case they sounded pretty severe.
I think you might be confusing me with someone else, but I agree that there are tradeoffs.
I think RDS is great until the needs are extreme enough to require a full-time sysadmin or DBA. Nowadays, though, that means you're dealing with quite a lot of traffic (or just an edge case in terms of your read/write ratios or latency requirements).
Before cross region replication was available for MySql we use to just ship our snapshots to our failover region. We also stood up an EC2 MySQL instance as a slave to the RDS instance in our primary region. This approach allowed us to use RDS back then and still be capable of a regional failover within a reasonable amount of time.
Calling Postgres the leading open source DB is probably a bit more than a stretch these days, but yes its quite popular with startups(though one would argue nosql DBs are #1 in the eyes of most startups sadly).
Unfortunately one of the places its not popular in, is Amazon and the majority of their business customers. MySQL, Oracle, and SQL Server are all much more popular than Postgres, which is why they have probably focused on it more over the years. That said, the features have seemed to be coming pretty regularly.
We've been waiting for JSONB support quite some months now, great that it finally arrived in AWS. Dumb question now: Anyone knows how to upgrade 9.3 machines?
So it's basically standstill? Create new instance, halt all processes, migrate the data, delete old one? Sounds sad, but if it's the only way to go, I'll go ahead. Anyone want to share experiences scriptingwise?
Alas you currently can't use RDS as either a target or source for Postgres streaming replication.
Right now the best you can do is a scheduled cutover. This isn't as bad as it sounds because you can provision the new instance, import the production data and test everything before final sync and cutover.
As someone who has done this multiple times in the past I have to disagree.
Yes, it is very much as bad as it sounds and a big pain point with Postgresql in general and RDS in particular.
For small databases it may be considered a mere nuisance. But when your pg_dump takes an hour to complete then the whole "final sync and cutover" becomes a real problem.
pg_upgrade which can do even huge databases (2 TB here) in seconds.
That depends on various factors. Sometimes pg_upgrade has to copy all your data. In those cases and for 2T you'll be measuring the wait in hours, not seconds.
I am under the impression that the only cases where this happens are when upgrading certain custom data types related to full-text search from 8.3 to 9.x, which seems like a very narrow complaint. This definitely used to be a serious general issue with PostgreSQL, however, but hasn't been in many years.
I suppose PL/V8 ( which RDS has installed by default) would make it trivial to write a function to do this - you'd be able to do anything with JSON in postgres that you can in Node.js or the browser.
Postgres is widely acknowledged as the leading open source database. It's frustrating that it remains a second class citizen on AWS.
https://forums.aws.amazon.com/message.jspa?messageID=604244