From: Rich Shepard <rshepard@appl-ecosys.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Upgrading 6 versions between 2 systems
Date: Tue, 15 Sep 2026 05:01:29 -0700 (PDT)
Message-ID: <abc0f870-c97b-cbcc-d8a3-7d6b3de620c6@appl-ecosys.com> (raw)
In-Reply-To: <0392D5EB-21FF-4BE4-9FAC-A4F7546C88D3@gmail.com>
References: <deee7dc-8e5e-cbba-aa3-2141e5d15c6a@appl-ecosys.com>
<CANzqJaBQYcLc=aThm52En4EY+sPvQy6gTLx+ekiA4wuJjMJFKw@mail.gmail.com>
<8598df-508d-fa22-5866-35423256b1b3@appl-ecosys.com>
<0392D5EB-21FF-4BE4-9FAC-A4F7546C88D3@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: rshepard@appl-ecosys.com
Subject: Re: Upgrading 6 versions between 2 systems
In-Reply-To: <abc0f870-c97b-cbcc-d8a3-7d6b3de620c6@appl-ecosys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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