3 ms·

Doesn't Postgres have its own set of unique of problems?

by TedDoesntTalk 5y ago

mschuster91 5y ago
Indeed, the worst of which is you can't do in-place upgrades - upgrading cluster versions is an insanely complex ride.
- onphonenow 5y ago
  I routinely upgrade versions - on AWS RDS this is just a click or two in my case. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_... When you say "complex ride" what does that mean?
  - jrockway 5y ago
    I am not sure if that procedure covers major versions (13 -> 14 for example), but if it does, they are ahead of the curve. On GCP, you have to backup the database and restore the backup to a new database. On self-hosted installs, you have to pick one of these processes: https://www.postgresql.org/docs/14/upgrading.html https://www.postgresql.org/docs/14/upgrading.html
    - acdha 5y ago
      It does. It’s really easy to outsource.
    - AlisdairO 5y ago
      To confirm, RDS does support in place major version upgrade for postgres.
    - onphonenow 5y ago
      They do an in_place upgrade using pg_upgrade I believe. They do have to regenerate system tables - they also automate I think some updates to system statistics. But for very large databases this really is the way to go I think. They do suggest a backup before you run this in case there are problems, most folks have periodic backups anyways, but the backup is not used.
  - mschuster91 5y ago
    > When you say "complex ride" what does that mean? On managed postgresql services like RDS it's easy, yes, because Amazon does all of the nasty work for you and has already seen all the kinks you may run into. If you are on your own and using Docker, you essentially need a third-party image that has the old and new binaries (https://github.com/tianon/docker-postgres-upgrade https://github.com/tianon/docker-postgres-upgrade). Good f...ing luck if you dare run PostgreSQL inside a Kubernetes environment, because adapting that guide to that is a can of worms large enough to make anyone squirm. If you are on your own and are using distribution packages, it's more complicated (https://blog.samuel.domains/blog/tutorials/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11 https://blog.samuel.domains/blog/tutorials/from-stretch-to-b...). If you are on your own and use postgres from source, well... have fun, there's a reason why I prefer using distribution-maintained packages when possible.
- paulryanrogers 5y ago
  Fun fact InnoDB in place updates have plenty of caveats too: must be last column, cannot have non-default on-update/delete clauses. And if you workaround with any table-rename solutions like pt-online-schema-change then you might crash your server. (Some MySQL 8 versions crash on table renames.) EDIT: Oops, guess you meant version updates not DDL. Leaving comment for posterity.
iruoy 5y ago
Postgres doesn't have a way to change the order of columns. Of course you could make a view, but my code doesn't use a view and I don't really want to make one just for when I want to check the database.
- corrigible 5y ago
  Wouldn't a single application of a temp table be sufficient?[0] 0: https://brianchildress.co/reorder-columns-postgres/ https://brianchildress.co/reorder-columns-postgres/
- mikl 5y ago
  If that’s the biggest problem you have with your database, be happy.
- acdha 5y ago
  Out of curiosity, how does this cause a problem for you? I’ve been using SQL databases heavily since the mid-90s and I’m not sure I’ve ever been in a situation where I had to use SELECT * but couldn’t either process the results by column name or have the client read in whatever order needed. I strongly favor only selecting the fields I care about, however, so I’m assuming there’s a case you’ve encountered.
acdha 5y ago
I’ve encountered far fewer and nothing treacherous like the “you lost data but we didn’t tell you about it” stuff MySQL is famous for.

Doesn't Postgres have its own set of unique of problems?

mschuster91 5y ago

Indeed, the worst of which is you can't do in-place upgrades - upgrading cluster versions is an insanely complex ride.

onphonenow 5y ago
I routinely upgrade versions - on AWS RDS this is just a click or two in my case. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Upgrading.html#USER_UpgradeDBInstance.Upgrading.Manual https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_... When you say "complex ride" what does that mean?
- jrockway 5y ago
  I am not sure if that procedure covers major versions (13 -> 14 for example), but if it does, they are ahead of the curve. On GCP, you have to backup the database and restore the backup to a new database. On self-hosted installs, you have to pick one of these processes: https://www.postgresql.org/docs/14/upgrading.html https://www.postgresql.org/docs/14/upgrading.html
  - acdha 5y ago
    It does. It’s really easy to outsource.
  - AlisdairO 5y ago
    To confirm, RDS does support in place major version upgrade for postgres.
  - onphonenow 5y ago
    They do an in_place upgrade using pg_upgrade I believe. They do have to regenerate system tables - they also automate I think some updates to system statistics. But for very large databases this really is the way to go I think. They do suggest a backup before you run this in case there are problems, most folks have periodic backups anyways, but the backup is not used.
- mschuster91 5y ago
  > When you say "complex ride" what does that mean? On managed postgresql services like RDS it's easy, yes, because Amazon does all of the nasty work for you and has already seen all the kinks you may run into. If you are on your own and using Docker, you essentially need a third-party image that has the old and new binaries (https://github.com/tianon/docker-postgres-upgrade https://github.com/tianon/docker-postgres-upgrade). Good f...ing luck if you dare run PostgreSQL inside a Kubernetes environment, because adapting that guide to that is a can of worms large enough to make anyone squirm. If you are on your own and are using distribution packages, it's more complicated (https://blog.samuel.domains/blog/tutorials/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11 https://blog.samuel.domains/blog/tutorials/from-stretch-to-b...). If you are on your own and use postgres from source, well... have fun, there's a reason why I prefer using distribution-maintained packages when possible.
paulryanrogers 5y ago
Fun fact InnoDB in place updates have plenty of caveats too: must be last column, cannot have non-default on-update/delete clauses. And if you workaround with any table-rename solutions like pt-online-schema-change then you might crash your server. (Some MySQL 8 versions crash on table renames.) EDIT: Oops, guess you meant version updates not DDL. Leaving comment for posterity.

iruoy 5y ago

Postgres doesn't have a way to change the order of columns. Of course you could make a view, but my code doesn't use a view and I don't really want to make one just for when I want to check the database.

corrigible 5y ago
Wouldn't a single application of a temp table be sufficient?[0] 0: https://brianchildress.co/reorder-columns-postgres/ https://brianchildress.co/reorder-columns-postgres/
mikl 5y ago
If that’s the biggest problem you have with your database, be happy.
acdha 5y ago
Out of curiosity, how does this cause a problem for you? I’ve been using SQL databases heavily since the mid-90s and I’m not sure I’ve ever been in a situation where I had to use SELECT * but couldn’t either process the results by column name or have the client read in whatever order needed. I strongly favor only selecting the fields I care about, however, so I’m assuming there’s a case you’ve encountered.

acdha 5y ago

I’ve encountered far fewer and nothing treacherous like the “you lost data but we didn’t tell you about it” stuff MySQL is famous for.