Re:Amazon RDS now supports PostgreSQL 9.4.1
by dankohn1 on Thursday March 19, 2015 @08:14AM
(#9231340)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by dijit on Thursday March 19, 2015 @08:31AM
(#9231399)
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)
Re:Amazon RDS now supports PostgreSQL 9.4.1
by netcraft on Thursday March 19, 2015 @08:56AM
(#9231525)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by dijit on Thursday March 19, 2015 @09:15AM
(#9231620)
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).
Re:Amazon RDS now supports PostgreSQL 9.4.1
by netcraft on Thursday March 19, 2015 @09:54AM
(#9231876)
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).
Re:Amazon RDS now supports PostgreSQL 9.4.1
by smt88 on Thursday March 19, 2015 @10:26AM
(#9232111)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by sciurus on Thursday March 19, 2015 @11:26AM
(#9232588)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by smt88 on Thursday March 19, 2015 @02:09PM
(#9233603)
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).
Re:Amazon RDS now supports PostgreSQL 9.4.1
by earless1 on Thursday March 19, 2015 @09:51AM
(#9231851)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by tgeek on Thursday March 19, 2015 @09:40AM
(#9231779)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by endymi0n on Thursday March 19, 2015 @06:44AM
(#9231088)
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?
Re:Amazon RDS now supports PostgreSQL 9.4.1
by endymi0n on Thursday March 19, 2015 @07:13AM
(#9231150)
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?
Re:Amazon RDS now supports PostgreSQL 9.4.1
by jpgvm on Thursday March 19, 2015 @07:45AM
(#9231236)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by moe on Thursday March 19, 2015 @08:07AM
(#9231312)
This isn't as bad as it sounds
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by moe on Thursday March 19, 2015 @10:25AM
(#9232101)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by saurik on Thursday March 19, 2015 @11:32AM
(#9232640)
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.
Re:Amazon RDS now supports PostgreSQL 9.4.1
by thomasfoster96 on Thursday March 19, 2015 @08:53AM
(#9231506)
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
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 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).
Plus it's all hooked into the rest of the AWS system, which can be really helpful.
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).
If the former, then unfortunately the only option is a database dump followed by creating a new RDS instance and then restoring.
As for the latter you can do an in place upgrade following the PG docs.
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.
this is pretty much a problem with RDS only. Postgres itself offers 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.
https://aws.amazon.com/blogs/aws/rds-postgres-read-replicas/