Good day.
Take a look at https://doc.ipesoft.com/label/D2DOCEN/trezorove_databazy
At the end of that page there is a text:
An example of a batch file used to perform a depository database maintenance and export as well as export of table trezors located in archive database MyApp.Archiv . The batch file requires as a parameter the name of depository database, which can be provided by setting the parameter TrezorPostCompressPar to value #TREZOR# .
And there are 4 things done (before the export itself):
- permitting write access to the depository database:
rem permit write access to depository database and cluster the data table
echo alter database “%1” set default_transaction_read_only=false | psql -S -U postgres MyApp.Archiv >> %MyDir%%1.log
- configuring clustering of data based on an index:
echo alter table data on ix_data_rc | psql -S -U postgres% 1 >>% MyDir %% 1.log
- clustering of data (it may take from a few minutes to several hours, according to DB size and HDD speed):
echo cluster data | psql -S -U postgres% 1 >>% MyDir %% 1.log
- setting read-only access to the database:
rem set access to storage database back to read only
echo alter database “% 2” set default_transaction_read_only = true | psql -S -U postgres MyApp.Archiv >>% MyDir %% 1.log
Try it out (first manually on some depository database). It would be a good idea to prevent the archive’s access to the depository database during the maintenance (because it will either fail or block the maintenance job), so DISMOUNT the depository database first.
However, when a database is dismounted, the access to the database is forbidden - see above in the same document:
update pg_database set datallowconn = false where datname = ‘APLIKACIA_TREZOR_#ID#’
So you have to manually perform
update pg_database set datallowconn = true where datname = ‘APPLICATION_TREZOR_#ID#’
to be able to connect to the database.
Then you can automate it by putting it into a batch file that is run as the TrezorPostCompressCmd. And set the TrezorCompressOffline to 2 - according to the manual:
Value 2 means that D2000 Archiv will not access the depository database while TrezorPostCompressCmd is running, so that this command may perform various maintenance tasks which could otherwise block the archiv.
This is how we have it configured in a few places. So when the archive stops using the depository database, it will mark it inaccessible (but it won’t forbid the access to it), it will run the TrezorPostCompressCmd command which will perform the maintenance in the depository database. When the maintenance is over, the archive will start using the depository again.
Last but not least, if you have any data with a short archiving depth (e.g. 3 days) and you rely on the fact that they are in the depository database, then during the maintenance, you have only the last 3 days available (from the archive database), as the depository database from the last month is not available and, only older depositories are… => it is not a good practice to rely on such a reading from the depository database.
By the way, depository databases should serve for occasional reading, therefore I suggest that you extend archiving depth so that at least 90% of the necessary data is in the archive database.
