5 ms·

Amazon RDS now supports PostgreSQL 9.4.1

131 points by endymi0n 12y ago

endymi0n 12y ago
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?
- jpgvm 12y ago
  Are you talking about RDS or your own 9.3 to 9.4 upgrade? 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.
  - endymi0n 12y ago
    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?
    - pyvpx 12y ago
      I imagine you could use one of the various streaming replication solutions to avoid the "halt" part. pure speculation on my part, however.
      - jpgvm 12y ago
        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.
        moe 12y ago
        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.
        pilif 12y ago
        >Yes, it is very much as bad as it sounds and a big pain point with Postgresql in general and RDS in particular. 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.
        moe 12y ago
        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.
        saurik 12y ago
        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.
    - zwily 12y ago
      You can use some of the trigger-based replication methods too, for a low-downtime migration. Read the second half of this post: https://aws.amazon.com/blogs/aws/rds-postgres-read-replicas/ https://aws.amazon.com/blogs/aws/rds-postgres-read-replicas/
dankohn1 12y ago
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. https://forums.aws.amazon.com/message.jspa?messageID=604244 https://forums.aws.amazon.com/message.jspa?messageID=604244
- dijit 12y ago
  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)
  - netcraft 12y ago
    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.
    - dijit 12y ago
      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).
alpeb 12y ago
JSONB is nice, but the inability to perform updates inside a JSON doc is major drawback.
- thomasfoster96 12y ago
  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.

131 points by endymi0n 12y ago

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?

jpgvm 12y ago
Are you talking about RDS or your own 9.3 to 9.4 upgrade? 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.
- endymi0n 12y ago
  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?
  - pyvpx 12y ago
    I imagine you could use one of the various streaming replication solutions to avoid the "halt" part. pure speculation on my part, however.
    - jpgvm 12y ago
      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.
      - moe 12y ago
        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.
        pilif 12y ago
        >Yes, it is very much as bad as it sounds and a big pain point with Postgresql in general and RDS in particular. 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.
        moe 12y ago
        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.
        saurik 12y ago
        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.
  - zwily 12y ago
    You can use some of the trigger-based replication methods too, for a low-downtime migration. Read the second half of this post: https://aws.amazon.com/blogs/aws/rds-postgres-read-replicas/ https://aws.amazon.com/blogs/aws/rds-postgres-read-replicas/

dankohn1 12y ago

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. https://forums.aws.amazon.com/message.jspa?messageID=604244 https://forums.aws.amazon.com/message.jspa?messageID=604244

dijit 12y ago
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)
- netcraft 12y ago
  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.
  - dijit 12y ago
    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).

alpeb 12y ago

JSONB is nice, but the inability to perform updates inside a JSON doc is major drawback.

thomasfoster96 12y ago
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.