3 ms·

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/

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/