Issue upgrading from V11 to V12 on Debian

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Issue upgrading from V11 to V12 on Debian

stan-9
I am working on upgrading from V11 to V12 on Debian.

My first attempt failed, and I have figured out that this is because I have
added extensions to the V11 DB, at least one of which was not installed
using  the Debian packages.

So, it looks like i need to install these before doing the upgrade, correct?

This brings up a chicken or egg issue however. We are creating the
extensions, like this:

CREATE EXTENSION emailaddr with schema ica

So, to accomplish this, I would need to create the ice schema, and the user
it "belongs" to before I run pg_upgradecluster. This raises 2 questions

1) Is it OK to create these before doing the upgrade?
2) Does this flag that creating these extensions "in" a schema may be a
long term "bad idea"?

Thanks for any advice n this.


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Tom Lane-2
stan <[hidden email]> writes:
> I am working on upgrading from V11 to V12 on Debian.
> My first attempt failed, and I have figured out that this is because I have
> added extensions to the V11 DB, at least one of which was not installed
> using  the Debian packages.

> So, it looks like i need to install these before doing the upgrade, correct?

Yes, but in this context, "install" only means "add the files to the
filesystem".  You don't need to do CREATE EXTENSION in the new
database; pg_upgrade will handle that when it transfers the data.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Paul A Jungwirth
In reply to this post by stan-9
On 12/3/19 7:53 AM, stan wrote:

> I am working on upgrading from V11 to V12 on Debian.
>
> My first attempt failed, and I have figured out that this is because I have
> added extensions to the V11 DB, at least one of which was not installed
> using  the Debian packages.
>
> So, it looks like i need to install these before doing the upgrade, correct?
>
> This brings up a chicken or egg issue however. We are creating the
> extensions, like this:
>
> CREATE EXTENSION emailaddr with schema ica
>
> So, to accomplish this, I would need to create the ice schema, and the user
> it "belongs" to before I run pg_upgradecluster. This raises 2 questions

There are two parts to adding extensions. First you install the *files*
the extension needs. If you build from source this happens when you say
`make install`. Or if you use pgxn, when you say `pgxn install foo`. On
Ubuntu this will add the *.sql and *.control files to
/usr/share/postgresql/12/extension, and the *.so files to
/usr/lib/postgresql/12/lib (so I assume Debian is similar). You can do
that before running pg_upgradecluster.

The second part is saying CREATE EXTENSION, which adds the extension to
the current database. pg_upgradecluster should handle that for you; it
shouldn't require any extra steps.

I hope this helps!

Yours,

--
Paul              ~{:-)
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

stan-9
In reply to this post by Tom Lane-2

On Tue, Dec 03, 2019 at 11:13:55AM -0500, Tom Lane wrote:

> stan <[hidden email]> writes:
> > I am working on upgrading from V11 to V12 on Debian.
> > My first attempt failed, and I have figured out that this is because I have
> > added extensions to the V11 DB, at least one of which was not installed
> > using  the Debian packages.
>
> > So, it looks like i need to install these before doing the upgrade, correct?
>
> Yes, but in this context, "install" only means "add the files to the
> filesystem".  You don't need to do CREATE EXTENSION in the new
> database; pg_upgrade will handle that when it transfers the data.

Sorry, I am back again :-(

So, I have V12 running as the default on the machine I am testing this on
now:

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main    5432 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

BUT, I went to the directory where I have the exentsion's source, did a
make clean ; make ; make install, and the files were still installed in the
V11 tree. How can I instruct the system to put these in the V12 tree?

The  extension in question is pgemailaddr if this matters, BTW.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Paul A Jungwirth
On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default
on the machine I am testing this on

> now:
>
> Ver Cluster Port Status Owner    Data directory              Log file
> 11  main    5433 down   postgres /var/lib/postgresql/11/main
> /var/log/postgresql/postgresql-11-main.log
> 12  main    5432 online postgres /var/lib/postgresql/12/main
> /var/log/postgresql/postgresql-12-main.log
>
> BUT, I went to the directory where I have the exentsion's source, did a
> make clean ; make ; make install, and the files were still installed in the
> V11 tree. How can I instruct the system to put these in the V12 tree?
>
> The  extension in question is pgemailaddr if this matters, BTW.

That extension (and every extension I've seen) uses Postgres's normal
extension-building infrastructure, so it runs pg_config to learn where
to put files. If you run pg_config on your system it will probably
report directories belonging to v11. (That's a little surprising because
on Ubuntu systems I've always had it report the latest version.)

Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to
specific which cluster to use. If you want to add that to your pg_config
you could do it like this: https://stackoverflow.com/a/43403193/122087 
Then just set PGCLUSTER before building. (Make sure you `make clean` first.)

--
Paul              ~{:-)
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

stan-9
On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:

> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> >
> > Ver Cluster Port Status Owner    Data directory              Log file
> > 11  main    5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main    5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> >
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> >
> > The  extension in question is pgemailaddr if this matters, BTW.
>
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
>
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
>

Indeed it does.

I to am surprised. What I find actually surprising is that it is the V12
server that is running.

Here is a bit of history.

I 1st installed V12 quite some time ago on this machine. At that point in
time the V12 client were active, but the V11 server was still active, and
the V12 one was, I believe, not running. So this morning I started trying to
do the upgrade. Using this as an reference:

//gist.gihttpsthub.com/dmitrykustov/27c673ec4f7abd716912e4c830910019

Since I did not know enogh, this failed. So I ran apt-get purge on the V12
server, got the V11 server working, and rebuilt my database, which was an
interesting learning experience, as I thought I had scripts to do this, but
of course the tests I had run on them were against a DB with various things
that somehow I had set up by hand, and not implemented in the scripts.
Learning experience for certain.

So, when I re-installed V12, it started either started up on the default
port, or the upgrade process that failed may have done something. I cannot
remember. So I probably should run the Debian alternative selection tool,
and set V1 as the default, but I really do not understand the consequences
of that vis a vi the upgrade tool.

Should I do this?


--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

stan-9
In reply to this post by Paul A Jungwirth

On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:

> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> >
> > Ver Cluster Port Status Owner    Data directory              Log file
> > 11  main    5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main    5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> >
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> >
> > The  extension in question is pgemailaddr if this matters, BTW.
>
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
>
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
>
Well, this gets more interesting. I decided to try the reinstall thing
agai, so I did an apt-get purge on the V12 server, started the V11 server
(admiitely I did not check to see what port it started on), reinstalled
the V1 package, and now BOTH are running, with eh V12 server on 5432 and
the V11 on 5433.



--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Paul A Jungwirth
On 12/3/19 9:41 AM, stan wrote:
> Well, this gets more interesting. I decided to try the reinstall thing
> agai, so I did an apt-get purge on the V12 server, started the V11 server
> (admiitely I did not check to see what port it started on), reinstalled
> the V1 package, and now BOTH are running, with eh V12 server on 5432 and
> the V11 on 5433.

Yes, Ubuntu/Debian make it easy to run multiple versions/clusters at
once. I don't think they even use the alternatives system (but I could
be wrong). For building/installing the extension, the only important
part AIUI is getting pg_config to point at the right files. My own
approach has always been to hack the bash code to accept a PGCLUSTER
envvar, as in that Stack Overflow answer. Someone here might have a
better approach though.

Regards,

--
Paul              ~{:-)
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

stan-9
In reply to this post by Paul A Jungwirth

On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:

> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> the machine I am testing this on
> > now:
> >
> > Ver Cluster Port Status Owner    Data directory              Log file
> > 11  main    5433 down   postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main    5432 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> >
> > BUT, I went to the directory where I have the exentsion's source, did a
> > make clean ; make ; make install, and the files were still installed in the
> > V11 tree. How can I instruct the system to put these in the V12 tree?
> >
> > The  extension in question is pgemailaddr if this matters, BTW.
>
> That extension (and every extension I've seen) uses Postgres's normal
> extension-building infrastructure, so it runs pg_config to learn where to
> put files. If you run pg_config on your system it will probably report
> directories belonging to v11. (That's a little surprising because on Ubuntu
> systems I've always had it report the latest version.)
>
> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> which cluster to use. If you want to add that to your pg_config you could do
> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> PGCLUSTER before building. (Make sure you `make clean` first.)
>
And weirder.

So I purged the V1 server again, edited the postgresql.conf file to put it
back to port 5432, reinstalled the V12 package, and now:

stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main
/var/log/postgresql/postgresql-12-main.log

So, I decided to take a quick look at the production machine, just to make
sure this matched up to what was going on over there, and look what I found:

stan@ica-db:~$ htop
stan@ica-db:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-11-main.log
12  main    5432 online postgres /var/lib/postgresql/11/main
/var/log/postgresql/postgresql-12-main.log

This seems impossible to me.



--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Daniel Verite
In reply to this post by stan-9
        stan wrote:

> BUT, I went to the directory where I have the exentsion's source, did a
> make clean ; make ; make install, and the files were still installed in the
> V11 tree. How can I instruct the system to put these in the V12 tree?

With the Debian packaging, /usr/bin/pg_config is a shell script that
has this comment on top:

# If postgresql-server-dev-* is installed, call pg_config from the latest
# available one. Otherwise fall back to libpq-dev's version.

According to this comment, invoking /usr/bin/pg_config refers to the v11
server probably because you didn't install postgresql-server-dev-12 yet.

When you'll have both postgresql-server-dev-11 and postgresql-server-dev-12
installed, independently of which clusters are actually running,
you can still force a specific target version when compiling an extension
with :
 $ make PG_CONFIG=/usr/lib/postgresql/11/bin/pg_config [install]
or
 $ make PG_CONFIG=/usr/lib/postgresql/12/bin/pg_config [install]


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

Adrian Klaver-4
In reply to this post by stan-9
On 12/3/19 9:51 AM, stan wrote:

>
> On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
>> On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
>> the machine I am testing this on
>>> now:
>>>
>>> Ver Cluster Port Status Owner    Data directory              Log file
>>> 11  main    5433 down   postgres /var/lib/postgresql/11/main
>>> /var/log/postgresql/postgresql-11-main.log
>>> 12  main    5432 online postgres /var/lib/postgresql/12/main
>>> /var/log/postgresql/postgresql-12-main.log
>>>
>>> BUT, I went to the directory where I have the exentsion's source, did a
>>> make clean ; make ; make install, and the files were still installed in the
>>> V11 tree. How can I instruct the system to put these in the V12 tree?
>>>
>>> The  extension in question is pgemailaddr if this matters, BTW.
>>
>> That extension (and every extension I've seen) uses Postgres's normal
>> extension-building infrastructure, so it runs pg_config to learn where to
>> put files. If you run pg_config on your system it will probably report
>> directories belonging to v11. (That's a little surprising because on Ubuntu
>> systems I've always had it report the latest version.)
>>
>> Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
>> which cluster to use. If you want to add that to your pg_config you could do
>> it like this: https://stackoverflow.com/a/43403193/122087 Then just set
>> PGCLUSTER before building. (Make sure you `make clean` first.)
>>
> And weirder.
>
> So I purged the V1 server again, edited the postgresql.conf file to put it
> back to port 5432, reinstalled the V12 package, and now:
>
> stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
> Ver Cluster Port Status Owner    Data directory              Log file
> 11  main    5432 online postgres /var/lib/postgresql/11/main
> /var/log/postgresql/postgresql-11-main.log
> 12  main    5434 online postgres /var/lib/postgresql/12/main
> /var/log/postgresql/postgresql-12-main.log
>
> So, I decided to take a quick look at the production machine, just to make
> sure this matched up to what was going on over there, and look what I found:
>
> stan@ica-db:~$ htop
> stan@ica-db:~$ pg_lsclusters
> Ver Cluster Port Status Owner    Data directory              Log file
> 11  main    5432 online postgres /var/lib/postgresql/11/main
> /var/log/postgresql/postgresql-11-main.log
> 12  main    5432 online postgres /var/lib/postgresql/11/main
> /var/log/postgresql/postgresql-12-main.log
>
> This seems impossible to me.
>

What does ps ax | grep post show?

>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Issue upgrading from V11 to V12 on Debian

stan-9
On Tue, Dec 03, 2019 at 11:35:40AM -0800, Adrian Klaver wrote:

> On 12/3/19 9:51 AM, stan wrote:
> >
> > On Tue, Dec 03, 2019 at 08:58:58AM -0800, Paul Jungwirth wrote:
> > > On 12/3/19 8:46 AM, stan wrote:> So, I have V12 running as the default on
> > > the machine I am testing this on
> > > > now:
> > > >
> > > > Ver Cluster Port Status Owner    Data directory              Log file
> > > > 11  main    5433 down   postgres /var/lib/postgresql/11/main
> > > > /var/log/postgresql/postgresql-11-main.log
> > > > 12  main    5432 online postgres /var/lib/postgresql/12/main
> > > > /var/log/postgresql/postgresql-12-main.log
> > > >
> > > > BUT, I went to the directory where I have the exentsion's source, did a
> > > > make clean ; make ; make install, and the files were still installed in the
> > > > V11 tree. How can I instruct the system to put these in the V12 tree?
> > > >
> > > > The  extension in question is pgemailaddr if this matters, BTW.
> > >
> > > That extension (and every extension I've seen) uses Postgres's normal
> > > extension-building infrastructure, so it runs pg_config to learn where to
> > > put files. If you run pg_config on your system it will probably report
> > > directories belonging to v11. (That's a little surprising because on Ubuntu
> > > systems I've always had it report the latest version.)
> > >
> > > Many other Ubuntu Postgres commands accept a PGCLUSTER envvar to specific
> > > which cluster to use. If you want to add that to your pg_config you could do
> > > it like this: https://stackoverflow.com/a/43403193/122087 Then just set
> > > PGCLUSTER before building. (Make sure you `make clean` first.)
> > >
> > And weirder.
> >
> > So I purged the V1 server again, edited the postgresql.conf file to put it
> > back to port 5432, reinstalled the V12 package, and now:
> >
> > stan@stantest:~/src/pgemailaddr-master$ pg_lsclusters
> > Ver Cluster Port Status Owner    Data directory              Log file
> > 11  main    5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main    5434 online postgres /var/lib/postgresql/12/main
> > /var/log/postgresql/postgresql-12-main.log
> >
> > So, I decided to take a quick look at the production machine, just to make
> > sure this matched up to what was going on over there, and look what I found:
> >
> > stan@ica-db:~$ htop
> > stan@ica-db:~$ pg_lsclusters
> > Ver Cluster Port Status Owner    Data directory              Log file
> > 11  main    5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-11-main.log
> > 12  main    5432 online postgres /var/lib/postgresql/11/main
> > /var/log/postgresql/postgresql-12-main.log
> >
> > This seems impossible to me.
> >
>
> What does ps ax | grep post show?
>

On the production machine, just teh V11 biaries.

Looks like the pg_lsclusters methond may not be totaly relaible.

BGot the test machine upgraded.

Thanks to all the helpful folks that provided expert advice on this.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin