...
PG_CreateTrezor - SQL command for creating a depository database. Default value isKotva pg_createtrezor pg_createtrezor
CREATE DATABASE "#TREZOR#" WITH ENCODING='UTF8' OWNER=dba TABLESPACE="D2000" TEMPLATE=template0
where #TREZOR# is a name of depository database (defined by configuration parameter PG_TrezorName0 resp. for depository database segments 1..N by parameter PG_TrezorName). By default existence of tablespace named D2000 and template database template0 is required in PostgreSQL database server.
PG_TrezorFileMulti - parameter is active if also parameter PG_TrezorFilePath is specified. Parameter defines a multiplicator for the parameter CommitCount . Default value of this parameter is 10.Kotva pg_trezorfilemulti pg_trezorfilemulti
PG_TrezorFilePath - parameter can be used for PostgreSQL 9.5 and above. It activates writing to depository databases via files, which is 2 to 3-times faster than the standard batch insert via ODBC interface. After PG_TrezorFileMulti * CommitCount values are accumulated, they are stored into a file named archiv_<TrezorId>_<SegmentId>.txt (e.g. archiv_4_1.txt) in directory PG_TrezorFilePath and an UPSERT into a depository database is performed, using a foreign table mapped onto this file (using PostgreSQL extension file_fdw) as a source of data.Kotva pg_trezorfilepath pg_trezorfilepath
The directory PG_TrezorFilePath must be available both for reading and writing to archive as well as to PostgreSQL database, therefore this parameter can be used if both archiv and database are on the same computer.
PG_TrezorName0 - mask for a name of created depository databases. If depository database segments are enabled, this mask will be used for segment 0. Default value is appname_TREZOR_#ID#, where appname is the application's name and #ID# is replaced by the depository database number.Kotva pg_trezorname0 pg_trezorname0
E.g. for application named Test depository databases Test_TREZOR_1, Test_TREZOR_2, Test_TREZOR_3 etc will be created.
Specified mask must contain text #ID#.
Note: If default value not acceptable, custom value must be configured before enabling depository databases. Should value of this parameter be changed after some depository databases have been created, it is necessary to rename all existing depository databases according to newly specified mask.
PG_TrezorName - mask for a name of created databases - depository database segments 1..N.Kotva pg_trezorname pg_trezorname
Parameter is used only when TrezorCountSegments > 0. Default value is je appname_TREZOR_#ID#_#SEG#, where appname is the application's name, #ID# is replaced by the depository database number and #SEG# is replaced by the segment number.
E.g. for application named Test with number of segments equal to 1, databases Test_TREZOR_1, Test_TREZOR_1_1, Test_TREZOR_2, Test_TREZOR_2_2, Test_TREZOR_3, Test_TREZOR_3_1 etc will be created.
Specified mask must contain text #ID# and #SEG#.
Note: If default value not acceptable, custom value must be configured before enabling depository databases. Should value of this parameter be changed after some depository databases have been created, it is necessary to rename all existing depository databases according to newly specified mask.
TrezorPeriod - period of the depository creation. It is specified in format NX, where N is an integer number and X defines a time unit.Kotva trezorperiodpg trezorperiodpg
Permitted time units are: H - hour, D - day, W - week, M - month.
Note: Setting the parameter either to empty value or to 0H disables the functionality of depository databases.
TrezorCompressOffline - value 1 (default) means that compressed depository database will be put into offline mode. Value 0 means that depository database remains in read-only mode and data will be available for reading. 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.Kotva trezorcompressofflinepg trezorcompressofflinepg
TrezorCompressTime - time for releasing depository database. The database is available for writing delayed data during this period. TrezorCompressTime must be less than TrezorPeriod.Kotva trezorcompresstimepg trezorcompresstimepg
TrezorCountSegments - number of depository database segments (depository databases), that are being created in parallel. Default value is 0 (only segment 0 is being created), maximum value is 99 (segments 0 to 99 are being created). The parameter is of DWORD type !!!.Kotva trezorcountsegmentspg trezorcountsegmentspg
TrezorPostCompressCmd - name of a program to be run after releasing depository database. This program can for example move and compress a disconnected depository database or create a dump of read-only depository database via PostgreSql utility pg_dump.Kotva trezorpostcompresscmdpg trezorpostcompresscmdpg
TrezorPostCompressPar - optional parameter, used for the program specified by the parameter TrezorPostCompressCmd. It can contain optional text and predefined macros to be replaced before running the program:Kotva trezorpostcompressparpg trezorpostcompressparpg - #ID# - is replaced by depository database Id (e.g. 5).
- #OID# - is replaced by OID (object identifier) of depository database (OID is related to a name of directory containing respective database inside PostgreSQL tablespace).
- #OID0#, #OID1#, #OID2# etc - is replaced by OIDs (object identifiers) of databases representing respective depository database segment 0, 1, 2 etc.
- #TREZOR# - is replaced by the depository database name (e.g. Test_TREZOR_5).
- #TREZOR0#, #TREZOR1#, #TREZOR2# etc - is replaced by the database name representing respective depository database segment.
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#.
rem Target directory for exports
set MyDir=D:\Trezors_export\
rem Set PGPASSWORD to password assigned to postgres user during installation
set PGPASSWORD=MyPostgresPassword
set PATH=%PATH%;c:\Program Files\PostgreSQL\9.5\bin
rem export of table trezors from the archive database
pg_dump -Fc -U postgres -f "%MyDir%\MyApp_arc_trezors.dmp" --table \"trezors\" MyApp.Archiv >> %MyDir%\%1.log
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
echo alter table data cluster on ix_data_rc | psql -S -U postgres %1 >> %MyDir%\%1.log
echo cluster data | psql -S -U postgres %1 >> %MyDir%\%1.log
rem set access to depository database back to read only
echo alter database "%1" set default_transaction_read_only=true | psql -S -U postgres MyApp.Archiv >> %MyDir%\%1.log
pg_dump -Fc -U postgres -f "%MyDir%\%1.dmp" %1 >> %MyDir%\%1.logExample of an equivalent batch file for Linux:
#!/usr/bin/env bash
MyDir=/trezorbackup
MyArc=mes_tpd_archive_self
MyLog=$MyDir/$1.log
#path to pg_dump, psql etc
PATH=/usr/pgsql-11/bin:$PATH
#password for dba user
export PGPASSWORD=***
#export of table trezors from the archive database
pg_dump -Fc -U dba -f "$MyDir/arc_trezors.dmp" --table \"trezors\" $MyArc >> $MyLog
#permit write access to depository database and cluster the data table
echo alter database \"$1\" set default_transaction_read_only=false | psql -S -U dba $MyArc >> $MyLog
echo alter table data cluster on ix_data_rc | psql -S -U dba "$1" >> $MyLog
echo cluster data | psql -S -U dba "$1" >> $MyLog
#set access to depository database back to read only
echo alter database \"$1\" set default_transaction_read_only=true | psql -S -U dba $MyArc >> $MyLog
#execute dump of depository database
pg_dump -Fc -U dba -f "$MyDir/$1.dmp" "$1" >> $MyLog