Uber migrated from Postgres to MySQL
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Show quoted text
Honestly, I've never heard of anyone doing that. But it sounds like they
had good reasons.https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/26/2016 10:39 AM, Guyren Howe wrote:
Honestly, I've never heard of anyone doing that. But it sounds like they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
Hello,
I started a thread about hackers on this.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats where the role of mysql ends. The bulk of the processing will be in nosql.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/26/2016 02:49 PM, Rakesh Kumar wrote:
This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats where the role of mysql ends. The bulk of the processing will be in nosql.
That doesn't mean they didn't bring up some very good points.
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent from mobile.
On Jul 26, 2016, at 5:56 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
On 07/26/2016 02:49 PM, Rakesh Kumar wrote:
This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats where the role of mysql ends. The bulk of the processing will be in nosql.
That doesn't mean they didn't bring up some very good points.
JD
Yes of course. But this is not one of those "product A sucked and we moved to product B and same workload and work is now 10 times better. "
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
They are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade
to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary
and library, need to do full dump and restore that take time and disk
space.
Regards,
Hristo S.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Just a few points on reading this.
First, the timeline bugs regarding replication (particularly iirc in the
9.1 days). I remember accidentally corrupting a (fortunately only
demonstration!) database cluster in the process of demonstrating promotion
at least once. Iirc last time I tried to reproduce these problems, they
had been fixed (by 9.3?).
The replication section made me wonder though if they were using the right
replication solution for the job. If you don't want an on-disk copy, don't
use physical replication. This being said there is one serious issue here
that is worth mentioning, which is that since autovacuum on the master has
no knowledge of autovacuum on the slave, it is easy to have longer-running
queries on a slave that have rows they need to see removed by autovacuum
and replication. This can of course be easily fixed (if your query takes
30 sec to run, every 30 sec open a minute-long transaction on the master,
which means that autovacuum can never clean rows that are older than 30
sec) but such is not a very robust solution and may cause more problems
than it is worth (the real solution is going to a logical replication
system where that is a problem). As I usually put it, streaming
replication is for cases where you need to guarantee an exact replica of
everything, while logical replication is where you need a copy of data for
use.
Finally, if I were trying to create something like schemaless, there is one
major limitation of PostgreSQL that is not mentioned here, which is TOAST
overhead. I have seen people try to do things like this and TOAST overhead
can be a real problem in these cases. If your data for a row won't easily
fit in significantly less than a page, then every read of that data and
every write can effectively do an implicit nested loop join. And if you
want to talk about write amplification...... But this is also very well
hidden and not easy to measure unless you know to look for it specifically
so it is possible that they ran into it and didn't know it but I don't have
any knowledge of what they did or tried so I could be totally off base
here. I would say I have seen more than one project run into this and
because explain analyze select * does not detoast....
All of the above being said, there are solutions to all the major
problems. But you have to know about them, where to look, and what to do.
And with higher scale, one very important aspect is that attention to
detail starts to matter a whole lot. I agree that there are some good
points raised but I wonder what the solutions are. There is room for some
improvement in the backend (it would really be nice to instrument and
measure toasting/detoasting overhead in explain analyze) but for a lot of
these I wonder if that is secondary. PostgreSQL is very well optimized
for a certain series of tasks, and one can build well optimized solutions
well outside that. At a certain point (including a certain scale)
therewill be no substitute for a teamof people who really know the db
backend inside and out who can design around limitations and I think that
is true for all databases I have worked with.
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like they
had good reasons.https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
On Jul 27, 2016, at 00:15, Condor <condor@stz-bg.com> wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalThey are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore that take time and disk space.
Not having tried to use pg_upgrade on a 1TB database, this might not apply, but pg_upgrade has a —link option that should greatly minimize the time required to upgrade, since it eliminates the duplication of files … apparently even works on Windows:
“—link … use hard links instead of copying files to the new cluster (use junction points on Windows)"
In fact, the —link option should also mean removing the requirement for ‘double the disk space’ to do the upgrade …
I don’t have a 1TB database to try it on, mind you, so your ‘wait couple of days’ might be *with* the —link option?
--
Marc G Fournier http://www.2ndQuadrant.com <http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
On 27/07/2016 10:15, Condor wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalThey are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and library, need to do full dump and restore that take time and disk space.
Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like seconds. (with the -k option)
However, be warned that the planing and testing took one full week.
Regards,
Hristo S.
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
On 27/07/2016 10:15, Condor wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalThey are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade
to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary and
library, need to do full dump and restore that take time and disk space.Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like
seconds. (with the -k option)
However, be warned that the planing and testing took one full week.
That being said, it doesn't really provide a back-out plan. The beauty of
replication is that you can halt the upgrade at any point if need be and
cut your (hopefully small) losses. If you use -k, you are all in. Sure,
you could setup a new standby, stop traffic, upgrade whichever node you'd
like (using -k) and still have the other ready in the event of total
catastrophe. More often than not, I see DBAs and sysads lead the
conversation with "well, postgres can't replicate from one version to
another, so instead.... " followed by a fast-glazing of management's eyes
and a desire to buy a 'commercial database'.
All in all, Evan's blog seemed to start out decently technical, it quickly
took a turn with half-truths, outdated information and, in some cases,
downright fud:
"The bug we ran into only affected certain releases of Postgres 9.2 and
has been fixed for a long time now. However, we still find it worrisome
that this class of bug can happen at all. A new version of Postgres could
be released at any time that has a bug of this nature, and because of the
way replication works, this issue has the potential to spread into all of
the databases in a replication hierarchy."
ISTM that they needed a tire swing
<http://i0.wp.com/blogs.perficient.com/perficientdigital/files/2011/07/treecomicbig.jpg>
and were using a dump truck. Hopefully they vectored somewhere in the
middle and got themselves a nice sandbox.
--Scott
Regards,
Hristo S.--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com>*
http://openscg.com
On Wed, Jul 27, 2016 at 10:22:27AM -0400, Scott Mead wrote:
That being said, it doesn't really provide a back-out plan.� The beauty of
replication is that you can halt the upgrade at any point if need be and cut
your (hopefully small) losses. If you use -k, you are all in.� Sure, you could
setup a new standby, stop traffic, upgrade whichever node you'd like (using -k)
and still have the other ready in the event of total catastrophe.� More often
than not, I see DBAs and sysads lead the conversation with "well, postgres
can't replicate from one version to another, so instead.... " followed by a
fast-glazing of management's eyes and a desire to buy a 'commercial database'.�
I agree, but I am not sure how to improve it. The big complaint I have
heard is that once you upgrade and open up writes on the upgraded
server, you can't re-apply those writes to the old server if you need to
fall back to the old server. I also don't see how to improve that either.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27 July 2016 at 15:22, Scott Mead <scottm@openscg.com> wrote:
"The bug we ran into only affected certain releases of Postgres 9.2 and
has been fixed for a long time now. However, we still find it worrisome
that this class of bug can happen at all. A new version of Postgres could
be released at any time that has a bug of this nature, and because of the
way replication works, this issue has the potential to spread into all of
the databases in a replication hierarchy."ISTM that they needed a tire swing
<http://i0.wp.com/blogs.perficient.com/perficientdigital/files/2011/07/treecomicbig.jpg>
and were using a dump truck. Hopefully they vectored somewhere in the
middle and got themselves a nice sandbox.
At least his bug got fixed. The last 2 bugs I reported to MySQL resulted in
an initial refusal to accept any problem existed, followed by (once that
particular strategy had run out of steam) the developer simply ignoring the
bug until it was closed automatically by their bug system. As far as I'm
aware those bugs still exist in the most recent version.
Admittedly they weren't data-corruption bugs but they were of the "this
will cause your application to unnecessarily come to a complete halt for a
couple of hours once your dataset grows beyond a certain point" variety.
As others have pointed out, db changes very rarely happen because of
technical reasons. Most developers will harp on at their boss about how
terrible their current database is and how <preferred database> performs
much better. Eventually one of two things happens: either a) those
developers end up in a position where their direct boss is in a position to
make the change and he or she doesn't understand how much time and money it
will actually take to change; or b) commercial considerations dictate the
change.
The amount of money and time they've wasted making this change (and
whatever anyone tells you these things never come for free) would have been
better invested in employing one of the commercial PG companies to improve
the specific postgres problems they found. The fact that they decided to go
the MySQL route suggests to me that this was a political, not technical,
change, and they're now reverse-justifying.
For what it's worth, from what I've read uber are a company whose very
business plan relies on them taking things that they don't deserve while
they treat customers and employees with similar levels of arrogance.
Frankly I'd rather there were as many levels of separation as possible
between me and them: they and Oracle are welcome to each other, it seems
like a marriage made in heaven.
Geoff
On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote:
technical reasons. Most developers will harp on at their boss about how
terrible their current database is and how <preferred database> performs
much better. Eventually one of two things happens: either a) those
developers end up in a position where their direct boss is in a position to
make the change and he or she doesn't understand how much time and money it
will actually take to change; or b) commercial considerations dictate the
change.
In a different context, someone suggested to me that Postgres
advocates sounded to him too often like FreeBSD advocates complaining
about Linux, and I'm afraid there is a certain truth to that. Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:
1. They'd decided to use a NoSQL database and ditch relational
systems, because shards.
2. They wanted an MVCC engine behind the above.
3. They wanted SQL semantics to this MVCC-enabled filesystem layer.
Sounds just like MySQL+InnoDB to me. Once you've already decided on
(1), the rest of it flows pretty naturally and Postgres is probably
not your choice. You can dismiss any of 1-3 as commerical or
political advocacy, but while I happen to think they're a somewhat
questionable set of goals they're not obviously stupid, and
competent people of good will could disagree about them.
At the same time, there really are two serious problems with Postgres
under heavy write loads. Postgres's focus on readers' speed and
convenience means you have to take the hit somewhere, so writers take
it instead. (The other side of the disk-layout description in the
blog post is that, under MySQL, secondary index use is more expensive
for readers than it is in Postgres. The post acknowledges that, but
of course most important secondary indexing is useless under sharding
anyway, since you have to select from shards; so they won't care.)
I/O storms on Postgres are a major source of pain for large operators,
and the tools for understanding are sort of primitive because many of
them depend on underlying OS features and tools.
The second is the upgrade-by-replica-and-fallback-plan problem. It's
really an issue. There is a reason that, back in the cloudy past, we
designed Slony to be able to replicate to and from any supported
version of Postgres: Afilias needed to be able to upgrade without a
lot of down time and with the ability to roll back if we had to,
because that was our contractual obligation. This has always been a
large gap, and when it was raised in the past the answer was, "Well,
Slony can already do that so use it." It wasn't too satisfying then,
and it's not much more satisfying now. :)
better invested in employing one of the commercial PG companies to improve
the specific postgres problems they found.
I think the two big problems laid out above are deep architectural
ones. I'm not sure these are the sort of improvement you can buy
without getting the community on board.
For what it's worth, from what I've read uber are a company whose very
business plan relies on them taking things that they don't deserve while
they treat customers and employees with similar levels of arrogance.
Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.
Best regards,
A
--
Andrew Sullivan
ajs@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:
I agree, but I am not sure how to improve it. The big complaint I have
heard is that once you upgrade and open up writes on the upgraded
server, you can't re-apply those writes to the old server if you need to
fall back to the old server. I also don't see how to improve that either.
doesn't and pg_logical solve this by logically replicating and allowing for
different architecture/version between the replication nodes ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27 July 2016 at 17:11, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:
Well yes, but that's pretty-much the point of back-justification, isn't it?
[snip a whole bunch of good points]
For what it's worth, from what I've read uber are a company whose very
business plan relies on them taking things that they don't deserve while
they treat customers and employees with similar levels of arrogance.Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.
Oh, I wasn't using as an argument about anything (hence "for what it's
worth").
G
On 27 July 2016 at 17:11, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
Show quoted text
On Wed, Jul 27, 2016 at 04:51:42PM +0100, Geoff Winkless wrote:
technical reasons. Most developers will harp on at their boss about how
terrible their current database is and how <preferred database> performs
much better. Eventually one of two things happens: either a) those
developers end up in a position where their direct boss is in a positionto
make the change and he or she doesn't understand how much time and money
it
will actually take to change; or b) commercial considerations dictate the
change.In a different context, someone suggested to me that Postgres
advocates sounded to him too often like FreeBSD advocates complaining
about Linux, and I'm afraid there is a certain truth to that. Given
the discussion in the post in question, the decision to use MySQL
appears to have been well-justified:1. They'd decided to use a NoSQL database and ditch relational
systems, because shards.2. They wanted an MVCC engine behind the above.
3. They wanted SQL semantics to this MVCC-enabled filesystem layer.
Sounds just like MySQL+InnoDB to me. Once you've already decided on
(1), the rest of it flows pretty naturally and Postgres is probably
not your choice. You can dismiss any of 1-3 as commerical or
political advocacy, but while I happen to think they're a somewhat
questionable set of goals they're not obviously stupid, and
competent people of good will could disagree about them.At the same time, there really are two serious problems with Postgres
under heavy write loads. Postgres's focus on readers' speed and
convenience means you have to take the hit somewhere, so writers take
it instead. (The other side of the disk-layout description in the
blog post is that, under MySQL, secondary index use is more expensive
for readers than it is in Postgres. The post acknowledges that, but
of course most important secondary indexing is useless under sharding
anyway, since you have to select from shards; so they won't care.)
I/O storms on Postgres are a major source of pain for large operators,
and the tools for understanding are sort of primitive because many of
them depend on underlying OS features and tools.The second is the upgrade-by-replica-and-fallback-plan problem. It's
really an issue. There is a reason that, back in the cloudy past, we
designed Slony to be able to replicate to and from any supported
version of Postgres: Afilias needed to be able to upgrade without a
lot of down time and with the ability to roll back if we had to,
because that was our contractual obligation. This has always been a
large gap, and when it was raised in the past the answer was, "Well,
Slony can already do that so use it." It wasn't too satisfying then,
and it's not much more satisfying now. :)better invested in employing one of the commercial PG companies to
improve
the specific postgres problems they found.
I think the two big problems laid out above are deep architectural
ones. I'm not sure these are the sort of improvement you can buy
without getting the community on board.For what it's worth, from what I've read uber are a company whose very
business plan relies on them taking things that they don't deserve while
they treat customers and employees with similar levels of arrogance.Nothin' for nothin', but I don't think it helps Postgres to attack
others' business plans -- whatever one thinks of them -- as part of an
argument about why Postgres is the right tool for a given job.Best regards,
A
--
Andrew Sullivan
ajs@crankycanuck.ca--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote:
On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <bruce@momjian.us> wrote:
I agree, but I am not sure how to improve it. The big complaint I have
heard is that once you upgrade and open up writes on the upgraded
server, you can't re-apply those writes to the old server if you need to
fall back to the old server. I also don't see how to improve that either.doesn't and pg_logical solve this by logically replicating and allowing for
different architecture/version between the replication nodes ?
Yes. I was saying I don't know how to improve pg_upgrade to address it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <bruce@momjian.us> wrote:
Yes. I was saying I don't know how to improve pg_upgrade to address it.
This problem is there even in oracle/db2/sqlserver. None of them allow
rollback to the lower version
unless it is a minor version upgrade. Major version upgrade almost
definitely involves change in transaction log
(WAL) structure and hence no rollback.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 12:51:40PM -0400, Rakesh Kumar wrote:
On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <bruce@momjian.us> wrote:
Yes. I was saying I don't know how to improve pg_upgrade to address it.
This problem is there even in oracle/db2/sqlserver. None of them allow
rollback to the lower version
unless it is a minor version upgrade. Major version upgrade almost
definitely involves change in transaction log
(WAL) structure and hence no rollback.
Oh, good to know.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jul 27, 2016 at 4:22 PM, Scott Mead <scottm@openscg.com> wrote:
On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:On 27/07/2016 10:15, Condor wrote:
On 26-07-2016 21:04, Dorian Hoxha wrote:
Many comments: https://news.ycombinator.com/item?id=12166585
https://www.reddit.com/r/programming/comments/4uph84/why_uber_engineering_switched_from_postgres_to/
On Tue, Jul 26, 2016 at 7:39 PM, Guyren Howe <guyren@gmail.com> wrote:
Honestly, I've never heard of anyone doing that. But it sounds like
they had good reasons.
https://eng.uber.com/mysql-migration/
Thoughts?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-generalThey are right for upgrades.
It's a hard to shutdown 1 TB database and wait couple of days pg_upgrade
to finish upgrade and meanwhile database is offline.
In some distros after upgrade of PG version you don't have old binary
and library, need to do full dump and restore that take time and disk space.Our last 1TB upgrade from 9.0 -> 9.3 went like a charm in something like
seconds. (with the -k option)
However, be warned that the planing and testing took one full week.That being said, it doesn't really provide a back-out plan. The beauty of
replication is that you can halt the upgrade at any point if need be and
cut your (hopefully small) losses.
Replication though does have limits and one aspect of incremental backups
is you cannot restore from one major version to the next. Another one I
think they obliquely referred to (in the subtle problems section) was the
fact that if you have longer-running queries on the replica with a lot of
updates, you can get funny auto-vacuum-induced errors (writes from
autovacuum on the master can interrupt queries on the slave). BTW if there
is interest in what could be done for that, something which allows
autovacuum to decide how long to wait before cleaning up dead tuples would
be a great enhancement.
I was on a project once where I was told, "we use pg_dump for our upgrades"
for a multi-TB database. When asked why, the answer made a lot of sense.
Namely if something goes wrong you need to do a restore on the new version
from a logical backup anyway, so you have to take a pg_dump backup before
you start, and you might have to restore anyway. So the thinking was that
it was better to keep expectations low than promise low downtime and have a
two-week outage.
If you use -k, you are all in. Sure, you could setup a new standby, stop
traffic, upgrade whichever node you'd like (using -k) and still have the
other ready in the event of total catastrophe. More often than not, I see
DBAs and sysads lead the conversation with "well, postgres can't replicate
from one version to another, so instead.... " followed by a fast-glazing of
management's eyes and a desire to buy a 'commercial database'.
This is one area where we need better presentation of what we have and what
it does.
Streaming replication works great for certain things, such as where you
have lots of small queries against the replica, where they don't have to be
absolutely up to date, or where what you are really after is guarantees
that you can keep moving after one of your servers suffers a catastrophic
failure.
Where the guarantee that the two systems are guaranteed identical on the
filesystem level, it is great. Where that is not what you want, it is a
pretty bad solution. But then there is Slony, Bucardo, and other logical
replication solutions out there (plus the newer logical replication
approaches in PostgreSQL) which handle the other situations very well (with
a very different sort of added complexity).
All in all, Evan's blog seemed to start out decently technical, it quickly
took a turn with half-truths, outdated information and, in some cases,
downright fud:"The bug we ran into only affected certain releases of Postgres 9.2 and
has been fixed for a long time now. However, we still find it worrisome
that this class of bug can happen at all. A new version of Postgres could
be released at any time that has a bug of this nature, and because of the
way replication works, this issue has the potential to spread into all of
the databases in a replication hierarchy."ISTM that they needed a tire swing
<http://i0.wp.com/blogs.perficient.com/perficientdigital/files/2011/07/treecomicbig.jpg>
and were using a dump truck. Hopefully they vectored somewhere in the
middle and got themselves a nice sandbox.
My first thought was, "If they know the database that well, surely they
could have built something that would work well!"
However, for what they seem to want to do specifically, MySQL might not
actually be a bad choice. In a case like what they are doing, nearly all
of your lookups are probably simple, primary key lookups and there InnoDB's
design helps more than it hurts. If I were to think of one are that MySQL
probably would do better, it would be looking up documents based on simple
primary key searches (no joins, no relational math, no need for complex
plans, just a single primary key index lookup). But this is also a reason
we might not want to worry about this sort of thing too much. Of course
NFS might be another alternative at that level of complexity....
So yeah, a sandbox ;-)
--Scott
Regards,
Hristo S.--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com>*
http://openscg.com
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
