Обсуждение: pg_upgrade 13.6 to 15.1?
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
Performing Consistency Checks
-----------------------------
Checking cluster versions
This utility can only upgrade to PostgreSQL version 15. <=====<<< ??
Failure, exiting
[postgres@pf ~]$ /usr/bin/pg_upgrade -V
pg_upgrade (PostgreSQL) 15.1
[postgres@pf ~]$ /usr/local/pgsql/bin/pg_upgrade -V
pg_upgrade (PostgreSQL) 13.6
This is on Mageia Linux (mga9/cauldron) where the package manager prevents
the installation of both PG13 and PG15; so I have PG15 officially
installed and PG13 manually installed in /usr/local.
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.
Rather than mess with the config locations; I use symlinks to point to
the database which are currently:
/var/lib/pgadmin -> /mnt/work/var/lib/pgadmin/
/var/lib/pgsql -> /mnt/work/var/lib/pgsql/
Nothing I read implies the need to upgrade to 14.x first... Right?
Thanks
Pierre
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!
> regards, tom lane
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! >> >> Thanks >> Pierre >> >> >> >
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... :) >> Thanks! >> >>>> >>>> Thanks >>>> Pierre >>>> >>>> >>>> >>> >> >> >
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 > regards, tom lane > > >
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...
>
>> regards, tom lane
>>
>>
>>
>
>
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 datacorruption) 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. Ican see no advantage in pure ASCII when there is the potential for the real world to be contributing text. And there couldwell 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
>> 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
datacorruption) 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.
Ican see no advantage in pure ASCII when there is the potential for the real world to be contributing text. And there
couldwell 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
>
>
>
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.