Re: pg_dump: VACUUM and REINDEXING

On 5/6/22 21:35, Hasan Marzooq wrote:
> Hello!
>
> I've some questions around Backup & Restore.
>
> 1: Is it necessary to perform a VACUUM and REINDEXING operation after
> restoring the dump from Postgres 9.6 to Postgres 13? The dump size could
> be 1/2 TB to 1 TB.

Perform VACUUM after there have been many updates and deletes. There have
been zero updates and deleted after pg_restore; therefore, *no need to vacuum*.

pg_restore loads all tables and then builds all indices.  Thus, *no need to
reindex*.

>
> 2: Also, are there any other operations that are recommended to perform
> after pg_restore?

ANALYZE all tables.

https://www.postgresql.org/docs/13/app-vacuumdb.html

vacuumdb --dbname=whatever --jobs=`nproc` --analyze-only

>
> 3: What is the minimum required disk space if taking a dump on the same
> machine where the source database exists? Is it the "size of the current
> data folder x 2"?

Probably much less, but maybe (if, for example, you store lots of images
(JPEG, TIFF, PDF, etc) in bytea columns.

Whatever you do, make sure to run pg_dump with these options:
--format=directory --jobs=`nproc`
https://www.postgresql.org/docs/13/app-pgdump.html

--
Angular momentum makes the world go 'round.