Re: Rolling forward to 10.1, new issue...

On Fri, Jan 12, 2018 at 2:27 PM, Karl Denninger <karl(at)denninger(dot)net> wrote:

> On 1/12/2018 12:46, Keith wrote:
>
>
>
> On Thu, Jan 11, 2018 at 1:16 PM, Karl Denninger <karl(at)denninger(dot)net>
> wrote:
>
>> On 1/11/2018 11:48, Tom Lane wrote:
>>
>> Karl Denninger <karl(at)denninger(dot)net> <karl(at)denninger(dot)net> writes:
>>
>> I'm starting to roll forward to 10.1 in a couple of places and have run
>> into a very odd problem -- the "pgcrypto" functions disappeared.
>>
>> Maybe you forgot "CREATE EXTENSION"?
>>
>> regards, tom lane
>>
>> Hmmm.... this was an upgrade from 9.6 and it *was* working there -- it's
>> not a new install. Wouldn't that persist across the upgrade and, when the
>> "gmake install" was done on the contrib module wouldn't it be picked up?
>>
>> I'll check that... it makes sense but I would have expected the
>> roll-forward to work seamlessly (I used pg_upgrade, not a dump/restore.)
>>
>> --
>> Karl Denninger
>> karl(at)denninger(dot)net
>> *The Market Ticker*
>> *[S/MIME encrypted email preferred]*
>>
>
> Nope, it was originally installed into template1 and should have been
> inherited.
>

Creating it in template1 does not automatically inherit it to all other
databases. That only causes it to be installed in databases created after
you installed it to template1.

> Create extension worked but I have no idea why the pg_upgrade didn't carry
> it forward -- I have used pg_upgrade before before (the installation in
> question has been around for a long time) without incident. The only
> "gotcha" I've run into with pg_upgrade before was the moving of tsearch2
> into the base which led me to have a bit of fun with upgrading to 10.x.
>
> I'll see if I can find a way to reproduce this if I can get the time to
> spin up a copy of the 9.6 database (this particular installation is not
> ridiculously large) from backups and run the upgrade again. I popped in
> here because it was completely unexpected, given that the database in
> question had previously been running under v9 and had stored passwords
> encrypted with the functions in the table. As soon as it was upgraded
> verification broke since the extension disappeared.
>
> --
> Karl Denninger
> karl(at)denninger(dot)net
> *The Market Ticker*
> *[S/MIME encrypted email preferred]*
>

Yeah, I think you're going to have to get a repeatable set of steps here.
Could very well be a problem, but without that, it's very hard to figure
out what went wrong here.

Keith