Re: Database upgrade Pg11 to Pg12 issues

On Wed, Feb 3, 2021 at 11:25:11PM +0000, Simon Windsor wrote:
> Hi
>
> I have upgraded many Pg databases from 9 to 10 to 11 using the Ubuntu
> pg_upgradecluster command (wrapper to pg_update) without issues, however today
> I upgraded a DB from 11 to 12.
>
> Using the syntax
>
> pg_upgradecluser -k -m upgrade 11 main
>
> using the latest 12.5 binaries all appeared to work well, and for a 330G DB
> this took 2 minutes. On starting the DB using
>
> systemctl start postgresql(at)12(dot)service
>
> the DB started, but the screen showed the default directory was /var/lib/
> postgresql/11/main. Checking the open database with
>
> show data_directory; gave /var/lib/postgresql/12/main
>
> Then using lsof | grep '11/main' gave no files, but lsof | grep '12/main' gave
> over 30K files, Checking the directory sizes suggests that 11/main is 300G and
> 12/main is 40G

This doesn't make sense to me. Since we hard-linked, why would 12 be so
much smaller? If it was symlinks, I could imaging that, but it doesn't
use symlinks, just hard links, so it should be similar. Please look at
the size of main/base on both, since that is where your data is. Maybe
11 just has a lot of old WAL that isn't copied.

> So all appears OK, with files hard linked between 11/main and 12/main, and I
> assume the issue relates to the PGDATA/postgresql.auto.conf file being just
> copied from the original 11/main with data_directory being set to /var/lib/
> postgresql/11/main.
>
> If I where to run pg_dropcluster 11 main to remove the old database and conf
> files, will this destroy my running Pg12 database with hard linked files in 11/
> main and 12/main? In theory it shouldn't and files from 11/main and 12/main
> will all be under 12/main, I just have doubts.
>
> Is there an easy way to tidy this up?

Yes, if the sizes made sense, removing 11 would be fine, but right now,
it doesn't sound right.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee