Обсуждение: Pre-version pg_upgrade syntax check
Running Slackware-14.2/x86_64. Current installation is postgres-11.5 and I'm upgrading to postgresql-12.1. Both versions are installed and stopped. If I have correctly read the pg_upgrade manual page this is the command to use (as user postgres) with lines split to fit the message; the command will be on a single line: pg_upgrade \ -b /usr/lib64/postgresql/11/bin/ \ -B /usr/lib64/postgesql/12/bin/ \ -d /var/lib/pgsql/11/data/ \ -D /var/lib/pgsql/12/data/ \ -p 5432 \ -P 5432 I don't know that the port numbers are required. Have I missed an option? Rich
On Mon, 10 Feb 2020, Rich Shepard wrote: > -B /usr/lib64/postgesql/12/bin/ \ Typo: it should be /usr/lib64/postgresql/12/bin/ Rich
On 2/10/20 1:59 PM, Rich Shepard wrote: > Running Slackware-14.2/x86_64. Current installation is postgres-11.5 and > I'm > upgrading to postgresql-12.1. Both versions are installed and stopped. > > If I have correctly read the pg_upgrade manual page this is the command to > use (as user postgres) with lines split to fit the message; the command > will > be on a single line: > > pg_upgrade \ > -b /usr/lib64/postgresql/11/bin/ \ > -B /usr/lib64/postgesql/12/bin/ \ > -d /var/lib/pgsql/11/data/ \ > -D /var/lib/pgsql/12/data/ \ > -p 5432 \ > -P 5432 > > I don't know that the port numbers are required. They are not: https://www.postgresql.org/docs/12/pgupgrade.html "Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade defaults to running servers on port 50432 to avoid unintended client connections. You can use the same port number for both clusters when doing an upgrade because the old and new clusters will not be running at the same time. However, when checking an old running server, the old and new port numbers must be different." > > Have I missed an option? Not a required one. Best way to find out is to run --check. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 10 Feb 2020, Adrian Klaver wrote: >> I don't know that the port numbers are required. > They are not: https://www.postgresql.org/docs/12/pgupgrade.html > "Obviously, no one should be accessing the clusters during the upgrade. > pg_upgrade defaults to running servers on port 50432 to avoid unintended > client connections. You can use the same port number for both clusters > when doing an upgrade because the old and new clusters will not be running > at the same time. However, when checking an old running server, the old > and new port numbers must be different." Adrian, As I'm the only user of the databases, and both are shut down, I assumed specifying port numbers was not needed. >> Have I missed an option? > Not a required one. Best way to find out is to run --check. Good advice; I'll do that. Thanks, Rich
On Mon, 10 Feb 2020, Rich Shepard wrote: > Good advice; I'll do that. Huh! Not what I expected: postgres@salmo:~$ pg_upgrade -c -b /usr/lib64/postgresql/11/bin/ -B /usr/lib64/postgresql/12/bin/ -d /var/lib/pgsql/11/data -D /var/lib/pgsql/12/data/ (All on one line; wrapped by alpine.) Performing Consistency Checks ----------------------------- Checking cluster versions Old cluster data and binary directories are from different major versions. Failure, exiting Well, of course they're from different major versions, that's why I'm running pg_upgrade. What did I do incorrectly? Rich
On 2/10/20 3:40 PM, Rich Shepard wrote: > On Mon, 10 Feb 2020, Rich Shepard wrote: > >> Good advice; I'll do that. > > Huh! Not what I expected: > > postgres@salmo:~$ pg_upgrade -c -b /usr/lib64/postgresql/11/bin/ -B > /usr/lib64/postgresql/12/bin/ -d /var/lib/pgsql/11/data -D > /var/lib/pgsql/12/data/ > > (All on one line; wrapped by alpine.) > > Performing Consistency Checks > ----------------------------- > Checking cluster versions Old cluster data and binary directories are > from different major versions. > Failure, exiting > > Well, of course they're from different major versions, that's why I'm > running pg_upgrade. What did I do incorrectly? What it is saying is -b and -d are pointing at binary and data directories that are incompatible. You need to make sure that: /usr/lib64/postgresql/11/bin/ and /var/lib/pgsql/11/data are actually pointing at 11 instances of binaries and data respectively. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 10 Feb 2020, Adrian Klaver wrote: > What it is saying is -b and -d are pointing at binary and data directories > that are incompatible. You need to make sure that: > > /usr/lib64/postgresql/11/bin/ > and > /var/lib/pgsql/11/data > > are actually pointing at 11 instances of binaries and data respectively. Adrian, I just copied the contents of those directories from last Thursday's backups. Still fails. I have the pg_dumpall .sql file from last Thursday. I'll fire up postgresql-12.1 and us pgsql to read in that file, unless you have a recommendation for me to apply. Thanks, Rich
On 2/10/20 4:07 PM, Rich Shepard wrote: > On Mon, 10 Feb 2020, Adrian Klaver wrote: > >> What it is saying is -b and -d are pointing at binary and data >> directories >> that are incompatible. You need to make sure that: >> >> /usr/lib64/postgresql/11/bin/ >> and >> /var/lib/pgsql/11/data >> >> are actually pointing at 11 instances of binaries and data respectively. > > Adrian, > > I just copied the contents of those directories from last Thursday's > backups. Still fails. So you already have 11 and 12 instances of Postgres running? If so why use pg_upgrade? To verify what is going on do: /usr/lib64/postgresql/11/bin/psql --version vi /var/lib/pgsql/11/data/PG_VERSION > > I have the pg_dumpall .sql file from last Thursday. I'll fire up > postgresql-12.1 and us pgsql to read in that file, unless you have a > recommendation for me to apply. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 10 Feb 2020, Adrian Klaver wrote: > So you already have 11 and 12 instances of Postgres running? Adrian, No. Both 11 and 12 are installed; neither is running. I have a cron job that runs pg_dumpall every weekday night. > If so why use pg_upgrade? Because I wanted to try it rather than use 'psql -f <filename>.sql' > To verify what is going on do: > /usr/lib64/postgresql/11/bin/psql --version The pg_upgrade page says to not have either the old or new versions running. > vi /var/lib/pgsql/11/data/PG_VERSION /var/lib/pgsql/11/data/PG_VERSION is 11; /var/lib/pgsql/12/data/PG_VERSION is 12. Rich
On Tue, 11 Feb 2020, Rich Shepard wrote: >> So you already have 11 and 12 instances of Postgres running? Adrian, I just started 12.1 and, as user postgres, read last Friday's backup file. All's well. A final question: which conf file do I edit so when I enter 'psql -l' (or open a specific database) I don't need to enter my password? I don't recall having to reset this permission with prior upgrades and want to do so now. Regards, Rich
On Tue, 11 Feb 2020, Rich Shepard wrote: > A final question: which conf file do I edit so when I enter 'psql -l' (or > open a specific database) I don't need to enter my password? I don't > recall having to reset this permission with prior upgrades and want to do > so now. Never mind: I found it: /var/lib/pgsql/12/data/pg_hba.conf. Thanks for all you help. Rich
On 2/11/20 5:43 AM, Rich Shepard wrote: > On Mon, 10 Feb 2020, Adrian Klaver wrote: > >> So you already have 11 and 12 instances of Postgres running? > > Adrian, > > No. Both 11 and 12 are installed; neither is running. I have a cron job > that > runs pg_dumpall every weekday night > >> If so why use pg_upgrade? > > Because I wanted to try it rather than use 'psql -f <filename>.sql' > >> To verify what is going on do: >> /usr/lib64/postgresql/11/bin/psql --version > > The pg_upgrade page says to not have either the old or new versions > running. The above runs the psql client not the server. It is a way of determining what version binaries /usr/lib64/postgresql/11/bin/ actually contains. > >> vi /var/lib/pgsql/11/data/PG_VERSION > > /var/lib/pgsql/11/data/PG_VERSION is 11; /var/lib/pgsql/12/data/PG_VERSION > is 12. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 11 Feb 2020, Adrian Klaver wrote: > The above runs the psql client not the server. It is a way of determining > what version binaries /usr/lib64/postgresql/11/bin/ actually contains. Adrian, Aha! Running the command taught me a couple of valuable lessons because both 11 and 12 show they're running 12.1. That's because the SlackBuilds.org build script apparently upgraded 11.5 to 12.1 in the same /usr/lib64/postgresql/11/ directory. Before the next major version upgrade I'll ensure the new version is installed in the proper subdirectory. Since 12.1 is running I'll remove the version 11 directories. Thanks very much, Rich
On 2/11/20 9:00 AM, Rich Shepard wrote: > On Tue, 11 Feb 2020, Adrian Klaver wrote: > >> The above runs the psql client not the server. It is a way of determining >> what version binaries /usr/lib64/postgresql/11/bin/ actually contains. > > Adrian, > > Aha! Running the command taught me a couple of valuable lessons because > both > 11 and 12 show they're running 12.1. That's because the SlackBuilds.org > build script apparently upgraded 11.5 to 12.1 in the same > /usr/lib64/postgresql/11/ directory. Before the next major version upgrade From here: https://slackbuilds.org/repository/14.2/system/postgresql/ the latest build is for Postgres 10.10 and it does: PG_VERSION=${PG_VERSION:-10.10} ./configure \ --prefix=/usr/lib${LIBDIRSUFFIX}/$PRGNAM/$PG_VERSION \ --sysconfdir=/etc/$PRGNAM/$PG_VERSION \ --includedir=/usr/include \ --datarootdir=/usr/share \ --mandir=/usr/man \ --docdir=/usr/doc/$PRGNAM-$VERSION \ --datadir=/usr/share/$PRGNAM-$PG_VERSION \ ... Is there maybe a custom script in the mix somewhere? Or something else setting PG_VERSION? > I'll ensure the new version is installed in the proper subdirectory. > > Since 12.1 is running I'll remove the version 11 directories. > > Thanks very much, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com