Thread: Upgrade from 9.5.4 to 9.6.1
All my previous version upgrades were performed by running pg_dumpall in the older version followed by running 'pgsql -f ...' to install the dumped .sql file, usually because the upgrade jumped several versions. Now I'd like to try the available postgres commands. The older version is installed in /var/lib/pgsql/9.5/data and I just initiated the new version in /var/lib/pgsql/9.6/data. The earlier version is currently running. Is pg_upgrade the recommended way to upgrade from one minor version to the next? The 9.5 manual recommends this approach for _major_ upgrades (e.g., 8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). That's a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 is a second digit upgrade I suppose it's semi-major, but in which upgrade camp does it belong? The command is: pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir and I don't know where to find -b and -B. On my Slackware-14.1 server I have /usr/bin/postgres and assume it is for the 9.5 release since that's running and the 9.6 release is initiated but not invoked. The data directories are easy but where do I look for the two bindirs? TIA, Rich
On 11/15/2016 2:37 PM, Rich Shepard wrote: > All my previous version upgrades were performed by running > pg_dumpall in > the older version followed by running 'pgsql -f ...' to install the > dumped > .sql file, usually because the upgrade jumped several versions. Now > I'd like > to try the available postgres commands. > > The older version is installed in /var/lib/pgsql/9.5/data and I just > initiated the new version in /var/lib/pgsql/9.6/data. The earlier > version is > currently running. > > Is pg_upgrade the recommended way to upgrade from one minor version > to the > next? The 9.5 manual recommends this approach for _major_ upgrades (e.g., > 8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). > That's > a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 > is a > second digit upgrade I suppose it's semi-major, but in which upgrade camp > does it belong? > 9.5 is considered a major version, 9.5.4 is a minor. this will change when 10 is released, from 10 on, major versions will be 10, 11, 12, ... > The command is: > > pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir > > and I don't know where to find -b and -B. On my Slackware-14.1 server > I have > /usr/bin/postgres and assume it is for the 9.5 release since that's > running > and the 9.6 release is initiated but not invoked. The data directories > are > easy but where do I look for the two bindirs? based on the data paths you gave, I'm guessing you're on a redhat/centos/fedora type distribution? those put the binaries in /usr/pgsql-X.Y/bin for version X.Y -- john r pierce, recycling bits in santa cruz
On 11/15/2016 02:37 PM, Rich Shepard wrote: > All my previous version upgrades were performed by running pg_dumpall in > the older version followed by running 'pgsql -f ...' to install the dumped > .sql file, usually because the upgrade jumped several versions. Now I'd > like > to try the available postgres commands. > > The older version is installed in /var/lib/pgsql/9.5/data and I just > initiated the new version in /var/lib/pgsql/9.6/data. The earlier > version is > currently running. > > Is pg_upgrade the recommended way to upgrade from one minor version to > the > next? The 9.5 manual recommends this approach for _major_ upgrades (e.g., > 8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). > That's > a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 is a > second digit upgrade I suppose it's semi-major, but in which upgrade camp > does it belong? > > The command is: > > pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir > > and I don't know where to find -b and -B. On my Slackware-14.1 server I > have > /usr/bin/postgres and assume it is for the 9.5 release since that's running > and the 9.6 release is initiated but not invoked. The data directories are > easy but where do I look for the two bindirs? Assuming the bindirs are in your $PATH: aklaver@panda:~> whereis -f pg_ctl pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl Even if only one is the $PATH: aklaver@panda:~> whereis -f pg_ctl pg_ctl: /usr/local/pgsql/bin/pg_ctl you can usually figure out where the other is. > > TIA, > > Rich > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 15 Nov 2016, John R Pierce wrote: > 9.5 is considered a major version, 9.5.4 is a minor. this will change > when 10 is released, from 10 on, major versions will be 10, 11, 12, ... John, So the upgrade from 9.5.4 to 9.6.1 is as I assumed: major. > based on the data paths you gave, I'm guessing you're on a > redhat/centos/fedora type distribution? those put the binaries in > /usr/pgsql-X.Y/bin for version X.Y Actually, slackware. No /usr/pgsql-x/ at all. Slackware binaries (and packages such as postgres provided by SlackBuilds.org are all installed in /bin/. There are no /bin directories associated with postgres. So, I'll go the pg_dumpall route then. Thanks very much, Rich
On 11/15/2016 4:55 PM, Adrian Klaver wrote:
where do I look for the two bindirs?
Assuming the bindirs are in your $PATH:
aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl
Even if only one is the $PATH:
aklaver@panda:~> whereis -f pg_ctl
pg_ctl: /usr/local/pgsql/bin/pg_ctl
you can usually figure out where the other is.
the RHEL style distributions don't put the actual bin dirs in the path, rather, they put symlinks to the common user utilities in /usr/bin managed via a system known as 'alternates'.
-- john r pierce, recycling bits in santa cruz
On Tue, 15 Nov 2016, Adrian Klaver wrote: > Assuming the bindirs are in your $PATH: > > aklaver@panda:~> whereis -f pg_ctl Adrian, In my case: $ whereis -f pg_ctl pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl /usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1 /usr/man/man1/pg_ctl.1.gz /usr/share/man/man1/pg_ctl.1 /usr/share/man/man1/pg_ctl.1.gz /usr/X11/man/man1/pg_ctl.1 /usr/X11/man/man1/pg_ctl.1.gz > Even if only one is the $PATH: > you can usually figure out where the other is. I suppose if I knew which version is in each of those directories it would make life easier. Currently, only 9.5.4 is running; 9.6.1 is installed and initiated but not running. When I invoke 'psql -l' I see the clusters installed in 9.5.4. Will use pg_dumpall and pg_restore ... tomorrow morning. Thanks very much, Rich
On 11/15/2016 05:01 PM, John R Pierce wrote: > On 11/15/2016 4:55 PM, Adrian Klaver wrote: >>> where do I look for the two bindirs? >> >> Assuming the bindirs are in your $PATH: >> >> aklaver@panda:~> whereis -f pg_ctl >> pg_ctl: /usr/local/pgsql/bin/pg_ctl /usr/local/pgsql94/bin/pg_ctl >> >> Even if only one is the $PATH: >> >> aklaver@panda:~> whereis -f pg_ctl >> pg_ctl: /usr/local/pgsql/bin/pg_ctl >> >> you can usually figure out where the other is. > > the RHEL style distributions don't put the actual bin dirs in the path, > rather, they put symlinks to the common user utilities in /usr/bin > managed via a system known as 'alternates'. Rich is using Slackware and I am pretty sure it marches to a different drummer. http://www.slackware.com/faq/do_faq.php?faq=installation#2 > > > -- > john r pierce, recycling bits in santa cruz > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 15 Nov 2016, Adrian Klaver wrote: > Rich is using Slackware and I am pretty sure it marches to a different > drummer. Adrian, And a different band. Pat likes MariaDB which replaced MySQL so PostgreSQL is not part of the base distribution. But, SlackBuilds.org provides a build script that's worked for me since some early version 7 of postgres. Regards, Rich
On 11/15/2016 05:06 PM, Rich Shepard wrote: > On Tue, 15 Nov 2016, Adrian Klaver wrote: > >> Assuming the bindirs are in your $PATH: >> >> aklaver@panda:~> whereis -f pg_ctl > > Adrian, > > In my case: > > $ whereis -f pg_ctl > pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl > /usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1 > /usr/man/man1/pg_ctl.1.gz /usr/share/man/man1/pg_ctl.1 > /usr/share/man/man1/pg_ctl.1.gz /usr/X11/man/man1/pg_ctl.1 > /usr/X11/man/man1/pg_ctl.1.gz So what do you get with /usr/bin/pg_ctl --version and given John's suggestion: ls -al /usr/bin/pg_ctl > >> Even if only one is the $PATH: > >> you can usually figure out where the other is. > > I suppose if I knew which version is in each of those directories it > would > make life easier. > > Currently, only 9.5.4 is running; 9.6.1 is installed and initiated but > not > running. When I invoke 'psql -l' I see the clusters installed in 9.5.4. So how are you going to start the 9.6 instance? > > Will use pg_dumpall and pg_restore ... tomorrow morning. > > Thanks very much, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/15/2016 05:18 PM, Rich Shepard wrote: > On Tue, 15 Nov 2016, Adrian Klaver wrote: > >> Rich is using Slackware and I am pretty sure it marches to a different >> drummer. > > Adrian, > > And a different band. Pat likes MariaDB which replaced MySQL so > PostgreSQL > is not part of the base distribution. But, SlackBuilds.org provides a build > script that's worked for me since some early version 7 of postgres. Aah, so the answer is here: https://slackbuilds.org/slackbuilds/14.2/system/postgresql/README.SBo "A few hints for PostgreSQL 9.5 -> 9.6 upgrade: .... " > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/15/2016 05:18 PM, Rich Shepard wrote: > On Tue, 15 Nov 2016, Adrian Klaver wrote: > >> Rich is using Slackware and I am pretty sure it marches to a different >> drummer. > > Adrian, > > And a different band. Pat likes MariaDB which replaced MySQL so > PostgreSQL > is not part of the base distribution. But, SlackBuilds.org provides a build > script that's worked for me since some early version 7 of postgres. To add to my previous post. If you do decide to follow the pg_upgrade procedure in the README do a pg_dump of the 9.5 data just before you do pg_ugrade and store it away in a safe place. The first time through a new process does not always end well:) > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 15 Nov 2016, Adrian Klaver wrote:
> So what do you get with
> /usr/bin/pg_ctl --version
Interesting:
$ /usr/bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.6.1
> and given John's suggestion:
> ls -al /usr/bin/pg_ctl
ls -al /usr/bin/pg_ctl
lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl ->
../lib/postgresql/9.6/bin/pg_ctl*
Aha! That's where the bin/ directories are. There's a 9.3.4/ and 9.5/
subdirectory there, too.
Will do a pg_dumpall and then run pg_upgrade.
Thanks, both of you. I learned valuable lessons,
Rich
On Tue, 15 Nov 2016, Rich Shepard wrote: > $ /usr/bin/pg_ctl --version > pg_ctl (PostgreSQL) 9.6.1 > > ls -al /usr/bin/pg_ctl > lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl -> > ../lib/postgresql/9.6/bin/pg_ctl* To increase my understanding I want to resolve an apparent discrepancy in versions. After installing and initiating 9.6.1 that's reported as the current running version, yet I've not yet migrated the cluster from 9.5.4 to 9.6.1. Last week, after a kernel upgrade, I rebooted the system and re-started postgres pointing to the 9.5/data/ directory. When I enter the command 'psql -l' I see all the databases and assumed they running on 9.5.4. But that's not the case, is it? If 9.6.1 is currently running after running initdb, and I can access my databases, what does pg_upgrade do that's necessary? A curious mind wants to learn, Rich
On 16 November 2016 at 16:33, Rich Shepard <rshepard@appl-ecosys.com> wrote: > On Tue, 15 Nov 2016, Rich Shepard wrote: > If 9.6.1 is currently running after running initdb, and I can access my > databases, what does pg_upgrade do that's necessary? pg_upgrade migrates your databases from your old (9.5) cluster to the new (9.6) one. Initdb doesn't do that. If your 9.6 database does indeed contain your databases, then something must have done the pg_upgrade for you. Perhaps the slackware package script does something like that, but that would cause problems for people who do _not_ want to migrate their databases, so I doubt that's what happened. What I think what happened is that you are using the new pg 9.6 psql binary to list the databases in your old 9.5 cluster. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
On Wed, 16 Nov 2016, Alban Hertroys wrote: > pg_upgrade migrates your databases from your old (9.5) cluster to the > new (9.6) one. Initdb doesn't do that. Alban, That's what I assumed to be the case. > If your 9.6 database does indeed contain your databases, then something > must have done the pg_upgrade for you. Perhaps the slackware package > script does something like that, No, it only builds the package. I manually install it and run initdb. > What I think what happened is that you are using the new pg 9.6 psql > binary to list the databases in your old 9.5 cluster. Not only list them, but access them from the command line. This is what I'm trying to understand. Regardless, I'll stop the running postgres, run pg_upgrade, then start it from the /9.6/data/ directory. Thanks, Rich
On Tue, 15 Nov 2016, Adrian Klaver wrote: > To add to my previous post. If you do decide to follow the pg_upgrade > procedure in the README do a pg_dump of the 9.5 data just before you do > pg_ugrade and store it away in a safe place. The first time through a new > process does not always end well:) Adrian, et al.: Did a pg_dumpall and stored it with my database-backups. Now that I know where to find the bin/ directories pg_upgrade ran flawlessly. Quick and easy. Thanks all, Rich