10 ms·

These changes look fantastic. If I may hijack the thread with some more general complaints though, I wish the Postgres team would someday prioritize migration.

These changes look fantastic. If I may hijack the thread with some more general complaints though, I wish the Postgres team would someday prioritize migration. Like make it easier to make all kinds of DB changes on a live DB, make it easier to upgrade between postgres versions with zero (or low) downtime, etc etc. Warnings when the migration you're about to do is likely to take ages because for some reason it's going to lock the entire table, instant column aliases to make renames easier, instant column aliases with runtime typecasts to make type migrations easier, etc etc etc. All this stuff is currently extremely painful for, afaict, no good reason (other than "nobody coded it", which is of course a great reason in OSS land). I feel like there's a certain level of stockholm syndrome in the sense that to PG experts, these things aren't that painful anymore because they know all the pitfalls and gotchas and it's part of why they're such valued engineers.

harikb 5y ago
Doesn’t PG already support inplace version upgrade? Also PG is one of the few that support schema/DDL statements inside a transaction.
- skrebbel 5y ago
  "one of the few" is a pretty low bar though, I don't know a DB that doesn't suck at this.
  - jeltz 5y ago
    Or maybe it is you who are underestimating the technical complexity of the task? A lot of effort has been spent on making PostgreSQL as good as it is on migrations. Yes, it is not as highly prioritized as things like performance or partitioning but it is not forgotten either.
bmcahren 5y ago
We currently use MongoDB and while Postgres is attractive for so many reasons, even with Amazon Aurora's Postgres we still need legacy "database maintenance windows" in order to achieve major version upgrades. With MongoDB, you're guaranteed single-prior-version replication compatibility within a cluster. This means you spin up an instance with the updated version of MongoDB, it catches up to the cluster. Zero downtime, seamless transition. There may be less than a handful of cancelled queries that are retryable but no loss of writes with their retryable writes and write concern preferences. e.g. MongoDB 3.6 can be upgraded to MongoDB 4.0 without downtime. Edit: Possibly misinformed but the last deep dive we did indicated there was not a way to use logical replication for seamless upgrades. Will have to research.
- deleted 5y ago
  [deleted]
- TedShiller 5y ago
  The problem with MongoDB though is that you're on MongoDB
  - salil999 5y ago
    And how exactly is that a problem?
    - mayankkaizen 5y ago
      That was sarcasm. But yeah, you can search for MongoDB and you'll come across many many posts criticizing it. It can be said Mongodb is hated ad much as Postgres is loved. Personally I have no opinion about mongodb.
      - The_Colonel 5y ago
        Yes, and most of these love/hate memes are blowned out of proportion by people who don't actually have any real expertise in those technologies, but just parrot whatever they've read in some memes.
        staticassertion 5y ago
        You're exactly correct. Tons of "XYZ is bad" because of some meme that they don't even understand or have context on that hasn't been relevant for years. I have no idea if MongoDB is good or bad at this point, but the comments of "haha it's mongo" are completely devoid of meaningful content and should be flagged.
        operator9A 5y ago
        I was part of a team that operated a large Mongo cluster for most of the last decade. I would not have advised anyone to use Mongo as their durable source of truth database then, and I still don't think it's advisable to do so now. On numerous occasions, Mongo demonstrated the consequences of poor engineering judgment and an addled approach to logic in critical components responsible for data integrity. In addition, Mongo internalized many poor patterns with respect to performance and change management. Mongo did not, and does not provide the data integrity or performance guarantees that other databases internalize by design (the WiredTiger transition helped, but did not cure many of the issues). PostgreSQL introduced JSONB GIN index support sometime around 2015, making Postgres a better fit for most JSON-based applications than Mongo.
dragonwriter 5y ago
> I feel like there's a certain level of stockholm syndrome in the sense that to PG experts, these things aren't that painful anymore I don't know if I’m a PG expert, but I just prefer “migration tool” to be a separate thing, and for the DB server engine to focus on being an excellent DB server engine, with the right hooks to support a robust tooling ecosystem, rather than trying to be the tooling ecosystem.
- JohnBooty 5y ago
  As a developer I fully support the notion of splitting the tools out from the server engine, like things are today. But, realistically, pg_upgrade's functionality would need to be integrated into the server itself if we're ever going to have zero-downtime upgrades, right? I don't know how other RDBMSs handle this, if at all
  - dragonwriter 5y ago
    Yeah, the comment I was responding to addressed two different kinds of migration—schema migration and version upgrades—and my comment really applies more to schema migration than version upgrades; more support for smoothing the latter in the engine makes sense.
    - JohnBooty 5y ago
      Agreed: Postgres' transactional schema migration is freaking sublime. I used and abused it pretty hard at my previous gig and now it's hard to imagine ever living without it. At my gig before THAT, we had MySQL and schema migrations were so very very painful.
tomhallett 5y ago
An orthogonal migration issue which I'm hitting right now: we need to migrate from heroku postgres to aws rds postgres, and I'm stressed about the risk and potential downtime in doing so. If there was a way to make a replica in rds based on heroku, promote the rds replica to be the primary, hard switch our apps over to rds, that'd be a lifesaver. I'm working through this blog post [1] now, but there is still a bit to be defined (including a dependency on heroku's support team) to get this rolling. Why the migration is required? Heroku postgres doesn't support logical replication, and logical replication is required for any ELT vendor (Fivetran, Stitch, Airbyte) to use Change Data Capture to replicate data from postgres to snowflake (with replicating deleted rows efficiently). Note: I've also read this ebook [2], but this approach requires downtime. Note 2: I reached out to heroku support and asked if logical replication was on their short term roadmap. They said they've heard this quite a bit, but nothing tangible is on the roadmap. If anyone has any thoughts on the above migration, I'd be all ears. :) 1) https://vericred.com/how-we-migrated-a-1tb-database-from-heroku-to-aws-aurora-with-almost-no-downtime/ https://vericred.com/how-we-migrated-a-1tb-database-from-her... 2) https://pawelurbanek.com/heroku-migrate-postgres-rds https://pawelurbanek.com/heroku-migrate-postgres-rds
- bastawhiz 5y ago
  I did that exact migration. Unfortunately, to my knowledge, there's no way to do it with zero downtime. You need to make your app read only until the RDS instance has ingested your data, then you can cut over. For me, that was roughly one gigabyte of data and took about forty seconds. My best advice is to automate the whole thing. You can automate it with the Heroku and AWS CLIs. Test on your staging site until you can run through the whole process end to end a few times with no interruptions.
  - tomhallett 5y ago
    Thank you for this - extremely helpful in validating the current approach and de-risking the developer time.
  - oauea 5y ago
    Yep, absolutely garbage that these clouds (Azure is another one) don't allow you to replicate with external systems. Pretty much devalues their entire hosted postgresql offering if you ask me, since it's just designed to keep you locked in (duh). If you have any significant amount of data where you're worried about a migration, stay far away from hosted postgres offerings. You'll never get your data out without significant downtime.
jeltz 5y ago
Migrations are quite highly prioritized in PostgreSQL. PostgreSQL has the by far best migration support of any database I have worked with. There is of course a lot of work left to do but that is true for many other areas in PostgreSQL. Also I can't agree at all with "nobody coded it", patches in this area generally welcome. My first real patch for PostgreSQL was about improving migrations. And there has been talk about several of the ideas you propose, but nobody is working on any of them right now. So I mostly think it is a lack of resources in general.
- oauea 5y ago
  DDL migrations are amazing. Migrations between different postgres versions not so much.
- skrebbel 5y ago
  Sorry, I didn't mean offense. What I meant with "nobody coded it" is that the migration DX features that don't exist yet, likely don't exist simply because they haven't been made yet (and not because eg they're architecturally impossible or because the postgres team are stupid or sth). Its hard to complain about OSS without attacking the creators, I tried to do that right buy clearly I failed nevertheless :-) Thanks for your contributions!
  - nix23 5y ago
    >Sorry, I didn't mean offense. Don't be a SJP, you have the right to offend someone when he talks bs.
    - burnished 5y ago
      What does SJP mean?
      - deleted 5y ago
        [deleted]
      - CRConrad 5y ago
phamilton 5y ago
They are slowly getting there. For example, postgres 12 added REINDEX CONCURRENTLY. Under the hood it's just recreating the index and then name swapping and dropping the old one. Basically what pg_repack was doing. There's a huge collection of tricks out there that just need to become formal features. The trick I'm working with today is adding a check constraint with NOT VALID and then immediately calling VALIDATE because otherwise it takes a very aggressive lock that blocks writes. That could easy become ALTER TABLE CONCURRENTLY or something.
- skrebbel 5y ago
  Do you know a good resource with these tricks? I often struggle to predict exactly which schema migrations will lock tables aggressively and what the smartest workaround is.
  - williamdclt 5y ago
    We've had good success with https://github.com/sbdchd/squawk https://github.com/sbdchd/squawk to lint migrations. It tells you if a query is going to lock your table (as long as it's written in SQL, not some ORM DSL)
throw0101a 5y ago
> If I may hijack the thread with some more general complaints though, I wish the Postgres team would someday prioritize migration. A thing I'm interested in is a 'simple' replication setup to reduce single points of failure. We currently use Galera with My/MariaDB/Percona and it's quite handy for HA-ish needs: we can have two DBs and the garbd running on the web app server. Pointers to tutorials for Debian/Ubuntu to accomplish something similar would be appreciated. (We run things on-prem in a private cloud.)
- zozbot234 5y ago
  There's no such thing as "simple" when it comes to HA setups, the requirements are simply too varied. PostgreSQL has great documentation for their HA featureset, but when it comes to systems-level concerns (detecting that a primary is down and arranging promotion of a replica to primary) you're expected to address those on your own.
  - throw0101a 5y ago
    With our Galera setup we have a keepalived health check look at the local system, and if it fails/times out it stops sending heart beats so the other sides takes over the vIP. If one system crashes the vIP fails over as well.
- fanf2 5y ago
  Take a look at Patroni, PAF, repmgr, Stolon, etc. https://github.com/zalando/patroni https://github.com/zalando/patroni https://clusterlabs.github.io/PAF/ https://clusterlabs.github.io/PAF/ https://repmgr.org/ https://repmgr.org/ https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon
brightball 5y ago
There’s a Ruby gem called strong_migrations that does this. It’s fantastic and I include it on all of my Ruby projects.
- why-el 5y ago
  The gem does not solve these issues, merely tells you about them (and even then it can't catch all of them, only the ones that Rails defines).
  - brightball 5y ago
    It gives you mitigation strategies. The mere presence of it on the dev workflow keeps your developers thinking about these types of issues as well, which goes a long way.
znpy 5y ago
My complain would be that there is no standard multi-master solution for postures, whereas mysql now has group replication as a native multi-master solution.

harikb 5y ago

Doesn’t PG already support inplace version upgrade? Also PG is one of the few that support schema/DDL statements inside a transaction.

skrebbel 5y ago
"one of the few" is a pretty low bar though, I don't know a DB that doesn't suck at this.
- jeltz 5y ago
  Or maybe it is you who are underestimating the technical complexity of the task? A lot of effort has been spent on making PostgreSQL as good as it is on migrations. Yes, it is not as highly prioritized as things like performance or partitioning but it is not forgotten either.

bmcahren 5y ago

We currently use MongoDB and while Postgres is attractive for so many reasons, even with Amazon Aurora's Postgres we still need legacy "database maintenance windows" in order to achieve major version upgrades. With MongoDB, you're guaranteed single-prior-version replication compatibility within a cluster. This means you spin up an instance with the updated version of MongoDB, it catches up to the cluster. Zero downtime, seamless transition. There may be less than a handful of cancelled queries that are retryable but no loss of writes with their retryable writes and write concern preferences. e.g. MongoDB 3.6 can be upgraded to MongoDB 4.0 without downtime. Edit: Possibly misinformed but the last deep dive we did indicated there was not a way to use logical replication for seamless upgrades. Will have to research.

deleted 5y ago
[deleted]
TedShiller 5y ago
The problem with MongoDB though is that you're on MongoDB
- salil999 5y ago
  And how exactly is that a problem?
  - mayankkaizen 5y ago
    That was sarcasm. But yeah, you can search for MongoDB and you'll come across many many posts criticizing it. It can be said Mongodb is hated ad much as Postgres is loved. Personally I have no opinion about mongodb.
    - The_Colonel 5y ago
      Yes, and most of these love/hate memes are blowned out of proportion by people who don't actually have any real expertise in those technologies, but just parrot whatever they've read in some memes.
      - staticassertion 5y ago
        You're exactly correct. Tons of "XYZ is bad" because of some meme that they don't even understand or have context on that hasn't been relevant for years. I have no idea if MongoDB is good or bad at this point, but the comments of "haha it's mongo" are completely devoid of meaningful content and should be flagged.
        operator9A 5y ago
        I was part of a team that operated a large Mongo cluster for most of the last decade. I would not have advised anyone to use Mongo as their durable source of truth database then, and I still don't think it's advisable to do so now. On numerous occasions, Mongo demonstrated the consequences of poor engineering judgment and an addled approach to logic in critical components responsible for data integrity. In addition, Mongo internalized many poor patterns with respect to performance and change management. Mongo did not, and does not provide the data integrity or performance guarantees that other databases internalize by design (the WiredTiger transition helped, but did not cure many of the issues). PostgreSQL introduced JSONB GIN index support sometime around 2015, making Postgres a better fit for most JSON-based applications than Mongo.

dragonwriter 5y ago

> I feel like there's a certain level of stockholm syndrome in the sense that to PG experts, these things aren't that painful anymore I don't know if I’m a PG expert, but I just prefer “migration tool” to be a separate thing, and for the DB server engine to focus on being an excellent DB server engine, with the right hooks to support a robust tooling ecosystem, rather than trying to be the tooling ecosystem.

JohnBooty 5y ago
As a developer I fully support the notion of splitting the tools out from the server engine, like things are today. But, realistically, pg_upgrade's functionality would need to be integrated into the server itself if we're ever going to have zero-downtime upgrades, right? I don't know how other RDBMSs handle this, if at all
- dragonwriter 5y ago
  Yeah, the comment I was responding to addressed two different kinds of migration—schema migration and version upgrades—and my comment really applies more to schema migration than version upgrades; more support for smoothing the latter in the engine makes sense.
  - JohnBooty 5y ago
    Agreed: Postgres' transactional schema migration is freaking sublime. I used and abused it pretty hard at my previous gig and now it's hard to imagine ever living without it. At my gig before THAT, we had MySQL and schema migrations were so very very painful.

tomhallett 5y ago

An orthogonal migration issue which I'm hitting right now: we need to migrate from heroku postgres to aws rds postgres, and I'm stressed about the risk and potential downtime in doing so. If there was a way to make a replica in rds based on heroku, promote the rds replica to be the primary, hard switch our apps over to rds, that'd be a lifesaver. I'm working through this blog post [1] now, but there is still a bit to be defined (including a dependency on heroku's support team) to get this rolling. Why the migration is required? Heroku postgres doesn't support logical replication, and logical replication is required for any ELT vendor (Fivetran, Stitch, Airbyte) to use Change Data Capture to replicate data from postgres to snowflake (with replicating deleted rows efficiently). Note: I've also read this ebook [2], but this approach requires downtime. Note 2: I reached out to heroku support and asked if logical replication was on their short term roadmap. They said they've heard this quite a bit, but nothing tangible is on the roadmap. If anyone has any thoughts on the above migration, I'd be all ears. :) 1) https://vericred.com/how-we-migrated-a-1tb-database-from-heroku-to-aws-aurora-with-almost-no-downtime/ https://vericred.com/how-we-migrated-a-1tb-database-from-her... 2) https://pawelurbanek.com/heroku-migrate-postgres-rds https://pawelurbanek.com/heroku-migrate-postgres-rds

bastawhiz 5y ago
I did that exact migration. Unfortunately, to my knowledge, there's no way to do it with zero downtime. You need to make your app read only until the RDS instance has ingested your data, then you can cut over. For me, that was roughly one gigabyte of data and took about forty seconds. My best advice is to automate the whole thing. You can automate it with the Heroku and AWS CLIs. Test on your staging site until you can run through the whole process end to end a few times with no interruptions.
- tomhallett 5y ago
  Thank you for this - extremely helpful in validating the current approach and de-risking the developer time.
- oauea 5y ago
  Yep, absolutely garbage that these clouds (Azure is another one) don't allow you to replicate with external systems. Pretty much devalues their entire hosted postgresql offering if you ask me, since it's just designed to keep you locked in (duh). If you have any significant amount of data where you're worried about a migration, stay far away from hosted postgres offerings. You'll never get your data out without significant downtime.

jeltz 5y ago

Migrations are quite highly prioritized in PostgreSQL. PostgreSQL has the by far best migration support of any database I have worked with. There is of course a lot of work left to do but that is true for many other areas in PostgreSQL. Also I can't agree at all with "nobody coded it", patches in this area generally welcome. My first real patch for PostgreSQL was about improving migrations. And there has been talk about several of the ideas you propose, but nobody is working on any of them right now. So I mostly think it is a lack of resources in general.

oauea 5y ago
DDL migrations are amazing. Migrations between different postgres versions not so much.
skrebbel 5y ago
Sorry, I didn't mean offense. What I meant with "nobody coded it" is that the migration DX features that don't exist yet, likely don't exist simply because they haven't been made yet (and not because eg they're architecturally impossible or because the postgres team are stupid or sth). Its hard to complain about OSS without attacking the creators, I tried to do that right buy clearly I failed nevertheless :-) Thanks for your contributions!
- nix23 5y ago
  >Sorry, I didn't mean offense. Don't be a SJP, you have the right to offend someone when he talks bs.
  - burnished 5y ago
    What does SJP mean?
    - deleted 5y ago
      [deleted]
    - CRConrad 5y ago

phamilton 5y ago

They are slowly getting there. For example, postgres 12 added REINDEX CONCURRENTLY. Under the hood it's just recreating the index and then name swapping and dropping the old one. Basically what pg_repack was doing. There's a huge collection of tricks out there that just need to become formal features. The trick I'm working with today is adding a check constraint with NOT VALID and then immediately calling VALIDATE because otherwise it takes a very aggressive lock that blocks writes. That could easy become ALTER TABLE CONCURRENTLY or something.

skrebbel 5y ago
Do you know a good resource with these tricks? I often struggle to predict exactly which schema migrations will lock tables aggressively and what the smartest workaround is.
- williamdclt 5y ago
  We've had good success with https://github.com/sbdchd/squawk https://github.com/sbdchd/squawk to lint migrations. It tells you if a query is going to lock your table (as long as it's written in SQL, not some ORM DSL)

throw0101a 5y ago

> If I may hijack the thread with some more general complaints though, I wish the Postgres team would someday prioritize migration. A thing I'm interested in is a 'simple' replication setup to reduce single points of failure. We currently use Galera with My/MariaDB/Percona and it's quite handy for HA-ish needs: we can have two DBs and the garbd running on the web app server. Pointers to tutorials for Debian/Ubuntu to accomplish something similar would be appreciated. (We run things on-prem in a private cloud.)

zozbot234 5y ago
There's no such thing as "simple" when it comes to HA setups, the requirements are simply too varied. PostgreSQL has great documentation for their HA featureset, but when it comes to systems-level concerns (detecting that a primary is down and arranging promotion of a replica to primary) you're expected to address those on your own.
- throw0101a 5y ago
  With our Galera setup we have a keepalived health check look at the local system, and if it fails/times out it stops sending heart beats so the other sides takes over the vIP. If one system crashes the vIP fails over as well.
fanf2 5y ago
Take a look at Patroni, PAF, repmgr, Stolon, etc. https://github.com/zalando/patroni https://github.com/zalando/patroni https://clusterlabs.github.io/PAF/ https://clusterlabs.github.io/PAF/ https://repmgr.org/ https://repmgr.org/ https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon

brightball 5y ago

There’s a Ruby gem called strong_migrations that does this. It’s fantastic and I include it on all of my Ruby projects.

why-el 5y ago
The gem does not solve these issues, merely tells you about them (and even then it can't catch all of them, only the ones that Rails defines).
- brightball 5y ago
  It gives you mitigation strategies. The mere presence of it on the dev workflow keeps your developers thinking about these types of issues as well, which goes a long way.

znpy 5y ago

My complain would be that there is no standard multi-master solution for postures, whereas mysql now has group replication as a native multi-master solution.