Executing manual maintenance

Edit online

Perform maintenance by running analyze, vacuum (online or full), and reindex commands with proper pre-checks, monitoring, and backups to ensure database health and minimal downtime.

Pre-Checks before the Maintenance Window:

Enable pgstattuple extension (optional, for accurate bloat estimation):
- Check if pgstattuple is installed:
```
SELECT * FROM pgstattuple_approx('schema.table');
```
- Install pgstattuple if it is not already installed:
```
CREATE EXTENSION IF NOT EXISTS pgstattuple;
```
Disk space:
- Ensure enough free space for VACUUM FULL, which rewrites tables.
Create backups:
- Take a fresh backup or snapshot before starting maintenance.

During the Maintenance Window:

Disable heavy jobs such as ETL or batch inserts to reduce lock contention.
Monitor logs in /var/isvd/data/logs/pgmaint_logger.
Use pg_stat_activity to identify blocking sessions.
Expect exclusive locks and idspgmaint connection revocation during VACUUM FULL.

Maintenance Examples:

ANALYZE (equivalent to runstats):
- ANALYZE only on all idsldap database schemas and tables.
```
./idspgmaint -U idsldap -d idsldap --analyze-only
```
- ANALYZE only on specific qualified tables of the "idsldap" database, then REINDEX those tables.
```
./idspgmaint -U idsldap -d idsldap -t idsldap.employees,idsldap.transactions --analyze-only --reindex
```
Online VACUUM:
- Vacuum all tables in idsldap schema (online):
```
./idspgmaint -n idsldap
```
- Process EVERY idsldap schemas and tables: VACUUM online, ANALYZE, REINDEX, with progress:
```
./idspgmaint -U idsldap -d idsldap --analyze --reindex –watch
```
- Vacuum online idsldap schema specific tables and ANALYZE, show concise progress every 10s:
```
./idspgmaint -U idsldap -d idsldap -n idsldap -t orders,customers --analyze --watch-quiet --watch-interval 10
```
- Process the idsldap database; exclude the title table, temp_ tables, acl tables, and everything in audit schema: VACUUM online and ANALYZE, show concise progress:
```
./idspgmaint -U idsldap -d idsldap --exclude idsldap.title --exclude 'idsldap.temp_*' --exclude 'acl*' --exclude 'audit.*' --analyze --watch-quiet
```
- Online Vacuum + ANALYZE on idsldap schema, only specific tables (employees and transactions); VACUUM FULL only if bloat >= 25%:
```
./idspgmaint -U idsldap -d idsldap -n idsldap -t employees,transactions --full-only-on-bloat=25 --analyze
```
VACUUM Full:
- Vacuum full all tables (exclusive mode):
```
./idspgmaint –-full
```
- Force VACUUM FULL (exclusive), then runstat all idsldap database schemas and tables:
```
./idspgmaint -U idsldap -d idsldap --full --analyze
```
- Force VACUUM FULL on all idsldap database schemas and tables (exclusive mode), then ANALYZE and REINDEX:
```
./idspgmaint -U idsldap -d idsldap --full --analyze --reindex
```
Dry-run:
- Dry-run ANALYZE-ONLY for entire data base idsldap (default log directory), nothing is really executed.
```
./idspgmaint -U idsldap -d idsldap --analyze-only --dry-run
```
- Dry-run across all idsldap database schemas and tables; FULL only if bloat >= 25%, analyze, and reindex concurrently.
```
./idspgmaint -U idsldap -d idsldap --full-only-on-bloat=25 --analyze --reindex-concurrently --dry-run
```
- Dry-run for idsldap schema’s specific tables (employees and transactions), show what would be VACUUM online, ANALYZE and REINDEX.
```
./idspgmaint -U idsldap -d idsldap -n idsldap -t employees,transactions --analyze --reindex --dry-run
```
Safety tips:
- Always test in a staging environment first.
- Use --dry-run to verify the plan.
- Use --require-log-dir for compliance logging.
- Avoid VACUUM FULL if downtime is not acceptable. Prefer online VACUUM and ANALYZE.

Post Maintenance:

Verify stats:

SELECT relname, last_vacuum, last_analyze FROM pg_stat_all_tables;

Validate indexes:

SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Restart the IBM® Verify Directory container.

For more information about the idspgmaint utility, see idspgmaint.