Upgrading 6 versions between 2 systems
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
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!
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
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
doneNew 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
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
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
doneNew 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
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
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
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
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
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
doneNew 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
doneGood idea.
Thanks,
Rich
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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.
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