Thread: 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?

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.

This is an old news. They are using mysql as a nosql to store schemaless. Basically one giant blob col. And thats where
therole of mysql ends. The bulk of the processing will be in nosql.  

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
wherethe 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 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
wherethe 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
now10 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.

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.

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-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
thattake 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

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 +

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

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 ?

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 +

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.

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 +

On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote:
> 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.

I think long-term we are looking at pg_logical for zero-downtime
upgrades and _downgrades_, and pg_upgrade for less overhead (I don't
want to make a second copy of my data) upgrades (but not downgrades).

I think this is probably the best we are going to be able to do for a
long time.

--
  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 +

On Wed, Jul 27, 2016 at 09:17:58AM +0200, Chris Travers wrote:
> 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. 

Uh, see hot_standby_feedback:

    #hot_standby_feedback = off             # send info from standby to prevent
                                            # query conflicts

> 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.

Watching the video was helpful:

    https://vimeo.com/145842299

You can see the failover happened because of various user errors.  That
doesn't excuse our bug, but I am not sure exactly how much they
understood of Postgres behavior.  His talk near the end about the
replication infrastucture being exposed to them was also interesting.

--
  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 +

On Wed, Jul 27, 2016 at 12:59:59PM -0400, Bruce Momjian wrote:
> On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote:
> > 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.
>
> I think long-term we are looking at pg_logical for zero-downtime
> upgrades and _downgrades_, and pg_upgrade for less overhead (I don't
> want to make a second copy of my data) upgrades (but not downgrades).
>
> I think this is probably the best we are going to be able to do for a
> long time.

Oh, let me give credit to Simon, who has always seen pg_logical as
providing superior upgrade options where the logical replication setup
isn't a problem.

--
  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 +

On 27/07/16 18:54, Chris Travers wrote:
> 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.

You mean something like hot_standby_feedback?

https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Wed, Jul 27, 2016 at 10:54:25AM -0700, Marc Fournier wrote:
> http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-paths
>
> ==
>
>        Unless otherwise documented, the following downgrade paths are
> supported:
>
>   • Downgrading from a release series version to an older release series
>     version is supported using all downgrade methods. For example, downgrading
>     from 5.7.10 to 5.7.9 is supported. Skipping release series versions is also
>     supported. For example, downgrading from 5.7.11 to 5.7.9 is supported.
>
>   • Downgrading one release level is supported using the logical downgrade
>     method. For example, downgrading from 5.7 to 5.6 is supported.
>
>   • Downgrading more than one release level is supported using the logical
>     downgrade method, but only if you downgrade one release level at a time.
>     For example, you can downgrade from 5.7 to 5.6, and then to 5.5.
>
> ==
>
> So, downgrade minor releases can be done by just changing the binaries …
> downgrading an older ‘major release’ requires a dump/reload …
>
> Unless I’m missing something, whether on PostgreSQL or MySQL, if you want to go
> back a major release, you would need to dump./ reload that 1TB database …

What they wanted, and I think was mentioned in the document, was that
they wanted to upgrade the slaves independently, then the master.  I
think MySQL supports that, Postgres doesn't.

--
  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 +

On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier
<marc.fournier@2ndquadrant.com> wrote:

> Stupid question here, but do we provide any less then what MySQL does?   I’m
> reading:

mysql provides same functionality for rollback like oracle/db2
provides. That is,
rollback on a minor version upgrade possible, but not on major version upgrade.

I am surprised PG does not even allow minor version rollback.

On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote:
>
> I am surprised PG does not even allow minor version rollback.

It almost never happens that a minor version (N.M.x, x is minor)
requires an upgrade at all.  Change your binaries and you're done.
Catalogue incompatibility historically was the basis for something
becoming a major version upgrade.  (I can recall a couple bugs where
you had to tickle the catalogues, so it's not exactly true that
they're never incompatible, but it's incredibly rare.)

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote:
> On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier
> <marc.fournier@2ndquadrant.com> wrote:
>
> > Stupid question here, but do we provide any less then what MySQL does?   I’m
> > reading:
>
> mysql provides same functionality for rollback like oracle/db2
> provides. That is,
> rollback on a minor version upgrade possible, but not on major version upgrade.
>
> I am surprised PG does not even allow minor version rollback.

Uh, I thought we did.  Perhaps there a few that didn't.

--
  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 +

On Wed, Jul 27, 2016 at 2:07 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> It almost never happens that a minor version (N.M.x, x is minor)
> requires an upgrade at all.  Change your binaries and you're done.
> Catalogue incompatibility historically was the basis for something
> becoming a major version upgrade.  (I can recall a couple bugs where
> you had to tickle the catalogues, so it's not exactly true that
> they're never incompatible, but it's incredibly rare.)

Yeah good enough.

On Wed, Jul 27, 2016 at 2:15 AM, Condor <condor@stz-bg.com> wrote:

> 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.

What?  I had a cluster over 3TB and it was offline for only 10
minutes doing a major version upgrade using pg_upgrade's --link
option.  Of course, that was with minimal statistics -- just enough
to keep from getting truly stupid plans.  If it takes *days* to run
pg_upgrade on a 1TB cluster either you have an insane number of
database objects or you are not taking advantage of the available
features.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jul 27, 2016 at 9:22 AM, Scott Mead <scottm@openscg.com> wrote:
> On Wed, Jul 27, 2016 at 3:34 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

>> 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.

Agreed -- you should spend a lot more time on planning and testing
than the actual upgrade will take; but that will probably be true
with any product.

> That being said, it doesn't really provide a back-out plan.

Until you get to the end of the upgrade and *start the cluster
under the new version*  you can fall back to the old version.  I
remember a couple times that we saw something during a pg_upgrade
--link run that we weren't expecting, and did exactly that so we
could investigate and try again later.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Jul 27, 2016 at 02:33:54PM -0500, Kevin Grittner wrote:
> Until you get to the end of the upgrade and *start the cluster
> under the new version*  you can fall back to the old version.

Yeah, but to be fair a lot of well-funded businesses (note what
started this discussion) are pico-managed by people way up the stack
who want a bite-sized answer.  If you do not have a happy story for,
"What if we're 48 hours into the upgrade and discover some critical
bug corner case that makes us need to roll back?" then you're going to
lose them.  Never mind that such cases literally never happen (if you
have a 48 hour old bug in an Internet system today, you have an
emergency bugfix, not a rollback).

A great deal of practical delivery of technology involves managing
expectations of management who do not understand what they are asking
for and basically want a glib happy answer.  As people delivering such
things, we must find a glib happy answer that does not get us fired if
it turns out to be false.  The poor story Postgres has about
downgrade, _even if it's a stupid problem_, is a problem.  It might
not be worth fixing because it's a stupid problem.  But one has to
face the critique in its own terms.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

I bet there are some people around here who could help you
troubleshoot.  Just sayin'  (I'm not among them.)

A

On Thu, Jul 28, 2016 at 09:34:05AM +1200, Patrick B wrote:
> We can't use the pg_upgrade in our 3TB database.... just does not work..
> that's the main reason we're still using 9.2.

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, Jul 27, 2016 at 4:34 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
> We can't use the pg_upgrade in our 3TB database.... just does not work..
> that's the main reason we're still using 9.2.

I think it's safe to say that that has absolutely nothing to do
with the size being 3TB.  They symptoms you report are a little
thin to diagnose the actual cause.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Patrick B wrote:
> >
> > I think it's safe to say that that has absolutely nothing to do
> > with the size being 3TB.  They symptoms you report are a little
> > thin to diagnose the actual cause.
>
> might be... we're using SATA disks... and that's a big problem. But still..
> the size of the DB is indeed a problem.

Andrew is correct -- the size of the database is not a limitation for
pg_upgrade.  Disk tech is not relevant either.  You may run into the
problem that you don't have enough disk space, but then that is not a
database or pg_upgrade problem, is it?

Other things might cause issues, but since you haven't actually reported
the problem, we don't know what is or whether there is any possible fix.

Then again, if you want to report a pg_upgrade failure, I suggest you
open a thread of your own rather than hijack this one.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Jul 27, 2016 at 07:02:52PM -0400, Alvaro Herrera wrote:
> Patrick B wrote:
> > >
> > > I think it's safe to say that that has absolutely nothing to do
> > > with the size being 3TB.  They symptoms you report are a little
> > > thin to diagnose the actual cause.
> >
> > might be... we're using SATA disks... and that's a big problem. But still..
> > the size of the DB is indeed a problem.
>
> Andrew is correct -- the size of the database is not a limitation for
> pg_upgrade.  Disk tech is not relevant either.  You may run into the
> problem that you don't have enough disk space, but then that is not a
> database or pg_upgrade problem, is it?
>
> Other things might cause issues, but since you haven't actually reported
> the problem, we don't know what is or whether there is any possible fix.
>
> Then again, if you want to report a pg_upgrade failure, I suggest you
> open a thread of your own rather than hijack this one.

You need only minimal disk space when using pg_upgrade --link.  I agree
we would like a full bug report so we can find a fix for you.

--
  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 +

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Marc wrote:
> 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?

I think you mean *without*, but yeah, there is no way the --link
option is going to take that long. Hard links are  awesome. We've upgraded
some really big databases, and --link is really, really fast.
If you can't use --link (usually because you want to get over
the checksum hump), we use something like Bucardo to help out.
No need to ever wait a "couple of days" as OP claims. :)

What really bites is the analyze afterwards. That's the part
that takes too long (yes, --in-stages helps some). Would love
to see progress made there.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201607272220
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAleZbGEACgkQvJuQZxSWSsiAngCgmsP0O9rMMmu43RQ6LkQJwMtP
IZcAn25UJWNs03q75iIEOVAabKHaBrWV
=zdDO
-----END PGP SIGNATURE-----

On Thu, Jul 28, 2016 at 02:23:18AM -0000, Greg Sabino Mullane wrote:
>
> Marc wrote:
> > 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?
>
> I think you mean *without*, but yeah, there is no way the --link
> option is going to take that long. Hard links are  awesome. We've upgraded
> some really big databases, and --link is really, really fast.
> If you can't use --link (usually because you want to get over
> the checksum hump), we use something like Bucardo to help out.
> No need to ever wait a "couple of days" as OP claims. :)
>
> What really bites is the analyze afterwards. That's the part
> that takes too long (yes, --in-stages helps some). Would love
> to see progress made there.

Agreed.  I will try to prioritize 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 +

On Wed, Jul 27, 2016 at 7:23 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:

> Marc wrote:
>> 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?
>
> I think you mean *without*, but yeah, there is no way the --link
> option is going to take that long.

That depends on how how many objects there are consuming that 1 TB.
With millions of small objects, you will have problems.  Not as many
in 9.5 as there were in 9.1, but still it does not scale linearly in
the number of objects.  If you only have thousands of objects, then as
far as I know -k works like a charm.

> Hard links are  awesome. We've upgraded
> some really big databases, and --link is really, really fast.
> If you can't use --link (usually because you want to get over
> the checksum hump), we use something like Bucardo to help out.
> No need to ever wait a "couple of days" as OP claims. :)
>
> What really bites is the analyze afterwards. That's the part
> that takes too long (yes, --in-stages helps some). Would love
> to see progress made there.

I'm of two minds about --in-stages.  On the one hand, it is totally
unprincipled.  Only two stages are in principle plausible, the stage
before you open your database for general use, and stage after you do
so.  (you could say there is a third stage, the one in which you do
EOW/EOM/EOY processing.  So don't run pg_upgrade on the second to last
day of the week or month, or towards the end of the second to the last
week of the year).  The current implementation of --in-stages uses
four stages, not two, and offers no convenient way to pause between
stages to open your database for general use.  On the other hand,
--in-stages is supposed to be pragmatic, not principled.  It is
supposed to work adequately over a wide variety of scenarios, even if
don't know ahead of time which scenario applies to you.

Cheers,

Jeff

On 7/27/2016 9:39 PM, Jeff Janes wrote:
> That depends on how how many objects there are consuming that 1 TB.
> With millions of small objects, you will have problems.  Not as many
> in 9.5 as there were in 9.1, but still it does not scale linearly in
> the number of objects.  If you only have thousands of objects, then as
> far as I know -k works like a charm.

millions of tables?  thats akin to having millions of classes in an
object oriented program, seems a bit excessive.



--
john r pierce, recycling bits in santa cruz

So, millions is a lot, but it's not difficult to get to a place where
you have thousands or tables.

Image a case in which census data and the associated geometries.
https://github.com/censusreporter/census-postgres has 22 surveys, each
with 230+ tables. That's 5000+ tables right there.  Now, the TIGER
tables for all of that is another 50 tables per year, so another 350
tables.

If these were to be partitioned by state, instead of all records for
all states in a single table, then we're looking at 270,000.

Jim

On Thu, Jul 28, 2016 at 12:48 AM, John R Pierce <pierce@hogranch.com> wrote:
> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>
>> That depends on how how many objects there are consuming that 1 TB.
>> With millions of small objects, you will have problems.  Not as many
>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> the number of objects.  If you only have thousands of objects, then as
>> far as I know -k works like a charm.
>
>
> millions of tables?  thats akin to having millions of classes in an object
> oriented program, seems a bit excessive.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> 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 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>
>> That depends on how how many objects there are consuming that 1 TB.
>> With millions of small objects, you will have problems.  Not as many
>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> the number of objects.  If you only have thousands of objects, then as
>> far as I know -k works like a charm.
>
>
> millions of tables?

Well, it was a problem at much smaller values, until we fixed many of
them.  But the perversity is, if you are stuck on a version before the
fixes, the problems prevent you from getting to a version on which it
is not a problem any more.

> thats akin to having millions of classes in an object
> oriented program, seems a bit excessive.

It is not outside the bounds of reason, in a multi-tenancy situation.
 Maybe you have a hundred tables and each table has two sequences and
7 indexes, on average.  Or 300 tables and fewer indices apiece.  But
then you have 1000 schemas each with the same, ah, schema.  I've
pursued these optimizations as an intellectual exercise, but I know
others have had more concrete motivations.

Cheers,

Jeff

On 28/07/16 17:52, Jason Dusek wrote:
> With regards to write amplification, it makes me think about about
> OIDs. Used to be, every row had an OID and that OID persisted across
> row versions.
>
> https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS
>
> Would reintroducing such a feature address some of Uber's concerns
> about multiple indexes? It could, and would do so without the implicit
> requirement of a foreign key; but it would also require a fast OID to
> CTID mapping.
>
> On Tue, 26 Jul 2016 at 10:40 Guyren Howe <guyren@gmail.com
> <mailto: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
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
Would it be best to increase OIDs to 64 bits?

Possibly a choice of 32/64 to be decided when the DB is created???


Cheers,
Gavin

On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> 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 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.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As someone who has gotten more than one bug fix from pgsql in less
than 48 hours, I feel sorry for anyone who finds a bug in a MySQL
version they are running in production.

On 27.07.2016 19:51, 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.
>
>
Sorry, what? You can rollback to previous version of software what you had.

https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

> Sorry, what? You can rollback to previous version of software what you had.
>
> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007

Not so fast. This requires the db to be set in compatibility mode of
the lower version. Once that is removed, no downgrade is possible.

Also starting from Oracle 12 this downgrade option has been removed.
That is, you can't upgrade in-place from Or 11 to 12 and then
downgrade back. The only supported way is the restore from an old
backup (which is true  for all products). I don't work in Oracle, but
got this clarified by someone who is an Oracle dba.

On 28.07.2016 17:50, Rakesh Kumar wrote:
> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>> Sorry, what? You can rollback to previous version of software what you had.
>>
>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007
> Not so fast. This requires the db to be set in compatibility mode of
> the lower version. Once that is removed, no downgrade is possible.
>
> Also starting from Oracle 12 this downgrade option has been removed.
> That is, you can't upgrade in-place from Or 11 to 12 and then
> downgrade back. The only supported way is the restore from an old
> backup (which is true  for all products). I don't work in Oracle, but
> got this clarified by someone who is an Oracle dba.
>
>

Not true -

https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007

About compatible params you are right but downgrade is possible from 12c.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
Sent: Thursday, July 28, 2016 10:59 AM
To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 17:50, Rakesh Kumar wrote:
> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>> Sorry, what? You can rollback to previous version of software what you had.
>>
>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#
>> UPGRD007
> Not so fast. This requires the db to be set in compatibility mode of 
> the lower version. Once that is removed, no downgrade is possible.
>
> Also starting from Oracle 12 this downgrade option has been removed.
> That is, you can't upgrade in-place from Or 11 to 12 and then 
> downgrade back. The only supported way is the restore from an old 
> backup (which is true  for all products). I don't work in Oracle, but 
> got this clarified by someone who is an Oracle dba.
>
>

Not true -

https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007

About compatible params you are right but downgrade is possible from 12c.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

_____________________________________________________________________________

Alex, documentation you point to just proves what Rakesh said:

"For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 

So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 

Regards,
Igor

On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then
>> downgrade back. The only supported way is the restore from an old
>> backup (which is true  for all products). I don't work in Oracle, but
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
>
> _____________________________________________________________________________
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4." 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures. 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section
"How the COMPATIBLE Initialization Parameter Operates in Oracle Database"
-
If you run an Oracle Database 12c database with the COMPATIBLE
initialization parameter set to 11.0.0, then it generates database
structures on disk that are compatible with Oracle Database 11g.
Therefore, the COMPATIBLE initialization parameter enables or disables
the use of features. If you try to use any new features that make the
database incompatible with the COMPATIBLE initialization parameter, then
an error occurs. However, any new features that do not make incompatible
changes on disk are enabled.

So if feature do not change disk in incompatible way this features is
enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain
compatibility. It enables you to take advantage of the maintenance
improvements of a new release immediately in your production systems
without testing the new functionality in your environment. Some features
of the release may be restricted."

So this parameter is made with rollback in mind and you have some new
feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

-----Original Message-----
From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru] 
Sent: Thursday, July 28, 2016 11:26 AM
To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org 
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>> #
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of 
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then 
>> downgrade back. The only supported way is the restore from an old 
>> backup (which is true  for all products). I don't work in Oracle, but 
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres 
> Company
>
> ______________________________________________________________________
> _______
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database"
 
-
If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
 
Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled.
 

So if feature do not change disk in incompatible way this features is enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted."
 

So this parameter is made with rollback in mind and you have some new feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

___________________________________________________________________________________________________________________

^^^
Which means that you can make use of some new feature, but definitely not all.
That makes "downgrade" feature very, very limited, if useful at all.

Regards,
Igor

-----Original Message-----
From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru] 
Sent: Thursday, July 28, 2016 11:26 AM
To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
Cc: PostgreSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL


On 28.07.2016 18:09, Igor Neyman wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org 
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
> Sent: Thursday, July 28, 2016 10:59 AM
> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 17:50, Rakesh Kumar wrote:
>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>
>>> Sorry, what? You can rollback to previous version of software what you had.
>>>
>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>> #
>>> UPGRD007
>> Not so fast. This requires the db to be set in compatibility mode of 
>> the lower version. Once that is removed, no downgrade is possible.
>>
>> Also starting from Oracle 12 this downgrade option has been removed.
>> That is, you can't upgrade in-place from Or 11 to 12 and then 
>> downgrade back. The only supported way is the restore from an old 
>> backup (which is true  for all products). I don't work in Oracle, but 
>> got this clarified by someone who is an Oracle dba.
>>
>>
> Not true -
>
> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>
> About compatible params you are right but downgrade is possible from 12c.
>
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com The Russian Postgres 
> Company
>
> ______________________________________________________________________
> _______
>
> Alex, documentation you point to just proves what Rakesh said:
>
> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last upgraded.
Forexample, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4."
 
>
> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures.
 
>
> Regards,
> Igor
Not true again.

 From Oracle docs
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database"
 
-
If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
 
Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled.
 

So if feature do not change disk in incompatible way this features is enable.

Also from this
https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
:

"This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted."
 

So this parameter is made with rollback in mind and you have some new feature from newer software release.

Alex Ignatov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

____________________________________________________________________________________________________________________

B.t.w., In Postgres release which doesn't change "database structures"  is called "minor" (just replacing binaries),
andallows downgrade.
 

"Compactable" or not,  in "Oracle world" no DBA in their right mind will attempt major upgrade without having complete
(andtested) backup that could be used in case of upgrade going wrong.
 

Regards,
Igor

On 28.07.2016 18:41, Igor Neyman wrote:
> -----Original Message-----
> From: Alex Ignatov [mailto:a.ignatov@postgrespro.ru]
> Sent: Thursday, July 28, 2016 11:26 AM
> To: Igor Neyman <ineyman@perceptron.com>; Rakesh Kumar <rakeshkumar464a3@gmail.com>
> Cc: PostgreSQL General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>
>
> On 28.07.2016 18:09, Igor Neyman wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Ignatov
>> Sent: Thursday, July 28, 2016 10:59 AM
>> To: Rakesh Kumar <rakeshkumar464a3@gmail.com>
>> Cc: PostgreSQL General <pgsql-general@postgresql.org>
>> Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL
>>
>>
>> On 28.07.2016 17:50, Rakesh Kumar wrote:
>>> On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>>
>>>> Sorry, what? You can rollback to previous version of software what you had.
>>>>
>>>> https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm
>>>> #
>>>> UPGRD007
>>> Not so fast. This requires the db to be set in compatibility mode of
>>> the lower version. Once that is removed, no downgrade is possible.
>>>
>>> Also starting from Oracle 12 this downgrade option has been removed.
>>> That is, you can't upgrade in-place from Or 11 to 12 and then
>>> downgrade back. The only supported way is the restore from an old
>>> backup (which is true  for all products). I don't work in Oracle, but
>>> got this clarified by someone who is an Oracle dba.
>>>
>>>
>> Not true -
>>
>> https://docs.oracle.com/database/121/UPGRD/downgrade.htm#UPGRD007
>>
>> About compatible params you are right but downgrade is possible from 12c.
>>
>> Alex Ignatov
>> Postgres Professional: http://www.postgrespro.com The Russian Postgres
>> Company
>>
>> ______________________________________________________________________
>> _______
>>
>> Alex, documentation you point to just proves what Rakesh said:
>>
>> "For supported releases of Oracle Database, you can downgrade a database to the release from which you last
upgraded.For example, if you recently upgraded from release 11.2.0.4 to Oracle Database 12c, and you did not change the
compatibleinitialization parameter to 12.1 or higher, then you can downgrade to release 11.2.0.4." 
>>
>> So, you can downgrade only if you didn't "activate" newer feature (didn't compatibility to higher version).
>> But then, what's the point in upgrading (I know, there are some like bug fixes, etc...), if you aren't going to use
newerfeatures. 
>>
>> Regards,
>> Igor
> Not true again.
>
>   From Oracle docs
> https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD001 Section "How the COMPATIBLE Initialization Parameter
Operatesin Oracle Database" 
> -
> If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.0.0, then it
generatesdatabase structures on disk that are compatible with Oracle Database 11g. 
> Therefore, the COMPATIBLE initialization parameter enables or disables the use of features. If you try to use any new
featuresthat make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs.
However,any new features that do not make incompatible changes on disk are enabled. 
>
> So if feature do not change disk in incompatible way this features is enable.
>
> Also from this
> https://docs.oracle.com/database/121/REFRN/GUID-6C57EE11-BD06-4BB8-A0F7-D6CDDD086FA9.htm#REFRN10019
> :
>
> "This parameter specifies the release with which Oracle must maintain compatibility. It enables you to take advantage
ofthe maintenance improvements of a new release immediately in your production systems without testing the new
functionalityin your environment. Some features of the release may be restricted." 
>
> So this parameter is made with rollback in mind and you have some new feature from newer software release.
>
> Alex Ignatov
> Postgres Professional:http://www.postgrespro.com
> The Russian Postgres Company
>
> ____________________________________________________________________________________________________________________
>
> B.t.w., In Postgres release which doesn't change "database structures"  is called "minor" (just replacing binaries),
andallows downgrade. 
>
> "Compactable" or not,  in "Oracle world" no DBA in their right mind will attempt major upgrade without having
complete(and tested) backup that could be used in case of upgrade going wrong. 
>
> Regards,
> Igor
>
  Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
right to do major upgrade without complete and tested backup?
Ok,  I understand you. In Postgres world there always sky is blue and
sun is shining.


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
>  Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their right
> to do major upgrade without complete and tested backup?
> Ok,  I understand you. In Postgres world there always sky is blue and sun is
> shining.

Of course we have backups. But we also have slony. So we CAN go back
and forth between latest and previous without a restore.

On 28.07.2016 19:43, Scott Marlowe wrote:
> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their right
>> to do major upgrade without complete and tested backup?
>> Ok,  I understand you. In Postgres world there always sky is blue and sun is
>> shining.
> Of course we have backups. But we also have slony. So we CAN go back
> and forth between latest and previous without a restore.
>
>

And? Oracle and MySql doesnt have it but can downgrade right out the
box. Quick and easy.

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

> And? Oracle and MySql doesnt have it but can downgrade right out the box.
> Quick and easy.

No it is not for mysql.

http://dev.mysql.com/doc/refman/5.7/en/downgrading.html

"In-place Downgrade: Involves shutting down the new MySQL version,
replacing the new MySQL binaries or packages with the old ones, and
restarting the old MySQL version on the existing data directory.
In-place downgrades are supported for downgrades between GA versions
within the same release series. For example, in-place downgrades are
supported for downgrades from 5.7.10 to 5.7.9.
The above is easy and same as PG if you are going from 9.5.2 to 9.5.1.
"
Now let us take about major downgrade.

Downgrading one release level is supported using the logical downgrade
method. For example, downgrading from 5.7 to 5.6 is supported. Logical
Downgrade: Involves using mysqldump to dump all tables from the new
MySQL version, and then loading the dump file into the old MySQL
version. Logical downgrades are supported for downgrades between GA
versions within the same release series and for downgrades between
release levels. For example, logical downgrades are supported for
downgrades from 5.7.10 to 5.7.9 and for downgrades from 5.7 to 5.6.

Only a fool will consider this as simple. And BTW all products support
methods similar to mysqldump. What we are looking at, is an in-place
downgrade after a major version upgrade and that looks to be almost
non-existent.

On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>
> On 28.07.2016 19:43, Scott Marlowe wrote:
>>
>> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov <a.ignatov@postgrespro.ru>
>> wrote:
>>>
>>>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
>>> right
>>> to do major upgrade without complete and tested backup?
>>> Ok,  I understand you. In Postgres world there always sky is blue and sun
>>> is
>>> shining.
>>
>> Of course we have backups. But we also have slony. So we CAN go back
>> and forth between latest and previous without a restore.
>>
>>
>
> And? Oracle and MySql doesnt have it but can downgrade right out the box.
> Quick and easy.

So you can swap between oracle 11 and 12 back and forth in a live
environment with no downtime? Please pull the other leg.

On 07/28/2016 10:43 AM, Scott Marlowe wrote:
> On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:

>> And? Oracle and MySql doesnt have it but can downgrade right out the box.
>> Quick and easy.
>
> So you can swap between oracle 11 and 12 back and forth in a live
> environment with no downtime? Please pull the other leg.

This conversation seems to be degrading, can we keep it productive please?

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.

On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> 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 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.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

On Thu, 28 Jul 2016 19:32:39 +0300
Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
> On 28.07.2016 18:41, Igor Neyman wrote:
> > "Compactable" or not,  in "Oracle world" no DBA in their right mind
> > will attempt major upgrade without having complete (and tested)
> > backup that could be used in case of upgrade going wrong.
>
>   Oh, so in contrast to "Oracle world"  "Postgres world" DBA in their
> right to do major upgrade without complete and tested backup?
> Ok,  I understand you. In Postgres world there always sky is blue and
> sun is shining.

Yikes!  Where did you read that?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
> > On 7/27/2016 9:39 PM, Jeff Janes wrote:
> >>
> >> That depends on how how many objects there are consuming that 1 TB.
> >> With millions of small objects, you will have problems.  Not as many
> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
> >> the number of objects.  If you only have thousands of objects, then as
> >> far as I know -k works like a charm.
> >
> >
> > millions of tables?
>
> Well, it was a problem at much smaller values, until we fixed many of
> them.  But the perversity is, if you are stuck on a version before the
> fixes, the problems prevent you from getting to a version on which it
> is not a problem any more.

Uh, that is only true if the slowness was in _dumping_ many objects.
Most of the fixes have been for _restoring_ many objects, and that is
done in the new cluster, so they should be OK.

--
  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 +

On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:

> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:

> > On 7/27/2016 9:39 PM, Jeff Janes wrote:

> >>
> >> That depends on how how many objects there are consuming that 1 TB.
> >> With millions of small objects, you will have problems.  Not as many
> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
> >> the number of objects.  If you only have thousands of objects, then as
> >> far as I know -k works like a charm.

> >
> >
> > millions of tables?

> 
> Well, it was a problem at much smaller values, until we fixed many of
> them.  But the perversity is, if you are stuck on a version before the
> fixes, the problems prevent you from getting to a version on which it
> is not a problem any more.

Uh, that is only true if the slowness was in _dumping_ many objects. 
Most of the fixes have been for _restoring_ many objects, and that is
done in the new cluster, so they should be OK.

I thought we were talking about pg_upgrade in -k link mode?    or does that rely on a dump/restore --schema-only operation to create the metadata?

-- 
john r pierce, recycling bits in santa cruz

On Thu, Jul 28, 2016 at 03:26:17PM -0700, John R Pierce wrote:
>     Uh, that is only true if the slowness was in _dumping_ many objects.
>     Most of the fixes have been for _restoring_ many objects, and that is
>     done in the new cluster, so they should be OK.
>
>
> I thought we were talking about pg_upgrade in -k link mode?    or does that
> rely on a dump/restore --schema-only operation to create the metadata?

Yes, it does, with our without -k --- -k only controls file link vs file
copy.

--
  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 +

On 07/28/2016 03:16 PM, Bruce Momjian wrote:
> On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
>> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
>>> On 7/27/2016 9:39 PM, Jeff Janes wrote:
>>>>
>>>> That depends on how how many objects there are consuming that 1 TB.
>>>> With millions of small objects, you will have problems.  Not as many
>>>> in 9.5 as there were in 9.1, but still it does not scale linearly in
>>>> the number of objects.  If you only have thousands of objects, then as
>>>> far as I know -k works like a charm.
>>>
>>>
>>> millions of tables?
>>
>> Well, it was a problem at much smaller values, until we fixed many of
>> them.  But the perversity is, if you are stuck on a version before the
>> fixes, the problems prevent you from getting to a version on which it
>> is not a problem any more.
>
> Uh, that is only true if the slowness was in _dumping_ many objects.
> Most of the fixes have been for _restoring_ many objects, and that is
> done in the new cluster, so they should be OK.


Not really true. I ran into two separate cases where on older (pre 9.3 I
believe) Postgres if you had hundreds of thousands of tables (in the
case I remember well, it was about 500k tables) the schema dump from the
old cluster basically never finished (ok, was killed after about a
week). I had to find the patch that fixed a good bit of the slowness and
backport it to the older version so we could successfully run pg_upgrade
(in something like 14 hours instead of 7+ days).

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On 07/28/2016 04:58 PM, Joe Conway wrote:
> On 07/28/2016 03:16 PM, Bruce Momjian wrote:
>
> Not really true. I ran into two separate cases where on older (pre 9.3 I
> believe) Postgres if you had hundreds of thousands of tables (in the
> case I remember well, it was about 500k tables) the schema dump from the
> old cluster basically never finished (ok, was killed after about a
> week). I had to find the patch that fixed a good bit of the slowness and
> backport it to the older version so we could successfully run pg_upgrade
> (in something like 14 hours instead of 7+ days).

Correct, I don't know if it is still true but definitely pre 9.3, if you
had lots and lots of tables, you were looking at very long times to
actually start a dump. The thing is, although 500k tables is very rare,
10k tables isn't nearly as rare. That would still take entirely too long.

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.

Em 28/07/2016 13:07, Chris Travers escreveu:

On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> 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 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.

Best / worst MySQL bug was one introduced and fixed twice. Someone put
in a short cut that sped up order by by quite a bit. It also meant
that order by desc would actually get order by asc output. It was
inserted into the code due to poor oversite / code review practices,
then fixed about 9 months later, then introduced again, and again,
took about a year to fix.

The fact that it was introduced into a General Release mid stream with
no testing or real reviews speaks volumes about MySQL and its
developers. The fact that it took months to years to fix each time
does as well.

As for MySQL issues, personally I love the fact that a single query inserting a bunch of rows can sometimes deadlock against itself.  And I love the fact that this is obliquely documented as expected behavior.  May I mention I am *really glad* PostgreSQL doesn't go the whole multi-threaded backend route and that this is exhibit A as to why (I am sure it is a thread race issue between index and table updates)?  

Sorry, I think this is a biased vision. Multi-threading will show as much problems as multi-process - both has to have simultaneous access (or, at least, right semaphor implementation to serialize writes and syncronize reads).
The fact is **on this point at least** is that Postgres is correctly implemented, and MySQL is faulty.
I've faced the "lost FK integrity hell" (caused by the problem above) with MySQL long before decided to migrate all systems to PostgreSQL.
My personal experience is that MySQL is excellent for data that is not sensitive (web site, e-mail settings, etc). Everything else goes to PostgreSQL (or Oracle, or MS SQL Server, or Sybase, or DB2 - in *my* order of preference).

Regards,

Edson Richter

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On Wed, Jul 27, 2016 at 01:02:40PM -0400, Bruce Momjian wrote:
> Watching the video was helpful:
>
>     https://vimeo.com/145842299
>
> You can see the failover happened because of various user errors.  That
> doesn't excuse our bug, but I am not sure exactly how much they
> understood of Postgres behavior.  His talk near the end about the
> replication infrastucture being exposed to them was also interesting.

Here is a more balanced blog post that corrects some missing
information, e.g. HOT updates, hot_standby_feedback:

    http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases

--
  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 +

Condor <condor@stz-bg.com> writes:

> 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.

Yeah, very hard indeed when done by unskilled DBAs :-)

I've done several ~7TB pg_upgrades and with the hard link option and a
framework that parallelizes the post-analyzer phase...

...45 minutes till completion.


>
> Regards,
> Hristo S.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

On 26/07/16 19:39, 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?
>
1. Open the page

2. Press Cmd-F on Mac, Ctrl-F on Linux/Windows. (Find on page)

3. Type "transaction" in the search field, without the quotes

4. Scroll through the article and look for the highlights


There are 17 highlights. I count 2 in the MySQL part. Chances are good
that the rest 15 are in the PostgreSQL part.

It tells a lot.

When I am told that MySQL supports transactions I face the fact that the
word "transaction" must have at least two meanings and no matter what I
do I know only one of them.

Every time I had to work with MySQL I felt overwhelming sloppiness. I
can’t (or don’t want to) deal with accepting 0 as NULL but only once,
auto typecasting pianos to cats (take it as a metaphor), committing a
"transaction" (in MySQL terms), without sending commit, on client
disconnect. (Older version).

One can say it can’t be that bad as Facebook and now Uber are using it.
The same logic tells that junk food is the best for humans.

In the last few years I tried out more or less every hyped schemaless
databases. Not for their main feature as my data like like the rest of
the data in the Universe can be put in a schema. I did it because faced
some of the issues mentioned in the article and other issues that aren’t
mentioned, even on smaller scale, that’s why. (Smaller scale means
smaller company, less resources, less people. At the end it hurts the
same way.)

I still don’t see how I could live without transactions, and not only
because a simple and intentional rollback saved me from much coding and
complexity with one the most important features of my application. But
having a single update statement modified about the 70% of the records
before Cassandra crashed is not for me. It tried to repair about 1000
records, using 3 nodes, for 1 or 2 hours before I deleted the test cluster.

Maybe I did it wrong. Or probably. It can’t be that bad. People at Uber
probably know more about the internals than I ever will. I also know
that a few big companies had about 1 day long downtimes thanx to MongoDB
and CouchDB.

Since I know that people who are way more professional than me decide to
use a database engine in production that doesn’t tell you whether it
could store your data or not, I don’t care who is doing what and what is
on his business card.


And yes, I hate upgrading PostgreSQL especially on FreeBSD where
pg_upgrade isn’t really an option.

Sometimes the answer is manual or full vacuum, no matter what the manual
says. (And yes, the downtime does hurt.)


On the other hand, if there was a stable and officially integrated
logical replication that supports multi-master setups, many of the
issues would just be gone. Upgrades wouldn’t be painful anymore,
timeline and pg_rewind bugs wouldn’t matter, and the DBA could remove
the bloat form the masters one by one by doing that thing in the night
when no one sees it. (Until the full-vacuum-police would find him and come.)

M.

On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
> And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade
> isn’t really an option.

Is that because it is hard to install the old and new clusters on the
same server on FreeBSD?

--
  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 +

On 2016-07-29 12:59, Bruce Momjian wrote:
> On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
>> And yes, I hate upgrading PostgreSQL especially on FreeBSD where
>> pg_upgrade
>> isn’t really an option.
>
> Is that because it is hard to install the old and new clusters on the
> same server on FreeBSD?
>
The current FreeBSD Ports collection ports only allow ONE version to be
installed at a time.


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

Bruce Momjian <bruce@momjian.us> writes:

> 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.

Hmmm, is it at least theoretically possible that if a newly upgraded
system were run for an interval where *no* incompatible changes to DDL
etc had been done...

...that a downgrade could be performed?

Er, using a not yet invented pg_downgrade:-)

I reason that the same kind of voodoo that lets us do those very quick
hard linked upgrades could be used to revert as well without data loss.

Such a feature would be part of whatever newer version that the upgrade
was done to in the first place.

That is, since higher version knew enough about lower version to
rejigger everything...  just maybe it could do the reverse.

Had the new version been run for very long with substantial data
changes, then a post-analyze on the downgraded system might be necessary
as well but possibly even this could be omitted in some cases.

Totally nuts?  Yes, perhaps :-)

FWIW

>   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 +

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Jerry,

* Jerry Sievers (gsievers19@comcast.net) wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
>
> Hmmm, is it at least theoretically possible that if a newly upgraded
> system were run for an interval where *no* incompatible changes to DDL
> etc had been done...
>
> ...that a downgrade could be performed?
>
> Er, using a not yet invented pg_downgrade:-)

The short answer is 'no'.  Consider a case like the GIN page changes- as
soon as you execute DML on a column that has a GIN index on it, we're
going to rewrite that page using a newer version of the page format and
an older version of PG isn't going to understand it.

Those kind of on-disk changes are, I suspect, why you have to set the
"compatibility" option in the big $O product to be able to later do a
downgrade.

> That is, since higher version knew enough about lower version to
> rejigger everything...  just maybe it could do the reverse.

That might work if you opened the database in read-only mode, but not
once you start making changes.

Thanks!

Stephen

On Fri, 29 Jul 2016 13:06:04 -0500
Larry Rosenman <ler@lerctr.org> wrote:
> > Is that because it is hard to install the old and new clusters on
> > the same server on FreeBSD?
> >
> The current FreeBSD Ports collection ports only allow ONE version to
> be installed at a time.

As does NetBSD.  The problem is that unlike Python (which BSD allows
multiple versions) there is only one executable to deal with.  It's not
an insurmountable problem but it could get messy.

The answer is either chroot or mount and run pg_upgrade on another
server. If you can afford the downtime you can also delete PG, install
the new version and run pg_upgrade without modifying the existing DB.
If it succeeds then replace the directories and restart the new
version.  If it fails then uninstall PG, reinstall the older version
and restart. Lather, rinse, repeat until it upgrades cleanly.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

On Fri, Jul 29, 2016 at 03:03:46PM -0400, D'Arcy J.M. Cain wrote:
> As does NetBSD.  The problem is that unlike Python (which BSD allows
> multiple versions) there is only one executable to deal with.  It's not
> an insurmountable problem but it could get messy.
>
> The answer is either chroot or mount and run pg_upgrade on another
> server. If you can afford the downtime you can also delete PG, install
> the new version and run pg_upgrade without modifying the existing DB.
> If it succeeds then replace the directories and restart the new
> version.  If it fails then uninstall PG, reinstall the older version
> and restart. Lather, rinse, repeat until it upgrades cleanly.

pg_upgrade needs to run the old and new server binaries as part of its
operation, so that would not work.

--
  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 +

On Fri, Jul 29, 2016 at 02:50:32PM -0400, Stephen Frost wrote:
> > Er, using a not yet invented pg_downgrade:-)
>
> The short answer is 'no'.  Consider a case like the GIN page changes- as
> soon as you execute DML on a column that has a GIN index on it, we're
> going to rewrite that page using a newer version of the page format and
> an older version of PG isn't going to understand it.
>
> Those kind of on-disk changes are, I suspect, why you have to set the
> "compatibility" option in the big $O product to be able to later do a
> downgrade.

Yes, you would need a mode that prevented new-format writes on the new
server.

--
  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 +

On Fri, 29 Jul 2016 15:07:53 -0400
Bruce Momjian <bruce@momjian.us> wrote:
> > The answer is either chroot or mount and run pg_upgrade on another
> > server. If you can afford the downtime you can also delete PG,
> > install the new version and run pg_upgrade without modifying the
> > existing DB. If it succeeds then replace the directories and
> > restart the new version.  If it fails then uninstall PG, reinstall
> > the older version and restart. Lather, rinse, repeat until it
> > upgrades cleanly.
>
> pg_upgrade needs to run the old and new server binaries as part of its
> operation, so that would not work.

My mistake.  I must have used the chroot idea last time I did an
upgrade.

I might take a look at the NetBSD package (I'm a developer) to see how
hard it would be to allow multiple versions.  We do keep all the lib
stuff in a separate directory so that part would be relatively simple.
We just need to find all the binaries and make the names versioned and
add a symlink to the user selected primary version to the bare version
of the binary name.  Example:
 - psql.8.3
 - psql.9.1
 - psql.9.3
 - psql ==> psql.9.3

Other than linking to the correct library can you think of any other
issues with this?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

On 2016-07-29 15:04, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:07:53 -0400
> Bruce Momjian <bruce@momjian.us> wrote:
>> > The answer is either chroot or mount and run pg_upgrade on another
>> > server. If you can afford the downtime you can also delete PG,
>> > install the new version and run pg_upgrade without modifying the
>> > existing DB. If it succeeds then replace the directories and
>> > restart the new version.  If it fails then uninstall PG, reinstall
>> > the older version and restart. Lather, rinse, repeat until it
>> > upgrades cleanly.
>>
>> pg_upgrade needs to run the old and new server binaries as part of its
>> operation, so that would not work.
>
> My mistake.  I must have used the chroot idea last time I did an
> upgrade.
>
> I might take a look at the NetBSD package (I'm a developer) to see how
> hard it would be to allow multiple versions.  We do keep all the lib
> stuff in a separate directory so that part would be relatively simple.
> We just need to find all the binaries and make the names versioned and
> add a symlink to the user selected primary version to the bare version
> of the binary name.  Example:
>  - psql.8.3
>  - psql.9.1
>  - psql.9.3
>  - psql ==> psql.9.3
>
> Other than linking to the correct library can you think of any other
> issues with this?

Data Directory naming, as well as keeping the init-scripts straight.

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

On 2016-07-29 15:06, Larry Rosenman wrote:
> On 2016-07-29 15:04, D'Arcy J.M. Cain wrote:
>> On Fri, 29 Jul 2016 15:07:53 -0400
>> Bruce Momjian <bruce@momjian.us> wrote:
>>> > The answer is either chroot or mount and run pg_upgrade on another
>>> > server. If you can afford the downtime you can also delete PG,
>>> > install the new version and run pg_upgrade without modifying the
>>> > existing DB. If it succeeds then replace the directories and
>>> > restart the new version.  If it fails then uninstall PG, reinstall
>>> > the older version and restart. Lather, rinse, repeat until it
>>> > upgrades cleanly.
>>>
>>> pg_upgrade needs to run the old and new server binaries as part of
>>> its
>>> operation, so that would not work.
>>
>> My mistake.  I must have used the chroot idea last time I did an
>> upgrade.
>>
>> I might take a look at the NetBSD package (I'm a developer) to see how
>> hard it would be to allow multiple versions.  We do keep all the lib
>> stuff in a separate directory so that part would be relatively simple.
>> We just need to find all the binaries and make the names versioned and
>> add a symlink to the user selected primary version to the bare version
>> of the binary name.  Example:
>>  - psql.8.3
>>  - psql.9.1
>>  - psql.9.3
>>  - psql ==> psql.9.3
>>
>> Other than linking to the correct library can you think of any other
>> issues with this?
>
> Data Directory naming, as well as keeping the init-scripts straight.
>
And who gets 5432, and Unix socket naming, it starts to get messy.....
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
> >>I might take a look at the NetBSD package (I'm a developer) to see how
> >>hard it would be to allow multiple versions.  We do keep all the lib
> >>stuff in a separate directory so that part would be relatively simple.
> >>We just need to find all the binaries and make the names versioned and
> >>add a symlink to the user selected primary version to the bare version
> >>of the binary name.  Example:
> >> - psql.8.3
> >> - psql.9.1
> >> - psql.9.3
> >> - psql ==> psql.9.3
> >>
> >>Other than linking to the correct library can you think of any other
> >>issues with this?
> >
> >Data Directory naming, as well as keeping the init-scripts straight.
> >
> And who gets 5432, and Unix socket naming, it starts to get messy.....

Well, pg_upgrade uses a socket created in the current run directory, so
that should be fine.

--
  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 +

On 2016-07-29 15:14, Bruce Momjian wrote:
> On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
>> >>I might take a look at the NetBSD package (I'm a developer) to see how
>> >>hard it would be to allow multiple versions.  We do keep all the lib
>> >>stuff in a separate directory so that part would be relatively simple.
>> >>We just need to find all the binaries and make the names versioned and
>> >>add a symlink to the user selected primary version to the bare version
>> >>of the binary name.  Example:
>> >> - psql.8.3
>> >> - psql.9.1
>> >> - psql.9.3
>> >> - psql ==> psql.9.3
>> >>
>> >>Other than linking to the correct library can you think of any other
>> >>issues with this?
>> >
>> >Data Directory naming, as well as keeping the init-scripts straight.
>> >
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> Well, pg_upgrade uses a socket created in the current run directory, so
> that should be fine.
if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
co-existence on an ongoing basis
is quite another.....
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

On Fri, 29 Jul 2016 15:09:59 -0500
Larry Rosenman <ler@lerctr.org> wrote:
> >> version to the bare version of the binary name.  Example:
> >>  - psql.8.3
> >>  - psql.9.1
> >>  - psql.9.3
> >>  - psql ==> psql.9.3
> >>
> >> Other than linking to the correct library can you think of any
> >> other issues with this?
> >
> > Data Directory naming, as well as keeping the init-scripts straight.
> >
> And who gets 5432, and Unix socket naming, it starts to get messy.....

I assume that anyone running multiple versions knows how to specify all
that stuff when initializing and starting the different servers.

From the comments I saw it looked to me like other systems (Linux?) had
already solved this problem.  Was I wrong?

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 788 2246     (DoD#0082)    (eNTP)   |  what's for dinner.
IM: darcy@Vex.Net, VoIP: sip:darcy@druid.net

On 29-07-2016 20:33, Jerry Sievers wrote:
> Condor <condor@stz-bg.com> writes:
>
>> 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.
>
> Yeah, very hard indeed when done by unskilled DBAs :-)
>
> I've done several ~7TB pg_upgrades and with the hard link option and a
> framework that parallelizes the post-analyzer phase...
>
> ...45 minutes till completion.
>
>
>>
>> Regards,
>> Hristo S.

GL to you

On 07/29/2016 01:17 PM, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:09:59 -0500
> Larry Rosenman <ler@lerctr.org> wrote:
>>>> version to the bare version of the binary name.  Example:
>>>>  - psql.8.3
>>>>  - psql.9.1
>>>>  - psql.9.3
>>>>  - psql ==> psql.9.3
>>>>
>>>> Other than linking to the correct library can you think of any
>>>> other issues with this?
>>>
>>> Data Directory naming, as well as keeping the init-scripts straight.
>>>
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> I assume that anyone running multiple versions knows how to specify all
> that stuff when initializing and starting the different servers.
>
> From the comments I saw it looked to me like other systems (Linux?) had
> already solved this problem.  Was I wrong?
>
No:

https://wiki.debian.org/PostgreSql

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2016-07-29 15:17, D'Arcy J.M. Cain wrote:
> On Fri, 29 Jul 2016 15:09:59 -0500
> Larry Rosenman <ler@lerctr.org> wrote:
>> >> version to the bare version of the binary name.  Example:
>> >>  - psql.8.3
>> >>  - psql.9.1
>> >>  - psql.9.3
>> >>  - psql ==> psql.9.3
>> >>
>> >> Other than linking to the correct library can you think of any
>> >> other issues with this?
>> >
>> > Data Directory naming, as well as keeping the init-scripts straight.
>> >
>> And who gets 5432, and Unix socket naming, it starts to get messy.....
>
> I assume that anyone running multiple versions knows how to specify all
> that stuff when initializing and starting the different servers.
>
> From the comments I saw it looked to me like other systems (Linux?) had
> already solved this problem.  Was I wrong?

They have, we (*BSD) just need to make sure we document it, and we make
damn
sure we don't break existing folks.

I'm willing to help on the FreeBSD side.

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: ler@lerctr.org
US Mail: 17716 Limpia Crk, Round Rock, TX 78664-7281

On Fri, Jul 29, 2016 at 3:18 PM, Condor <condor@stz-bg.com> wrote:
> On 29-07-2016 20:33, Jerry Sievers wrote:

>> I've done several ~7TB pg_upgrades and with the hard link option and a
>> framework that parallelizes the post-analyzer phase...
>>
>> ...45 minutes till completion.

> GL to you

Luck has nothing to do with anything.  You really might want to
post with more details and see whether people can help sort out why
you have seen such slow performance where so many others have not.
I hope it's not just a matter of saying "I have a 1TB database and
upgrade is slow, therefore it is slow because it is 1TB."  That
would be roughly the equivalent of saying "I have a blue car and it
is slow, therefore it is slow because it is blue."  It just might
be the flat tire that actually matters.  If your upgrade is slow
because you have 10 million database objects, that might be a hard
one to overcome, but it might be something with an easy solution in
the pg_upgrade options or server configuration.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jul 29, 2016 at 3:22 PM, Larry Rosenman <ler@lerctr.org> wrote:

> I'm willing to help on the FreeBSD side.

One more tip -- if you are running multiple clusters (same version
or not) on the same machine, it is best to run each cluster under a
separate OS user.  It's not *required*, but it makes a restart
after a crash less problematic and it is generally better from a
security standpoint, so you might want to look for a way to allow
it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Larry Rosenman (ler@lerctr.org) wrote:
> On 2016-07-29 15:14, Bruce Momjian wrote:
> >On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
> >>>Data Directory naming, as well as keeping the init-scripts straight.
> >>>
> >>And who gets 5432, and Unix socket naming, it starts to get messy.....
> >
> >Well, pg_upgrade uses a socket created in the current run directory, so
> >that should be fine.
> if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
> co-existence on an ongoing basis
> is quite another.....

It's not an insurmountable problem, though it's a bit painful.  Still,
both the Debian-based and RedHat-based distributions demonstrate how it
can be done.

Thanks!

Stephen

On Sat, Jul 30, 2016 at 8:51 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Larry Rosenman (ler@lerctr.org) wrote:
>> On 2016-07-29 15:14, Bruce Momjian wrote:
>> >On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote:
>> >>>Data Directory naming, as well as keeping the init-scripts straight.
>> >>>
>> >>And who gets 5432, and Unix socket naming, it starts to get messy.....
>> >
>> >Well, pg_upgrade uses a socket created in the current run directory, so
>> >that should be fine.
>> if we're talking JUST for pg_upgrade, that's one thing.  Peaceful
>> co-existence on an ongoing basis
>> is quite another.....
>
> It's not an insurmountable problem, though it's a bit painful.  Still,
> both the Debian-based and RedHat-based distributions demonstrate how it
> can be done.

As a user of FreeBSD (as of recently) and Debian (since forever), I
have often thought that I''d really like to see a FreeBSD port of the
excellent postgresql-common package[1], and then
postgresql[major][minor] ports that can be installed in parallel.  The
binaries would have be installed under somewhere like
/usr/local/libexec/postgresql/[major].[minor], the default pg_data
location would have to be somewhere like like
/usr/local/pg_data/[major].[minor]/[cluster-name], the config files
somewhere like /usr/local/etc/postgresql/[major].[minor]/[cluster-name]/postgresql.conf
(or they could stay inside the pg_data dirs, as long as they can be
found by the postgresql-common cluster management scripts), and the
postgresql-common port would provide wrapper commands like
pg_createcluster, pg_lsclusters, ... and that psql wrapper that takes
an extra optional switch like --cluster 9.5/main.  The client library
on the other hand would not be versioned in that way: there would be
just the latest major version's libpq5[2], and that is what other
things like py-psycopg2 etc would depend on (instead of depending on a
specific client major version like postgresql93-client).

[1] https://packages.debian.org/jessie/postgresql-common
[2] https://packages.debian.org/jessie/libpq5

--
Thomas Munro
http://www.enterprisedb.com

On Thu, Jul 28, 2016 at 3:16 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote:
>> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pierce@hogranch.com> wrote:
>> > On 7/27/2016 9:39 PM, Jeff Janes wrote:
>> >>
>> >> That depends on how how many objects there are consuming that 1 TB.
>> >> With millions of small objects, you will have problems.  Not as many
>> >> in 9.5 as there were in 9.1, but still it does not scale linearly in
>> >> the number of objects.  If you only have thousands of objects, then as
>> >> far as I know -k works like a charm.
>> >
>> >
>> > millions of tables?
>>
>> Well, it was a problem at much smaller values, until we fixed many of
>> them.  But the perversity is, if you are stuck on a version before the
>> fixes, the problems prevent you from getting to a version on which it
>> is not a problem any more.
>
> Uh, that is only true if the slowness was in _dumping_ many objects.
> Most of the fixes have been for _restoring_ many objects, and that is
> done in the new cluster, so they should be OK.

There have been improvements on both sides.  For the improvements that
need to exist in the old-server to be effective, we did backpatch the
main one back to 9.1, in the October 2015 releases, specifically to
help people get off the old versions.  So if you are on 9.1 with
tens/hundreds of thousands of objects, you need to do a minor version
upgrade to at least 9.1.19 before doing the major version upgrade.  If
you are on 9.0 or before with so many objects, you don't have a lot of
good options.


Cheers,

Jeff

On Mon, Aug  1, 2016 at 11:54:00AM -0700, Jeff Janes wrote:
> > Uh, that is only true if the slowness was in _dumping_ many objects.
> > Most of the fixes have been for _restoring_ many objects, and that is
> > done in the new cluster, so they should be OK.
>
> There have been improvements on both sides.  For the improvements that
> need to exist in the old-server to be effective, we did backpatch the
> main one back to 9.1, in the October 2015 releases, specifically to
> help people get off the old versions.  So if you are on 9.1 with
> tens/hundreds of thousands of objects, you need to do a minor version
> upgrade to at least 9.1.19 before doing the major version upgrade.  If
> you are on 9.0 or before with so many objects, you don't have a lot of
> good options.

Yeah, that's what I remember.  Thanks for the details.

--
  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 +

> On 27/07/16 18:54, Chris Travers wrote:
>> 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.
>
> You mean something like hot_standby_feedback?
>
> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK

I wonder if their problem could be fixed by using
hot_standby_feedback.  I have encountered similar problem but it seems
hot_standby_feedback was not any help in this case:

https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 5 August 2016 at 09:16, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>> On 27/07/16 18:54, Chris Travers wrote:
>>> 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.
>>
>> You mean something like hot_standby_feedback?
>>
>> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
>
> I wonder if their problem could be fixed by using
> hot_standby_feedback.  I have encountered similar problem but it seems
> hot_standby_feedback was not any help in this case:
>
> https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp

There have been various bugs and enhancements over the years, not all
of which were backpatched.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

>>> https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
>>
>> I wonder if their problem could be fixed by using
>> hot_standby_feedback.  I have encountered similar problem but it seems
>> hot_standby_feedback was not any help in this case:
>>
>> https://www.postgresql.org/message-id/20130829.164457.863984798767991096.t-ishii%40sraoss.co.jp
>
> There have been various bugs and enhancements over the years, not all
> of which were backpatched.

The paticular case still does not work with PostgreSQL 9.5.3.

On primary:
create table t1(i int);
insert into t1 values(1),(2),(3);

On standby:
begin;
test=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
(1 row)

select * from t1;
 i
---
 1
 2
 3
(3 rows)

On primary:
delete from t1;

On standby:
select * from t1;
 i
---
(0 rows)

On primary:
test=# vacuum verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": removed 3 row versions in 1 pages
INFO:  "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "t1": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

After while on standby:
test=# select * from t1;
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 5 August 2016 at 14:06, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> On primary:
> test=# vacuum verbose t1;
> INFO:  vacuuming "public.t1"
> INFO:  "t1": removed 3 row versions in 1 pages
> INFO:  "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> Skipped 0 pages due to buffer pins.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "t1": truncated 1 to 0 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
>
> After while on standby:
> test=# select * from t1;
> FATAL:  terminating connection due to conflict with recovery
> DETAIL:  User was holding a relation lock for too long.
> HINT:  In a moment you should be able to reconnect to the database and repeat your command.
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.

Yes, the VACUUM truncation is still an issue. But statements are
retryable, just like deadlocks.

Unfo the truncation logic always kicks in or small tables of less than
16 blocks. It's more forgiving on bigger tables.

Maybe we could defer the truncation on the standby in some cases.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

> Yes, the VACUUM truncation is still an issue. But statements are
> retryable, just like deadlocks.
>
> Unfo the truncation logic always kicks in or small tables of less than
> 16 blocks. It's more forgiving on bigger tables.

Oh, I didn't know that. Thanks for the info.

> Maybe we could defer the truncation on the standby in some cases.

Do we want to add this to the TODO list?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 29/07/2016 21:06, Larry Rosenman wrote:
> On 2016-07-29 12:59, Bruce Momjian wrote:
>> On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote:
>>> And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade
>>> isn’t really an option.
>>
>> Is that because it is hard to install the old and new clusters on the
>> same server on FreeBSD?
>>
> The current FreeBSD Ports collection ports only allow ONE version to be installed at a time.
>
>

In our installation, pgsql is one of the very few packages that we prefer to deal by hand.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt