Обсуждение: Issue upgrading from V11 to V12 on Debian
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
stan <stanb@panix.com> 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
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 ~{:-)
pj@illuminatedcomputing.com
On Tue, Dec 03, 2019 at 11:13:55AM -0500, Tom Lane wrote:
> stan <stanb@panix.com> 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
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 ~{:-) pj@illuminatedcomputing.com
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
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
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 ~{:-)
pj@illuminatedcomputing.com
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
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
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 adrian.klaver@aklaver.com
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