5 ms·

"The replication procedure is super fragile, prone to error, relies on a handful of random shell scripts, and is badly documented" This is true of many databas

"The replication procedure is super fragile, prone to error, relies on a handful of random shell scripts, and is badly documented" This is true of many databases, but in my experience is particularly true of postgres. It's a marvelous single-instance product, but I've never really found any replication/ha tech for it that I've been that happy with. I've always been a bit nervous about postgres-backed products for this very reason. I'd be interested what other people's take on this is, though.

dijit 10y ago
I don't share your observation at all. If we're talking normal replication then I can tell you for certain that the built in replication system is completely rock solid (especially compared to MySQL). If you're talking multi-master replication, then Citus is pretty solid, but not nearly as solid as replication. If you're talking statement based replication, well, it's the same as all databases. Here be dragons.
- jfindley 10y ago
  The built-in replication may be good - but it's pretty new. Replication slots, which are IMO vital for making the built-in replication non-fragile, only arrived in 9.4, which is pretty recent of a release. I wonder how widely tested it is, given that? How many people are actually using it for large workloads? Citrus I hadn't heard of though - that's interesting, thanks.
  - dmichulke 10y ago
    > only arrived in 9.4, which is pretty recent of a release. Release Date: 2014-12-18 (from https://www.postgresql.org/docs/9.4/static/release-9-4.html https://www.postgresql.org/docs/9.4/static/release-9-4.html) Two years do surely not confer the status of "battle-tested" but I wouldn't call it "recent" either. Then again, DB service level and a standard application service level might differ by a few nines/sigma here. Is it that what you're referring to?
    - jfindley 10y ago
      Okay, it's less recent than I remembered (how time flies!). Still, users are typically fairly slow to update their DB software, so I suspect 9.4 is still a pretty small percentage of the installed base at this point.
koolba 10y ago
Any replication / backup / restore process that is not regularly tested to verify that it works is fragile. Even more so when it's a series of manual steps to stonith, promote, etc. There's nothing particularly fragile about Postgres and there's a wealth of backup options ranging from logical (pg_dump), to replication (built-in WAL based), to physical archiving (pg_basebackup + WAL archiving). There are a number of cloud DBaaS vendors that run huge fleets of Postgres databases (Heroku and Amazon RDS come to mind). This is a solved problem. The root of this clusterfuck for GitLab is that they didn't test anything. Looks like there was some cargo culting of "Yah we need backups so let's run something ... yada yada S3 ..." but no verification of anything actually being backed up, or error alerts for missed backups, let alone testing a full restoration from said backups.
spudlyo 10y ago
I've been a DBA for thousands of MySQL hosts in production and just a handful of PostgreSQL clusters, so I'm not a PostgreSQL expert by any stretch. These PostgreSQL machines were also inherited from a company we acquired, so some of my negative impressions can likely be explained by them being set up poorly, which sadly seems to be the norm. PostgreSQL's replication has evolved slowly over the years, and as a result it's idiosyncratic, complicated to get right, and hard to generalize about. Postgres replication works by shipping WAL (transaction/redo) logs to slaves, and the slaves are in a constant state of DB recovery. Streaming replication does so as writes happen, and file based log shipping copies the logs when the 16MB segment is complete. Ours were set up to use streaming replication, without having a separate file-based archive log host to use to fetch older logs. Whenever the write load became too much and the slaves could no longer keep up with the master WAL logs on the master would expire and replication would break entirely, requiring you to rebuild all your slaves from backup. This was my introduction to PG replication. Apparently PG 9.4 has 'replication slots' or something that make this less of a headache. WAL logs were also applied without checksums or verification which corrupted the entire replication chain, and as I remember we didn't discover this until the corrupt pages got hit by a query. Software upgrades with PG seemed to be a huge pain as well, we could never figure out how long they were going to take, and even if the on-disk formats didn't change, somehow all the cardinality stats on our tables disappeared, and all our query plans went to shit until we ran some process to rebuild them, which took days. Operationally there were some things that made our team angry. We couldn't figure out how to reparent a slave without completely re-cloning it from the new parent, even though it was completely up to date from the authoritative master at the time of the reparenting. Also, do you really have to take down the entire cluster to change max connections on a slave? Many such settings seemed to not be dynamic and must remain in sync across the entire chain. One of the things people seem to love about PG is how correct and proper it is respecting the sanctity of your data. As an ops person I'd much rather deal with slightly inconsistent replicas (common in MySQL) than have to fight with how rigid PG is.

dijit 10y ago

I don't share your observation at all. If we're talking normal replication then I can tell you for certain that the built in replication system is completely rock solid (especially compared to MySQL). If you're talking multi-master replication, then Citus is pretty solid, but not nearly as solid as replication. If you're talking statement based replication, well, it's the same as all databases. Here be dragons.

jfindley 10y ago
The built-in replication may be good - but it's pretty new. Replication slots, which are IMO vital for making the built-in replication non-fragile, only arrived in 9.4, which is pretty recent of a release. I wonder how widely tested it is, given that? How many people are actually using it for large workloads? Citrus I hadn't heard of though - that's interesting, thanks.
- dmichulke 10y ago
  > only arrived in 9.4, which is pretty recent of a release. Release Date: 2014-12-18 (from https://www.postgresql.org/docs/9.4/static/release-9-4.html https://www.postgresql.org/docs/9.4/static/release-9-4.html) Two years do surely not confer the status of "battle-tested" but I wouldn't call it "recent" either. Then again, DB service level and a standard application service level might differ by a few nines/sigma here. Is it that what you're referring to?
  - jfindley 10y ago
    Okay, it's less recent than I remembered (how time flies!). Still, users are typically fairly slow to update their DB software, so I suspect 9.4 is still a pretty small percentage of the installed base at this point.

koolba 10y ago

Any replication / backup / restore process that is not regularly tested to verify that it works is fragile. Even more so when it's a series of manual steps to stonith, promote, etc. There's nothing particularly fragile about Postgres and there's a wealth of backup options ranging from logical (pg_dump), to replication (built-in WAL based), to physical archiving (pg_basebackup + WAL archiving). There are a number of cloud DBaaS vendors that run huge fleets of Postgres databases (Heroku and Amazon RDS come to mind). This is a solved problem. The root of this clusterfuck for GitLab is that they didn't test anything. Looks like there was some cargo culting of "Yah we need backups so let's run something ... yada yada S3 ..." but no verification of anything actually being backed up, or error alerts for missed backups, let alone testing a full restoration from said backups.

spudlyo 10y ago

I've been a DBA for thousands of MySQL hosts in production and just a handful of PostgreSQL clusters, so I'm not a PostgreSQL expert by any stretch. These PostgreSQL machines were also inherited from a company we acquired, so some of my negative impressions can likely be explained by them being set up poorly, which sadly seems to be the norm. PostgreSQL's replication has evolved slowly over the years, and as a result it's idiosyncratic, complicated to get right, and hard to generalize about. Postgres replication works by shipping WAL (transaction/redo) logs to slaves, and the slaves are in a constant state of DB recovery. Streaming replication does so as writes happen, and file based log shipping copies the logs when the 16MB segment is complete. Ours were set up to use streaming replication, without having a separate file-based archive log host to use to fetch older logs. Whenever the write load became too much and the slaves could no longer keep up with the master WAL logs on the master would expire and replication would break entirely, requiring you to rebuild all your slaves from backup. This was my introduction to PG replication. Apparently PG 9.4 has 'replication slots' or something that make this less of a headache. WAL logs were also applied without checksums or verification which corrupted the entire replication chain, and as I remember we didn't discover this until the corrupt pages got hit by a query. Software upgrades with PG seemed to be a huge pain as well, we could never figure out how long they were going to take, and even if the on-disk formats didn't change, somehow all the cardinality stats on our tables disappeared, and all our query plans went to shit until we ran some process to rebuild them, which took days. Operationally there were some things that made our team angry. We couldn't figure out how to reparent a slave without completely re-cloning it from the new parent, even though it was completely up to date from the authoritative master at the time of the reparenting. Also, do you really have to take down the entire cluster to change max connections on a slave? Many such settings seemed to not be dynamic and must remain in sync across the entire chain. One of the things people seem to love about PG is how correct and proper it is respecting the sanctity of your data. As an ops person I'd much rather deal with slightly inconsistent replicas (common in MySQL) than have to fight with how rigid PG is.