Feedback-ID: i76984098:Fastmail
Message-ID: <61c3bb14-504c-46d6-a6e3-da6862e772cd@aklaver.com>
Date: Mon, 14 Sep 2026 13:58:33 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Upgrading 6 versions between 2 systems
To: Ron Johnson <ronljohnsonjr@gmail.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <deee7dc-8e5e-cbba-aa3-2141e5d15c6a@appl-ecosys.com>
 <CANzqJaBQYcLc=aThm52En4EY+sPvQy6gTLx+ekiA4wuJjMJFKw@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CANzqJaBQYcLc=aThm52En4EY+sPvQy6gTLx+ekiA4wuJjMJFKw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/61c3bb14-504c-46d6-a6e3-da6862e772cd%40aklaver.com>
Precedence: bulk

On 9/14/26 1:43 PM, Ron Johnson wrote:
> On Mon, Sep 14, 2026 at 4:26 PM Rich Shepard <rshepard@appl-ecosys.com 
> <mailto:rshepard@appl-ecosys.com>> wrote:
> 
>     When I've upgraded postgres in the past on my server/workstation it's
>     usually been from one major version to the next. What I want to do
>     now is to
>     move from 12.7 (on an older OS linux version) to 18.6 (on the current
>     version.)
> 
>     I know that data formats can change with postgres version increases
>     so my
>     question is: can I use dumpall in the 12.7 version and move that to
>     the host
>     running 18.6 since the output is a text .sql file?
> 
> 
> Yes.  Note, though, that a single .sql file is the /slow/ way to export/ 
> import a database, especially if it's of any size.

It's not and the below is overkill for Rich's purpose.

> 
> Multithreaded export import is faster.  Something like this is what I'd do:
> 
> Current system:
> cd /path/to/backups
> pg_dumpall --globals-only > globals_127.sql
> Zlvl="--compress=zstd"
> Threads=4 # or whatever
> for db in x, y, z; do
> pg_dump -p5432 -Fd $Zlvl -v --jobs=$Threads -f $db $db
> done
> 
> New system:
> cd /path/to/backups
> psql -af globals_127.sql
> Zlvl="--compress=zstd"
> Threads=4 # or whatever
> for db in x, y, z; do
> ${pg18}/pg_restore -p5433 -v --jobs=$Threads --clean --create -Fd -d 
> postgres $db
> done
> 
> 
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!


-- 
Adrian Klaver
adrian.klaver@aklaver.com