Pre-version pg_upgrade syntax check

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