How to Maintain Database server

Several operations can be done on a database server such as backup and restore databases, changing Postures settings... 

We received some requests from our customers asking for automating Maintenance operations (DB housekeeping) ! for that we suggest you the following:

Step 1 - To be done only one time in order to generate the tables names (you will need to do that again if you add new schema on the server):

1.1 Run the following on the PG session :

SELECT 'vacuum (full, analyze) '
       || table_schema
       || '.'
       || table_name
 
       || ';'
FROM   information_schema.tables
where  table_schema not in ('pg_catalog','information_schema') 

1.2. Export the results with the following options: 

​

Step 2 - Run the following CLI each time you want to Maintain the DB

CAST_install_director>\CSSAdmin\3rdParties\x64\psql.exe -h <HOSTNAME> -p <PORT> -U <USER> -d postgres -f "<The file you generated from STEP 1>" -L "<LOG FILE>"