Re: Help in vetting outcome of "vacuumdb --analyze-in-stages" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 14.X

On 2/16/25 08:27, Y_Bharani_mbsv wrote:
> Adrian
> TQ for the instant reply.
> post DB migration to Ver 14.X (successfully) and Post executing the
> "vacuumdb --analyze-in-stages", i noticed "read me option" and the
> caveat on it.

Did you do:

vacuumdb --analyze-in-stages

or

vacuumdb --all --analyze-in-stages

?

>
> Later, I too did
> a) vacuum(full,verbose,skip_locked) ... each table wise b) analyze
> (verbose,skip_locked) .. each table wise
> against all the DB's

VACUUM FULL has not purpose at this point as FULL recycles unneeded
tuples from DELETEs and UPDATEs and at this stage there are none of
those. Also going table by table is not necessary when you can
vacuuum/analyze an entire database, which is what you want, with one
command.

>
> Any guidance on how to overcome the issue.

There is no issue to overcome.

Per:

https://www.postgresql.org/docs/current/pgupgrade.html

17. Statistics

"Because optimizer statistics are not transferred by pg_upgrade, you
will be instructed to run a command to regenerate that information at
the end of the upgrade. You might need to set connection parameters to
match your new cluster."

Running vacuumdb --all --analyze-in-stages will create the statistics
you need it just does it in steps(stages) vs doing vacuumdb --all
--analyze-only which does it in a single pass. Unless you are planning
to run some large complicated queries immediately upon completion of the
upgrade you will not notice the difference.

> Any suggestion ?
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com