From: | Albrecht Dreß <albrecht(dot)dress(at)posteo(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | pg_upgradecluster fails if pg_hba.conf contains "@file" entries |
Date: | 2023-07-20 17:18:39 |
Message-ID: | 6N5HDIPJ.LI6WO5J4.SIKFKCPB@QGVJDDTK.R22H4AFZ.4UGMDGJB |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I run a PostgreSQL 14 database cluster on a Debian Bullseye (oldstable) box using the deb's from apt.postgresql.org. The configuration 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 new config 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 “(at)…” database and user entries. If such entries are detected and are relative to the config 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 usable patch.
Thanks,
Albrecht.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2023-07-20 23:26:06 | Re: fsync data directory after DB crash |
Previous Message | Ron | 2023-07-20 16:52:35 | Re: suggestion about time based partitioning and hibernate |