pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres upgrade does not mention requirement to have only 1 user
6+ messages / 5 participants
[nested] [flat]
* Postgres upgrade does not mention requirement to have only 1 user
@ 2021-01-26 06:57 PG Doc comments form <noreply@postgresql.org>
2021-01-26 07:45 ` Re: Postgres upgrade does not mention requirement to have only 1 user Charlie Hayes <ce.ceo@cybercoment.com>
2021-01-26 08:39 ` Re: Postgres upgrade does not mention requirement to have only 1 user Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 6+ messages in thread
From: PG Doc comments form @ 2021-01-26 06:57 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: ce.ceo@cybercoment.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/pgupgrade.html
Description:
During the pg_upgrade process, a check is run to make sure
pg_catalog.pg_roles has only a single non pg_* user, typically this is just
'postgres'. If this check fails, the error message is "Only the install user
can be defined in the new cluster."; This message implies the user executing
pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.
There's no indication in the documentation that the user table must be
flushed of all but the one user, nor does it prescribe a method to move
forward. The error message is not helpful; I only figured it out by finding
and reading the postgres source code.
I'll add that this upgrade process is quite rough. The instructions dictate
the install happen on the new version and to reference the old binaries;
this can be tricky to setup because simply installing the new version often
removes the old version. I use the docker image so the old version is not
present on the new docker image. After spending quite some time getting the
two versions in line and running the upgrade, I ran into this user issue. I
suspect I need to delete all users from the database before running the
upgrade, and obviously that must happen on the old version, which
essentially precludes a new version from 'adopting' an old version's data
files. This also encourages application developers to use the default
superuser for all db access which is a bad practice.
I'm fine with the upgrade needing to be explicit, but Ideally there would be
no need to load the old binaries or prep the user table.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres upgrade does not mention requirement to have only 1 user
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
@ 2021-01-26 07:45 ` Charlie Hayes <ce.ceo@cybercoment.com>
1 sibling, 0 replies; 6+ messages in thread
From: Charlie Hayes @ 2021-01-26 07:45 UTC (permalink / raw)
To: Charles William Hayes <ce.ceo@cybercoment.com>; pgsql-docs@lists.postgresql.org
I see now that the user check was verifying the target database and not the source. My upgrade script (which I used from 11 to 12) initialized the target database for upgrades as it did for seeding from scratch which included a user account. I removed that part of the script and the upgrade completed successfully.
I maintain that the documentation and error message could be improved and the process remains rough.
Thanks,
Charlie
> On Jan 26, 2021, at 12:57 AM, PG Doc comments form <noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/pgupgrade.html
> Description:
>
> During the pg_upgrade process, a check is run to make sure
> pg_catalog.pg_roles has only a single non pg_* user, typically this is just
> 'postgres'. If this check fails, the error message is "Only the install user
> can be defined in the new cluster."; This message implies the user executing
> pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.
>
> There's no indication in the documentation that the user table must be
> flushed of all but the one user, nor does it prescribe a method to move
> forward. The error message is not helpful; I only figured it out by finding
> and reading the postgres source code.
>
> I'll add that this upgrade process is quite rough. The instructions dictate
> the install happen on the new version and to reference the old binaries;
> this can be tricky to setup because simply installing the new version often
> removes the old version. I use the docker image so the old version is not
> present on the new docker image. After spending quite some time getting the
> two versions in line and running the upgrade, I ran into this user issue. I
> suspect I need to delete all users from the database before running the
> upgrade, and obviously that must happen on the old version, which
> essentially precludes a new version from 'adopting' an old version's data
> files. This also encourages application developers to use the default
> superuser for all db access which is a bad practice.
>
> I'm fine with the upgrade needing to be explicit, but Ideally there would be
> no need to load the old binaries or prep the user table.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres upgrade does not mention requirement to have only 1 user
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
@ 2021-01-26 08:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 09:27 ` Re: Postgres upgrade does not mention requirement to have only 1 user Daniel Gustafsson <daniel@yesql.se>
2021-01-26 17:16 ` Re: Postgres upgrade does not mention requirement to have only 1 user Charlie Hayes <ce.ceo@cybercoment.com>
1 sibling, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2021-01-26 08:39 UTC (permalink / raw)
To: ce.ceo@cybercoment.com; pgsql-docs@lists.postgresql.org
On Tue, 2021-01-26 at 06:57 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/13/pgupgrade.html
> Description:
>
> During the pg_upgrade process, a check is run to make sure
> pg_catalog.pg_roles has only a single non pg_* user, typically this is just
> 'postgres'. If this check fails, the error message is "Only the install user
> can be defined in the new cluster."; This message implies the user executing
> pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.
>
> There's no indication in the documentation that the user table must be
> flushed of all but the one user, nor does it prescribe a method to move
> forward. The error message is not helpful; I only figured it out by finding
> and reading the postgres source code.
The error message says "in the new cluster".
The documentation says, "Initialize the new PostgreSQL cluster" and that there
is no need to start the cluster. That is a pretty strong indication that it
is not necessary to create any objects there.
But perhaps it would not harm to be more explicit and add something like
that:
"Do not create and users, tablespaces or other objects on the new cluster."
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres upgrade does not mention requirement to have only 1 user
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
2021-01-26 08:39 ` Re: Postgres upgrade does not mention requirement to have only 1 user Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-01-26 09:27 ` Daniel Gustafsson <daniel@yesql.se>
2021-01-26 17:56 ` Re: Postgres upgrade does not mention requirement to have only 1 user Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 6+ messages in thread
From: Daniel Gustafsson @ 2021-01-26 09:27 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: ce.ceo@cybercoment.com; pgsql-docs@lists.postgresql.org
> On 26 Jan 2021, at 09:39, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> But perhaps it would not harm to be more explicit and add something like
> that:
>
> "Do not create and users, tablespaces or other objects on the new cluster."
+1, that makes sense (with a s/and/any/).
--
Daniel Gustafsson https://vmware.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres upgrade does not mention requirement to have only 1 user
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
2021-01-26 08:39 ` Re: Postgres upgrade does not mention requirement to have only 1 user Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 09:27 ` Re: Postgres upgrade does not mention requirement to have only 1 user Daniel Gustafsson <daniel@yesql.se>
@ 2021-01-26 17:56 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2021-01-26 17:56 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; ce.ceo@cybercoment.com; pgsql-docs@lists.postgresql.org
On Tue, Jan 26, 2021 at 10:27:23AM +0100, Daniel Gustafsson wrote:
> > On 26 Jan 2021, at 09:39, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> > But perhaps it would not harm to be more explicit and add something like
> > that:
> >
> > "Do not create and users, tablespaces or other objects on the new cluster."
>
> +1, that makes sense (with a s/and/any/).
I do not see this as an improvement.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres upgrade does not mention requirement to have only 1 user
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
2021-01-26 08:39 ` Re: Postgres upgrade does not mention requirement to have only 1 user Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-01-26 17:16 ` Charlie Hayes <ce.ceo@cybercoment.com>
1 sibling, 0 replies; 6+ messages in thread
From: Charlie Hayes @ 2021-01-26 17:16 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
It may say “in the new cluster” but it was not clear to me if that was “in the new cluster (before the upgrade starts)” or “in the new cluster (after the upgrade completes)”
In hind sight everything makes sense, but in the present it was very confusing.
-Charlie
> On Jan 26, 2021, at 2:39 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Tue, 2021-01-26 at 06:57 +0000, PG Doc comments form wrote:
>> Page: https://www.postgresql.org/docs/13/pgupgrade.html
>> Description:
>>
>> During the pg_upgrade process, a check is run to make sure
>> pg_catalog.pg_roles has only a single non pg_* user, typically this is just
>> 'postgres'. If this check fails, the error message is "Only the install user
>> can be defined in the new cluster."; This message implies the user executing
>> pg_upgrade must match the the rolsuper in pg_catalog.pg_roles.
>>
>> There's no indication in the documentation that the user table must be
>> flushed of all but the one user, nor does it prescribe a method to move
>> forward. The error message is not helpful; I only figured it out by finding
>> and reading the postgres source code.
>
> The error message says "in the new cluster".
>
> The documentation says, "Initialize the new PostgreSQL cluster" and that there
> is no need to start the cluster. That is a pretty strong indication that it
> is not necessary to create any objects there.
>
> But perhaps it would not harm to be more explicit and add something like
> that:
>
> "Do not create and users, tablespaces or other objects on the new cluster."
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-01-26 17:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-26 06:57 Postgres upgrade does not mention requirement to have only 1 user PG Doc comments form <noreply@postgresql.org>
2021-01-26 07:45 ` Charlie Hayes <ce.ceo@cybercoment.com>
2021-01-26 08:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 09:27 ` Daniel Gustafsson <daniel@yesql.se>
2021-01-26 17:56 ` Bruce Momjian <bruce@momjian.us>
2021-01-26 17:16 ` Charlie Hayes <ce.ceo@cybercoment.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