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