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@12.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@momjian.us> https://momjian.us
EDB https://enterprisedb.com

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

(replying to the entire list instead of Bruce only this time...)

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.

It depends on how the directory sizes were measured. "du" won't count the
same file more than once, so if space was measured using one of the
following commands:

du -hs /var/lib/postgresql/
du -hs /var/lib/postgresql/11/main /var/lib/postgresql/12/main

Then you would see a large value for the 11 directory and then the 12
directory would only show the files that weren't already counted when
measuring the 11 directory. Running du on each version's directory
separately might give results that are more in line with expectations.

Craig

On Wed, Feb 3, 2021 at 10:07:03PM -0500, Craig McIlwee wrote:

(replying to the entire list instead of Bruce only this time...)

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.

It depends on how the directory sizes were measured. "du" won't count the same
file more than once, so if space�was measured using one of the following
commands:

du -hs /var/lib/postgresql/
du -hs�/var/lib/postgresql/11/main /var/lib/postgresql/12/main�

Then you would see a large value for the 11 directory and then the 12 directory
would only show the files that weren't already counted when measuring the 11
directory.� Running du on each version's directory separately might give
results that are more in line with expectations.

Yes, I was assuming the du commands were run separately on each
directory.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

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