Re: [PATCH] improve the pg_upgrade error message

Thanks Jeevan for working on this.
Overall patch looks good to me.

+ pg_fatal("All non-template0 databases must allow connections, i.e.
their\n"
+ "pg_database.datallowconn must be true. Your installation contains\n"
+ "non-template0 databases with their pg_database.datallowconn set to\n"
+ "false. Consider allowing connection for all non-template0 databases\n"
+ "using:\n"
+ " UPDATE pg_catalog.pg_database SET datallowconn='true' WHERE datname
NOT LIKE 'template0';\n"
+ "A list of databases with the problem is given in the file:\n"
+ " %s\n\n", output_path);

Instead of giving suggestion about updating the pg_database catalog, can we
give "ALTER DATABASE <datname> ALLOW_CONNECTIONS true;" command?
Also, it would be good if we give 2 spaces after full stop in an error
message.

On Tue, Jul 13, 2021 at 6:57 PM Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com>
wrote:

>
> The admin might fix DB123, restart their upgrade procedure, spend 5 or 15
>> minutes with that, only to have it then fail on DB1234.
>>
>
> Agree with this observation.
>
> Here is a patch that writes the list of all the databases other than
> template0
> that are having their pg_database.datallowconn to false in a file. Similar
> approach is seen in other functions like check_for_data_types_usage(),
> check_for_data_types_usage() etc. Thanks Suraj Kharage for the offline
> suggestion.
>
> PFA patch.
>
> For experiment, here is how it turns out after the fix.
>
> postgres=# update pg_database set datallowconn='false' where datname in
> ('mydb', 'mydb1', 'mydb2');
> UPDATE 3
>
> $ pg_upgrade -d /tmp/v96/data -D /tmp/v13/data -b $HOME/v96/install/bin -B
> $HOME/v13/install/bin
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
> Checking database user is the install user ok
> Checking database connection settings fatal
>
> All non-template0 databases must allow connections, i.e. their
> pg_database.datallowconn must be true. Your installation contains
> non-template0 databases with their pg_database.datallowconn set to
> false. Consider allowing connection for all non-template0 databases
> using:
> UPDATE pg_catalog.pg_database SET datallowconn='true' WHERE datname
> NOT LIKE 'template0';
> A list of databases with the problem is given in the file:
> databases_with_datallowconn_false.txt
>
> Failure, exiting
>
> $ cat databases_with_datallowconn_false.txt
> mydb
> mydb1
> mydb2
>
>
> Regards,
> Jeevan Ladhe
>

--
--

Thanks & Regards,
Suraj kharage,

edbpostgres.com