Re: Upgrading 6 versions between 2 systems

On 9/14/26 1:43 PM, Ron Johnson wrote:
> On Mon, Sep 14, 2026 at 4:26 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com
> <mailto:rshepard(at)appl-ecosys(dot)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(dot)klaver(at)aklaver(dot)com