11.2 Dynamic Disk Space Used by Dashboard (Pool Conditions) Has Increased More Than Expected

Cause

If the FSM (Free Space Map) value in the database of pool conditions is insufficient, usage of the disk that stores the pool condition information might have increased if the Vacuum operation was not performed correctly by Autovacuum.

Target Directories

[Windows Manager]

Installation_folder\RCXCTMG\Dashboard\var\pgsql\db\pg_log

[Linux Manager]

/var/opt/FJSVctdsb/pgsql/db/pg_log

File name

[Windows/Linux Manager]

 postgres-ctdsb-YYYY-MM-DD_hhmmss.log

*: YYYY-MM-DD_hhmmss in file names is the date and time.

Output Message

[Windows/Linux Manager]

WARNING:  relation "public.b_edi_xxxxxxxxxx"
contains more than "max_fsm_pages" pages with useful free space
HINT:  Consider using VACUUM FULL on this
relation or increasing the configuration parameter "max_fsm_pages".

*: One of the following messages will be output in the "public.b_edi_xxxxxxxxxx" of the output message.

public.b_edi_eub1salewp
public.b_edi_ao0mykhq1o
public.b_edi_cfsukxfdw3

Action

Execute the following command to stop Dashboard (Pool Conditions).
Specify the -nodb option for the command because database maintenance will be performed.
[Windows Manager]
```
Installation_folder\RCXCTMG\Dashboard\bin\dsb_pool_stop -nodb
```
[Linux Manager]
```
 /opt/FJSVctdsb/bin/dsb_pool_stop.sh -nodb
```

Execute the following command to access database (PostgreSQL).

[Windows Manager]

%ProgramFiles%\fjsvpgs83\bin\psql.exe" -d ctdsb -U rcxctdbdsb -h localhost -p PortNo

[Linux Manager]

/opt/FJSVpgs83/bin/psql -d ctdsb -U rcxctdbdsb -h localhost -p PortNo

For PortNo, specify the port number used for the DB of Dashboard. The default value is 5442.

Example in case of the port number 5442:

[Windows Manager]

"%ProgramFiles%\fjsvpgs83\bin\psql.exe" -d ctdsb -U rcxctdbdsb -h localhost -p 5442

[Linux Manager]

/opt/FJSVpgs83/bin/psql -d ctdsb -U rcxctdbdsb -h localhost -p 5442

Optimize the following tables one by one.
- b_edi_eub1salewp
- b_edi_ao0mykhq1o
- b_edi_cfsukxfdw3
Note
If this procedure needs to be suspended, do not suspend in the middle of the operations of one table.
The operations 1 to 4, 5 to 8, and 9 to 12 of the procedure 3 are the operations for respective tables.
1. When stopping the optimization, stop it between tables. After the "ctdsb=#" prompt, execute the commands in the following order.
  Any file name can be specified. However, specify a different file name for each table.
  [Windows Manager]
  The following is an execution example when the backup target is "C:/work/postgresql_bak/b_edi_eub1salewp.txt".
```
ctdsb=# COPY b_edi_eub1salewp TO 'C:/work/postgresql_bak/b_edi_eub1salewp.txt';
```
  [Linux Manager]
  The following is an execution example when the backup target is "/tmp/xxxxx/b_edi_eub1salewp".
```
ctdsb=# COPY b_edi_eub1salewp TO '/tmp/xxxxx/b_edi_eub1salewp';
```
2. Execute the following command to delete data from the disk.
  [Windows/Linux Manager]
```
 ctdsb=# TRUNCATE b_edi_eub1salewp;
```
3. Execute the following commands to load valid data from the file backed up earlier.
  Before executing the commands, check carefully the table name and the file name because it deletes all the contents of the table.
  If the COPY command has failed due to a shortage of disk capacity at the destination, resolve the problem by increasing free space, and execute the COPY command again.
  [Windows Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_eub1salewp;
ctdsb=# COPY b_edi_eub1salewp FROM 'C:/work/postgresql_bak/b_edi_eub1salewp.txt';
ctdsb=# COMMIT;
```
  [Linux Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_eub1salewp;
ctdsb=# COPY b_edi_eub1salewp FROM '/tmp/xxxxx/b_edi_eub1salewp';
ctdsb=# COMMIT;
```
4. Execute the following command.
  [Windows/Linux Manager]
```
ctdsb=# VACUUM FREEZE ANALYZE VERBOSE b_edi_eub1salewp;
```
5. Execute the following command to back up valid data in the table to files.
  Specify the backup file name using an absolute path.
  Any file name can be specified. However, specify a different file name for each table.
  [Windows Manager]
  The following is an execution example when the backup target is "C:/work/postgresql_bak/b_edi_ao0mykhq1o.txt".
```
ctdsb=# COPY b_edi_ao0mykhq1o TO 'C:/work/postgresql_bak/b_edi_ao0mykhq1o.txt';
```
  [Linux Manager]
  The following is an execution example when the backup target is "/tmp/xxxxx/b_edi_ao0mykhq1o".
```
ctdsb=# COPY b_edi_ao0mykhq1o TO '/tmp/xxxxx/b_edi_ao0mykhq1o';
```
6. Execute the following command to delete data from the disk.
  [Windows/Linux Manager]
```
 ctdsb=# TRUNCATE b_edi_ao0mykhq1o;
```
7. Execute the following commands to load valid data from the file backed up earlier.
  Before executing the commands, check carefully the table name and the file name because it deletes all the contents of the table.
  If the COPY command has failed due to a shortage of disk capacity at the destination, resolve the problem by increasing free space, and execute the COPY command again.
  [Windows Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_ao0mykhq1o;
ctdsb=# COPY b_edi_ao0mykhq1o FROM 'C:/work/postgresql_bak/b_edi_ao0mykhq1o.txt';
ctdsb=# COMMIT;
```
  [Linux Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_ao0mykhq1o;
ctdsb=# COPY b_edi_ao0mykhq1o FROM '/tmp/xxxxx/b_edi_ao0mykhq1o';
ctdsb=# COMMIT;
```
8. Execute the following command.
  [Windows/Linux Manager]
```
ctdsb=# VACUUM FREEZE ANALYZE VERBOSE b_edi_ao0mykhq1o;
```
9. Execute the following command to back up valid data in the table to files.
  Specify the backup file name using an absolute path.
  Any file name can be specified. However, specify a different file name for each table.
  [Windows Manager]
  The following is an execution example when the backup target is "C:/work/postgresql_bak/b_edi_cfsukxfdw3.txt".
```
ctdsb=# COPY b_edi_cfsukxfdw3 TO 'C:/work/postgresql_bak/b_edi_cfsukxfdw3.txt';
```
  [Linux Manager]
  The following is an execution example when the backup target is "/tmp/xxxxx/b_edi_cfsukxfdw3".
```
ctdsb=# COPY b_edi_cfsukxfdw3 TO '/tmp/xxxxx/b_edi_cfsukxfdw3';
```
10. Execute the following command to delete data from the disk.
  [Windows/Linux Manager]
```
 ctdsb=# TRUNCATE b_edi_cfsukxfdw3;
```
11. Execute the following commands to load valid data from the file backed up earlier.
  Before executing the commands, check carefully the table name and the file name because it deletes all the contents of the table.
  If the COPY command has failed due to a shortage of disk capacity at the destination, resolve the problem by increasing free space, and execute the COPY command again.
  [Windows Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_cfsukxfdw3;
ctdsb=# COPY b_edi_cfsukxfdw3 FROM 'C:/work/postgresql_bak/b_edi_cfsukxfdw3.txt';
ctdsb=# COMMIT;
```
  [Linux Manager]
```
ctdsb=# BEGIN;
ctdsb=# TRUNCATE b_edi_cfsukxfdw3;
ctdsb=# COPY b_edi_cfsukxfdw3 FROM '/tmp/xxxxx/b_edi_cfsukxfdw3';
ctdsb=# COMMIT;
```
12. Execute the following command.
  [Windows/Linux Manager]
```
ctdsb=# VACUUM FREEZE ANALYZE VERBOSE b_edi_cfsukxfdw3;
```
13. Execute the following command to end the access state of the database.
  [Windows/Linux Manager]
```
ctdsb=# \q
```
Execute the following command to start Dashboard (Pool Conditions).
[Windows Manager]
```
Installation_folder\RCXCTMG\Dashboard\bin\dsb_pool_start
```
[Linux Manager]
```
/opt/FJSVctdsb/bin/dsb_pool_start.sh
```
Refer to "18.14 Setting the Database for the Dashboard (Pool Conditions)" in the "Setup Guide CE" to review the database settings.