Thread: pg_upgradecluster fails if pg_hba.conf contains "@file" entries

pg_upgradecluster fails if pg_hba.conf contains "@file" entries

From
Albrecht Dreß
Date:
Hi all,

I run a PostgreSQL 14 database cluster on a Debian Bullseye (oldstable) box using the deb's from apt.postgresql.org.
Theconfiguration contains “indirect” entries in /etc/postgresql/14/main/pg_hba.conf, looking like e.g. 

<snip>
hostssl @dblist.txt      all             172.16.96.0/24         ident
</snip>

Running the command

<snip>
pg_upgradecluster -m upgrade --link -j 16 14 main
</snip>

to upgrade to ver. 15 failed with without naming a specific reason, but with the information

<snip>
Consult the last few lines of
"/var/lib/postgresql/15/main/pg_upgrade_output.d/20230720T110544.025/log/pg_upgrade_server.log"for 
the probable cause of the failure.
</snip>

However, as one of the last steps, pg_upgradecluster in this case completely erases inter alia the folder
/var/lib/postgresql/15,including the log files, which obviously isn't very helpful.  Only after commenting out the
“pg_dropcluster”statement in the script (3rd line of the snippet) 

<snip>
if ($?) {
     print STDERR "Error during cluster dumping, removing new cluster\n";
     system 'pg_dropcluster', '--stop', $newversion, $newcluster;
</snip>

I could identify the cause of the problem.  The solution was to add copying the “indirect” files from the old to the
newconfig folder to the pg_upgradecluster script, after which it ran without any issue as expected. 

IMHO, the pg_upgradecluster script could/should be improved:
1. If the upgrade fails, it is fine to remove the new cluster, but *all* log files should be copied to a different
location(e.g. /tmp) before so the user has a chance to find out what actually went wrong. 
2. pg_hba.conf should be scanned for “@…” database and user entries.  If such entries are detected and are relative to
theconfig folder, the respective files should be copied automatically. 

Unfortunately, my knowledge of perl is /very/ limited, and my hacks were specific for my setup, so I cannot provide a
usablepatch. 

Thanks,
Albrecht.
Attachment