1 follower Follow

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>"

Please sign in to leave a comment.