Convert PostgreSQL® database Indices from BRIN to BTREE
CAUTION: We highly recommended to do a full backup of the
database before performing the following steps to avoid possible data loss.
To convert existing PostgreSQL® database indices from BRIN to BTREE for segments, use the
script
wincc_oa_install_path/data/NGA/PostgreSQL/sql/brin_to_btree_conversion.sql
Note: Note that the path to the script must not contain
backslashes (\), but only slashes (/). Also note that if the absolute path to the
script contains whitespaces, you must wrap the path into single apostrophes (') and
not quotation marks (").
- We highly recommend reading the readme file wincc_oa_install_path/data/NGA/PostgreSQL/sql/readme.md, which additionally contains the default settings for the BRIN to BTREE conversion. If you want to change the default settings, for example, in order to prevent the removal of duplicate values, you can adapt the respective constants in the script brin_to_btree_conversion.sql.
- In order to start the script, use the command:
\i wincc_oa_install_path/data/NGA/PostgreSQL/sql/brin_to_btree_conversion.sql
Note: In order for psql to find all the dependencies of the conversion script, both the brin_to_btree_conversion.sql and the brin_to_btree_conversion_lib.sql must be located in the same directory.Note: While the recommended tool is psql, you can still use pgadmin by using its embedded psql tool. However, the regular pgadmin query tool does not work for this script. - Restart the PostgreSQL® backend after the brin_to_btree_conversion.sql script finished with success, since it will alter the index configuration for the PostgreSQL® backend to create segments with btree index in the future.
Note: Please note that when converting the indices from the archive
tables to btree, the current segments are not converted to ensure that no data loss
occurs during archiving. After a segment change, you can run the
brin_to_btree_conversion.sql script again, to also include the
former current segments.