Tools - How to VACUUM FULL tables of a schema

This page proposes a way to VACUUM FULL all the tables of a schema. This SQL command can help to improve the performance while executing queries, for more detailed description refer to Postgres - VACUUM

1. Generate the Full vaccum queries:

   SELECT 'vacuum full ' 
          || table_name 
          || ';' 
   FROM   information_schema.tables 
   WHERE  table_schema = 'delta_local' 
   ORDER  BY table_schema, 
             table_name; 

   Here is an example of results vacumm_sample.txt

    

2. Create an sql file

3. Put the following in the created file:

   set search_path = :schema;

4. Copy past the result of the query generated in point 1 in the sql file

5. Remove all the guillemets from the file

6. Trace the beginning of the vacuum full tables

7. Open CMD

8. Go to the location <CAST_install_director>\CSSAdmin\3rdParties\x64

9. Run the following command line:

   psql --host <hostname> -p<port> -U <username> -v schema=<schema_name> -f "<sql file generated in step 1>" -d postgres -L <log file>

   where <hostname> is the host, <username> is the username, <schema_name> is the name of the schema <sql file generated in step 1> is the sql file generated in step 1, <log file> is log file that you can scpecified
   Exemple:

    

   psql --host localhost -p2280 -U operator -v schema=delta_local -f "C:\Users\myname\Documents\test.sql" -d postgres -L C:\Users\myname\Documents\test.log

10. In order to validate that the operation is successful, refer to the generated log file.