pg_upgrade 13.6 to 15.1?

pf@pfortin.com writes:

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

This utility can only upgrade to PostgreSQL version 15. <=====<<< ??

This indicates that it thinks the new data directory (-D) is the
wrong version. Perhaps the error message could be clearer about that.

Due to the database size, I have the working copy on a 4TB NVMe SSD
mounted at /mnt/work/var/lib/{pgadmin,pgsql} and an identical copy on an
18TB platter at /mnt/db/var/lib/{pgadmin,pgsql}. Both copies are
currently at 13.6.

I think you misunderstand how this is supposed to work. The -D
argument should point at an *empty* data directory that has been
freshly initialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).

regards, tom lane

On 1/15/23 11:27, pf@pfortin.com wrote:

Hi,

I'm fairly new to postgres; but have databases with about 2TB of data.

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

Nothing I read implies the need to upgrade to 14.x first... Right?

In addition to Tom Lane's comments I would recommend reading this:

https://www.postgresql.org/docs/current/pgupgrade.html

multiple times. There is a lot going on there and it will take a couple
of reads at least to begin to understand it all.

Thanks
Pierre

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:

pf@pfortin.com writes:

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

This utility can only upgrade to PostgreSQL version 15. <=====<<< ??

This indicates that it thinks the new data directory (-D) is the
wrong version. Perhaps the error message could be clearer about that.

Due to the database size, I have the working copy on a 4TB NVMe SSD
mounted at /mnt/work/var/lib/{pgadmin,pgsql} and an identical copy on an
18TB platter at /mnt/db/var/lib/{pgadmin,pgsql}. Both copies are
currently at 13.6.

I think you misunderstand how this is supposed to work. The -D
argument should point at an *empty* data directory that has been
freshly initialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).

I was hoping to avoid the hours worth of copying to the NVMe SSD.
The instructions refer to upgrading with --link; would that save the copy
time? I have an identical copy of the DB, so could recover if necessary
or just go the initdb route. I wasn't clear on the exact syntax for
including --link... if that's an option I can use, do I eliminate one of
the -d or -D parameters?

Thanks!

On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:

On 1/15/23 11:27, pf@pfortin.com wrote:

Hi,

I'm fairly new to postgres; but have databases with about 2TB of data.

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

Nothing I read implies the need to upgrade to 14.x first... Right?

In addition to Tom Lane's comments I would recommend reading this:

https://www.postgresql.org/docs/current/pgupgrade.html

multiple times. There is a lot going on there and it will take a couple
of reads at least to begin to understand it all.

Yup... that's what I've been working from... See my reply to Tom re
--link...
Thanks!

pf@pfortin.com writes:

On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:

I think you misunderstand how this is supposed to work. The -D
argument should point at an *empty* data directory that has been
freshly initialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).

I was hoping to avoid the hours worth of copying to the NVMe SSD.
The instructions refer to upgrading with --link; would that save the copy
time?

Yes, but to use --link you must have both data directories on the
same filesystem, so this is still the wrong thing.

Try something like

mv /mnt/work/var/lib/pgsql/data /mnt/work/var/lib/pgsql/data13
initdb /mnt/work/var/lib/pgsql/data
pg_upgrade ... -d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link ...

regards, tom lane

On 1/15/23 12:41, pf@pfortin.com wrote:

On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:

On 1/15/23 11:27, pf@pfortin.com wrote:

Hi,

I'm fairly new to postgres; but have databases with about 2TB of data.

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

Nothing I read implies the need to upgrade to 14.x first... Right?

In addition to Tom Lane's comments I would recommend reading this:

https://www.postgresql.org/docs/current/pgupgrade.html

multiple times. There is a lot going on there and it will take a couple
of reads at least to begin to understand it all.

Yup... that's what I've been working from... See my reply to Tom re
--link...

1) Working from and understanding are two different things. For instance
further on in the docs there is:

--clone

Use efficient file cloning (also known as “reflinks” on some
systems) instead of copying files to the new cluster. This can result in
near-instantaneous copying of the data files, giving the speed
advantages of -k/--link while leaving the old cluster untouched.

File cloning is only supported on some operating systems and file
systems. If it is selected but not supported, the pg_upgrade run will
error. At present, it is supported on Linux (kernel 4.5 or later) with
Btrfs and XFS (on file systems created with reflink support), and on
macOS with APFS.

2) From the docs:

Run pg_upgrade

Always run the pg_upgrade binary of the new server, not the old one.
pg_upgrade requires the specification of the old and new cluster's data
and executable (bin) directories. You can also specify user and port
values, and whether you want the data files linked or cloned instead of
the default copy behavior.

3) Again, read the docs multiple times there is a lot to understand.

Thanks!

Thanks
Pierre

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

--clone

I think --clone is probably contraindicated here, given that Pierre
already made a copy of the data. If I understand how that works,
it'll just wind up making another whole copy, but in a time-extended
manner as the tables are modified. Over the long run there would
still be two copies of the DB on the new disk, which doesn't seem
like what he wants.

regards, tom lane

On Sun, 15 Jan 2023 13:00:58 -0800 Adrian Klaver wrote:

On 1/15/23 12:41, pf@pfortin.com wrote:

On Sun, 15 Jan 2023 12:23:10 -0800 Adrian Klaver wrote:

On 1/15/23 11:27, pf@pfortin.com wrote:

Hi,

I'm fairly new to postgres; but have databases with about 2TB of data.

Trying to upgrade from 13.6 to 15.1, pg_upgrade complains with:
[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin \
-d /mnt/db/var/lib/pgsql/data -D /mnt/work/var/lib/pgsql/data \
-s /tmp -U postgres

Nothing I read implies the need to upgrade to 14.x first... Right?

In addition to Tom Lane's comments I would recommend reading this:

https://www.postgresql.org/docs/current/pgupgrade.html

multiple times. There is a lot going on there and it will take a couple
of reads at least to begin to understand it all.

Yup... that's what I've been working from... See my reply to Tom re
--link...

1) Working from and understanding are two different things. For instance
further on in the docs there is:

--clone

Use efficient file cloning (also known as “reflinks” on some
systems) instead of copying files to the new cluster. This can result in
near-instantaneous copying of the data files, giving the speed
advantages of -k/--link while leaving the old cluster untouched.

File cloning is only supported on some operating systems and file
systems. If it is selected but not supported, the pg_upgrade run will
error. At present, it is supported on Linux (kernel 4.5 or later) with
Btrfs and XFS (on file systems created with reflink support), and on

^^^^^^^^^^^^^

Unless there's an update to pg_upgrade that's still undocumented; this is
not an option since like so many Linux users, my file system is ext4.

macOS with APFS.

2) From the docs:

Run pg_upgrade

Always run the pg_upgrade binary of the new server, not the old one.
pg_upgrade requires the specification of the old and new cluster's data
and executable (bin) directories. You can also specify user and port
values, and whether you want the data files linked or cloned instead of
the default copy behavior.

3) Again, read the docs multiple times there is a lot to understand.

Agreed. But they could be a little clearer... :)

On Sun, 15 Jan 2023 15:59:20 -0500 Tom Lane wrote:

pf@pfortin.com writes:

On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:

I think you misunderstand how this is supposed to work. The -D
argument should point at an *empty* data directory that has been
freshly initialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).

I was hoping to avoid the hours worth of copying to the NVMe SSD.
The instructions refer to upgrading with --link; would that save the copy
time?

Yes, but to use --link you must have both data directories on the
same filesystem, so this is still the wrong thing.

Try something like

My understanding:

mv /mnt/work/var/lib/pgsql/data /mnt/work/var/lib/pgsql/data13

- renames the DB

initdb /mnt/work/var/lib/pgsql/data

- creates new DB

pg_upgrade ... -d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link ...

- if this only creates hard links; then this should do what I want.
My big concern was due to the DB being about 65% of /mnt/work; so doing
it on the same file system absolutely requires hard links vs copying...

Looks like this is what I was trying to be certain of... Thanks!!
Pierre

On Sun, 15 Jan 2023 16:38:08 -0500 pf@pfortin.com wrote:

On Sun, 15 Jan 2023 15:59:20 -0500 Tom Lane wrote:

pf@pfortin.com writes:

On Sun, 15 Jan 2023 14:47:35 -0500 Tom Lane wrote:

I think you misunderstand how this is supposed to work. The -D
argument should point at an *empty* data directory that has been
freshly initialized with the new version's initdb. pg_upgrade then
transfers data into that from the old database (-d argument).

I was hoping to avoid the hours worth of copying to the NVMe SSD.
The instructions refer to upgrading with --link; would that save the copy
time?

Yes, but to use --link you must have both data directories on the
same filesystem, so this is still the wrong thing.

Try something like

My understanding:

mv /mnt/work/var/lib/pgsql/data /mnt/work/var/lib/pgsql/data13

- renames the DB

initdb /mnt/work/var/lib/pgsql/data

- creates new DB

pg_upgrade ... -d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link ...

- if this only creates hard links; then this should do what I want.
My big concern was due to the DB being about 65% of /mnt/work; so doing
it on the same file system absolutely requires hard links vs copying...

Looks like this is what I was trying to be certain of... Thanks!!
Pierre

Sigh... I thought all was good... This was not expected and is not
discussed in the pg_upgrade instructions:

[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin
-d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link
-U postgres Performing Consistency Checks -----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Creating dump of global objects ok
Creating dump of database schemas
ok

encodings for database "template1" do not match: old "UTF8", new
"SQL_ASCII" Failure, exiting

"template1" is not a DB I've ever messed with; so this will require that
I fire up the old version and change the encoding somehow?

Is this likely to repeat for my actual databases?

Sorry if this is noise...

On Sun, 2023-01-15 at 16:59 -0500, pf@pfortin.com wrote:

encodings for database "template1" do not match:  old "UTF8", new
"SQL_ASCII" Failure, exiting

You almost certainly don't want your new database to use SQL_ASCII.
Init the new cluster with -E UTF8.

On 16 Jan 2023, at 8:59, pf@pfortin.com wrote:

encodings for database "template1" do not match: old "UTF8", new
"SQL_ASCII" Failure, exiting

Suggest the old dB using UTF8 is the better practice, and the new dB should do likewise

"template1" is not a DB I've ever messed with; so this will require that
I fire up the old version and change the encoding somehow?

This is created at initdb and mostly you don’t need/want to mess with it

Is this likely to repeat for my actual databases?

AFAICT the least work option is to redo the initdb for the new v15.1 database. There is a lot of pain (and potential data corruption) to be had trying to reconfigure the old one before it can be moved.

Personally, UTF8 is the way to go. It will handle everything in the old database and the future brings to the new one. I can see no advantage in pure ASCII when there is the potential for the real world to be contributing text. And there could well be non-ASCII characters lurking in the old dB, especially since someone set it up to receive them.

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920

pf@pfortin.com writes:

encodings for database "template1" do not match: old "UTF8", new
"SQL_ASCII" Failure, exiting

So you need to do the initdb under the same locale setting you
used for the old DB. Looking into its LC_XXX settings should
refresh your memory on what that was.

regards, tom lane

On Mon, 16 Jan 2023 09:16:27 +1100 Gavan Schneider wrote:

On 16 Jan 2023, at 8:59, pf@pfortin.com wrote:

encodings for database "template1" do not match: old "UTF8", new
"SQL_ASCII" Failure, exiting

Suggest the old dB using UTF8 is the better practice, and the new dB should do likewise

I was surprised to see pg_upgrade even suggest that...

"template1" is not a DB I've ever messed with; so this will require that
I fire up the old version and change the encoding somehow?

This is created at initdb and mostly you don’t need/want to mess with it

I should have checked "initdb --help" first; but now I've used:
initdb -E UTF8 /mnt/work/var/lib/pgsql/data

and got burned (2nd time) by one file with root:root ownership; fixed
with:
chown postgres:postgres /mnt/work/var/lib/pgsql/data13/base/24597/35874

I have no clue how a single file would have root ownership; but found
this a few days ago, and forgot to fix it in both copies of the DB. Of
course, this put me on a new path of [recoverable] disaster... ;/

pg_upgrade aborted on it; but only after getting to a point of "no
return". The documentation alludes to checking everything before
proceeding; but it's the story of my life to find the unexpected...

[postgres@pf ~]$ /usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin
-d /mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link
-U postgres
Performing Consistency Checks -----------------------------
[snip]
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
[snip]
Adding ".old" suffix to old global/pg_control ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from
/mnt/work/var/lib/pgsql/data13/global/pg_control.old. Because "link" mode
was used, the old cluster cannot be safely started once the new cluster
has been started.

Linking user relation files
/mnt/work/var/lib/pgsql/data13/base/24597/35874
error while creating link for relation "public.vr_snapshot_2022_01_01"
("/mnt/work/var/lib/pgsql/data13/base/24597/35874" to
"/mnt/work/var/lib/pgsql/data/base/24597/35874"): Operation not permitted
Failure, exiting

OK... starting over...
rm -rf data
initdb -E UTF8 /mnt/work/var/lib/pgsql/data
/usr/bin/pg_upgrade -b /usr/local/pgsql/bin -B /usr/bin -d
/mnt/work/var/lib/pgsql/data13 -D /mnt/work/var/lib/pgsql/data --link -U
postgres
Performing Consistency Checks -----------------------------
Checking cluster versions ok
pg_controldata: fatal: could not open file
"/mnt/work/var/lib/pgsql/data13/global/pg_control" for reading: No such
file or directory

The source cluster lacks cluster state information:
Failure, exiting

Sigh... this should "fix" it:
mv /mnt/work/var/lib/pgsql/data13/global/pg_control.old
/mnt/work/var/lib/pgsql/data13/global/pg_control

Yup... Success!! Upgrade done. Again, sorry for the noise; but hope
the above helps with other issues that can go wrong during an upgrade...

Thanks Tom, Gavan, et al!!

Pierre

On Sun, Jan 15, 2023 at 04:27:50PM -0500, pf@pfortin.com wrote:

3) Again, read the docs multiple times there is a lot to understand.

Agreed. But they could be a little clearer... :)

Agreed the docs are complex, but how can they be clearer?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Embrace your flaws. They make you human, rather than perfect,
which you will never be.