Re: pg_upgrade 13.6 to 15.1?

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
>>
>>
>>  
>
>