Manually vacuuming the TDV repository.
Article ID: KB0082478
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0.0 and later |
Description
If you find that the disk usage space under <install dir>/repository/data has grown above 1 GB (typically several GBs), it may be time to perform a Postgres vacuum.
Other symtoms include:
Messages in the /logs/cs_repository.log similar to these:
• 2016-01-26 14:10:19.379 PST [ 14016]WARNING: skipping "metadata" --- only table or database owner can analyze it
Details of the scenario:
•VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.
• With no parameter, VACUUM processes every table in the current database that the current user has permission to vacuum. With a parameter, VACUUM processes only that table.
•VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE for more details about its processing.
- Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.
Issue/Introduction
Resolution
1) Log into the Postgres repository on the command line
Note: $REPOSITORY_PORT can be found in server_values.xml( search for database/databasePort).
$REPOSITORY_NAME is usually 'cis070001' or 'cisrepo', the name also can be found in server_values.xml (search for database/databaseSchema).
$USER_NAME is the os user that was used to install the CIS Server.
2) Execute this command to find the size of the tables on diskSELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
3) Stop CIS using $INSTALL_DIR/bin/composite.(sh or bat)
4) Stop the repository using command $INSTALL_DIR/bin/composite.sh repo stop
5) Change the settings to roll the repository logs (before this fix the cs_repository.log would grow infinitely)
6) Open $INSTALL_DIR/repository/data/postgres.conf
7) Change log_filename = 'cs_repository-%a.log'
8) enable log_truncate_on_rotation = on
Add SUPERUSER to the user that installed CIS
9) Start the repository $INSTALL_DIR/bin/composite.sh repo start
10) Execute the command: $INSTALL_DIR/repository/bin/psql -U root -p $REPOSITORY_PORT -h localhost -W -d $REPOSITORY_NAME -c "ALTER USER $USER_NAME WITH SUPERUSER“
\du
Truncate the size of the tables – this may take some time
13) Using psql, execute this command: VACUUM FULL ANALYZE VERBOSE;
14) Verify that the table sizes are truncated
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
Feedback
