pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrading 6 versions between 2 systems
13+ messages / 5 participants
[nested] [flat]
* Upgrading 6 versions between 2 systems
@ 2026-09-14 20:26 Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 2 replies; 13+ messages in thread
From: Rich Shepard @ 2026-09-14 20:26 UTC (permalink / raw)
To: pgsql-general
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?
TIA,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 20:43 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 3 replies; 13+ messages in thread
From: Ron Johnson @ 2026-09-14 20:43 UTC (permalink / raw)
To: pgsql-general
On Mon, Sep 14, 2026 at 4:26 PM Rich Shepard <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.
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!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 20:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2026-09-14 20:43 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
Rich Shepard <rshepard@appl-ecosys.com> writes:
> 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?
You could, but better practice is to use pg_dump[all] from the newer
version to take a dump from the old server. The idea behind that
recommendation is that the newer pg_dump may contain bug fixes that
aren't in the 12.7 version (especially noting that 12.7 is many
minor releases short of v12's EOL).
Keep in mind also that 6 years is a lot of time for things to change.
I expect your dump will probably load okay, but test your application
for compatibility.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 20:58 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2026-09-14 20:58 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 21:39 Rich Shepard <rshepard@appl-ecosys.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Rich Shepard @ 2026-09-14 21:39 UTC (permalink / raw)
To: pgsql-general
On Mon, 14 Sep 2026, Tom Lane wrote:
> You could, but better practice is to use pg_dump[all] from the newer
> version to take a dump from the old server.
Tom,
I know that's the way it's normally done, but the two versions are on
different LAN hosts and each runs a different Slackware version (14.2 with
the current data) and verson 15.0 on a different host.
> Keep in mind also that 6 years is a lot of time for things to change.
> I expect your dump will probably load okay, but test your application
> for compatibility.
My databases are small, most for specific projects. I'll dump each database
separately rather than the whole cluster. That way I can see if a problem
shows up.
Thanks,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 21:44 Rich Shepard <rshepard@appl-ecosys.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 2 replies; 13+ messages in thread
From: Rich Shepard @ 2026-09-14 21:44 UTC (permalink / raw)
To: pgsql-general
On Mon, 14 Sep 2026, Ron Johnson wrote:
> Yes. Note, though, that a single .sql file is the *slow* way to
> export/import a database, especially if it's of any size.
Ron,
My databases are small and I'll transfer them one-at-a-time rather than the
whole custer itself.
> 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
Good idea.
Thanks,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 21:55 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2026-09-14 21:55 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; pgsql-general
On 9/14/26 2:39 PM, Rich Shepard wrote:
> On Mon, 14 Sep 2026, Tom Lane wrote:
>
>> You could, but better practice is to use pg_dump[all] from the newer
>> version to take a dump from the old server.
>
> Tom,
>
> I know that's the way it's normally done, but the two versions are on
> different LAN hosts and each runs a different Slackware version (14.2 with
> the current data) and verson 15.0 on a different host.
Since you are transferring the data as text that should not be a
problem. Just point the newer version of pg_dumpall, I assume the one on
the Slackware 15 host, at the older version Postgres server.
>
>> Keep in mind also that 6 years is a lot of time for things to change.
>> I expect your dump will probably load okay, but test your application
>> for compatibility.
>
> My databases are small, most for specific projects. I'll dump each database
> separately rather than the whole cluster. That way I can see if a problem
> shows up.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 21:55 Rich Shepard <rshepard@appl-ecosys.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Rich Shepard @ 2026-09-14 21:55 UTC (permalink / raw)
To: pgsql-general
On Mon, 14 Sep 2026, Ron Johnson wrote:
> Yes. Note, though, that a single .sql file is the *slow* way to
> export/import a database, especially if it's of any size.
Ron,
As Adrian pointed out size is not an issue. /var/lib/pgsql/12/data holds
432M.
Regards,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 21:58 Rich Shepard <rshepard@appl-ecosys.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 13+ messages in thread
From: Rich Shepard @ 2026-09-14 21:58 UTC (permalink / raw)
To: pgsql-general
On Mon, 14 Sep 2026, Adrian Klaver wrote:
> Since you are transferring the data as text that should not be a problem.
> Just point the newer version of pg_dumpall, I assume the one on the
> Slackware 15 host, at the older version Postgres server.
Adrian,
Hadn't thought I could do that. But, why not if I use the
servername:/directory?
Thanks,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-14 22:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Rich Shepard <rshepard@appl-ecosys.com>
0 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2026-09-14 22:23 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; pgsql-general
On 9/14/26 2:58 PM, Rich Shepard wrote:
> On Mon, 14 Sep 2026, Adrian Klaver wrote:
>
>> Since you are transferring the data as text that should not be a problem.
>> Just point the newer version of pg_dumpall, I assume the one on the
>> Slackware 15 host, at the older version Postgres server.
>
> Adrian,
>
> Hadn't thought I could do that. But, why not if I use the
> servername:/directory?
Not sure what servername:/directory means?
As example, assuming Postgres 12.7 is running on machine at address
192.168.0.1.
pg_dumpall -h 192.168.0.1 -U some_user -f db_cluster.sql
This would be run from the second machine that has Postgres 18.6.
>
> Thanks,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-15 02:33 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 0 replies; 13+ messages in thread
From: Ron Johnson @ 2026-09-15 02:33 UTC (permalink / raw)
To: pgsql-general
If you're upgrading to a new PC instead of upgrading Slackware and PG on an
existing PC, then something like this will work from the new system:
pg_dumpall -g --host=old_pc | psql -d postgres
for db in x, y, z; do
pg_dump --host=old_pc -Fp $db | psql $db
done
(I might be missing some required options, since I don't do this
very often.)
On Mon, Sep 14, 2026 at 5:44 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:
> On Mon, 14 Sep 2026, Ron Johnson wrote:
>
> > Yes. Note, though, that a single .sql file is the *slow* way to
> > export/import a database, especially if it's of any size.
>
> Ron,
>
> My databases are small and I'll transfer them one-at-a-time rather than the
> whole custer itself.
>
> > 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
>
> Good idea.
>
> Thanks,
>
> Rich
>
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-15 08:02 Alban Hertroys <haramrae@gmail.com>
parent: Rich Shepard <rshepard@appl-ecosys.com>
1 sibling, 1 reply; 13+ messages in thread
From: Alban Hertroys @ 2026-09-15 08:02 UTC (permalink / raw)
To: Rich Shepard <rshepard@appl-ecosys.com>; +Cc: pgsql-general
> On 14 Sep 2026, at 23:44, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> On Mon, 14 Sep 2026, Ron Johnson wrote:
>
>> Yes. Note, though, that a single .sql file is the *slow* way to
>> export/import a database, especially if it's of any size.
>
> Ron,
>
> My databases are small and I'll transfer them one-at-a-time rather than the
> whole custer itself.
Rich,
Just a little heads-up:
If you’re dumping one database at a time, that doesn’t include any of the database roles or users you may have added. Roles (and users) are stored with the cluster, not per database.
That’s where the below mentioned `pg_dumpall --globals-only` feature comes into play. You don’t need the parallel stuff, but the globals bit could be important.
With the rest cut out, that leaves:
>
>> Current system:
>> cd /path/to/backups
>> pg_dumpall --globals-only > globals_127.sql
...
>> New system:
>> cd /path/to/backups
>> psql -af globals_127.sql
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Upgrading 6 versions between 2 systems
@ 2026-09-15 12:01 Rich Shepard <rshepard@appl-ecosys.com>
parent: Alban Hertroys <haramrae@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Rich Shepard @ 2026-09-15 12:01 UTC (permalink / raw)
To: pgsql-general
On Tue, 15 Sep 2026, Alban Hertroys wrote:
> If you’re dumping one database at a time, that doesn’t include any of the
> database roles or users you may have added. Roles (and users) are stored
> with the cluster, not per database.
>
> That’s where the below mentioned `pg_dumpall --globals-only` feature comes
> into play. You don’t need the parallel stuff, but the globals bit could be
> important.
>
> With the rest cut out, that leaves:
>
>>
>>> Current system:
>>> cd /path/to/backups
>>> pg_dumpall --globals-only > globals_127.sql
>
> ...
>
>>> New system:
>>> cd /path/to/backups
>>> psql -af globals_127.sql
Alban,
I was not aware of this because in the past all version upgrades were on the
same host.
A valuable lesson. Thanks very much.
Regards,
Rich
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-09-15 12:01 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-14 20:26 Upgrading 6 versions between 2 systems Rich Shepard <rshepard@appl-ecosys.com>
2026-09-14 20:43 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-14 20:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-09-14 21:44 ` Rich Shepard <rshepard@appl-ecosys.com>
2026-09-15 02:33 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-15 08:02 ` Alban Hertroys <haramrae@gmail.com>
2026-09-15 12:01 ` Rich Shepard <rshepard@appl-ecosys.com>
2026-09-14 21:55 ` Rich Shepard <rshepard@appl-ecosys.com>
2026-09-14 20:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-14 21:39 ` Rich Shepard <rshepard@appl-ecosys.com>
2026-09-14 21:55 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-09-14 21:58 ` Rich Shepard <rshepard@appl-ecosys.com>
2026-09-14 22:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox