Hi Team,
We had an one issue with prod server .The database showing the size of 809gb but it assigned in custom tablespace and the path also showing same size but
inside the tablespace path only minimum files only 1gb others it kb size only
we did vacuum full also no dead tuples but the size occupied its strange actual db size is 90gb
need some help how can i resolve it.
please find the below details,
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
AIBE01PR | npp | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 809 GB | be2_data |
npp | npp | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8067 kB | pg_default |
postgres=# SELECT
pg_size_pretty (
pg_tablespace_size ('be2_data')
);
pg_size_pretty
----------------
796 GB
(1 row)
this is the schemas size only inside the database something wrong in occupied the space around 750gb please do the needful
schemaname | s
-----------------------+------------
company_prod | 18 MB
common_prod | 2408 kB
media_prod | 9168 kB
temp_tables_prod | 32 kB
print_prod | 93 MB
printvp_prod | 80 kB
utl_file | 8192 bytes
pg_temp_5 | 3680 kB
pcheck_bo | 405 MB
item_prod | 1214 MB
admin | 3120 kB
importbe_prod | 2381 MB
hint_plan | 8192 bytes
printtracking_prod | 756 MB
tariff_prod | 168 kB
lbmbe_prod | 7731 MB
lbm_prod | 56 kB
ora_webbe | 20 MB
printtracking_hist | 1408 kB
flatcom_hist | 16 kB
company_hist | 1051 MB
tariffbe_hist | 9480 kB
lbmbe_hist | 48 kB
flatcom_prod | 112 kB
public | 455 MB
realtimebe_prod | 7181 MB
lbmdigital_hist | 38 MB
tariffbe_prod | 2216 kB
printmoc_prod | 32 kB
item_hist | 10 GB
printmop_prod | 0 bytes
lbmdigital_prod | 14 GB
importexternalbe_prod | 81 MB
media_hist | 16 MB
reportsbe_prod | 696 kB
import_prod | 8192 bytes
cmt_prod | 4934 MB