Hello All,
I am relatively new to Postgres world, so would like to get help from experts out here.
Quick summary of the issue:
Upgraded a very large database from Pg 9.4.7 to 9.5.3 , using pg_upgrade --link option. Right after the upgrade, I also started converting all the tables (total size exceeds 25 TB ) from "unlogged" to "regular" tables,
and as the process running realized that PG was not releasing the space occupied by the "unlogged" version of the tables. So I stopped the job and started running " alter table set tablespace, set logged" for all "unlogged" tables - this way I can migrate all the tables to new tablespaces (mounted on new vols), and also making them "regular" tables at the same time. How can I recover the space occupied by the original "unlogged" tables in the original tablespaces?
More details:
Environment:
RHEL 7, XFS
Used this command to upgrade:
/usr/pgsql-9.5/bin/pg_upgrade --old-datadir "/var/lib/pgsql/9.4/data" --new-datadir "/var/lib/pgsql/9.5/data" --old-bindir "/usr/pgsql-9.4/bin/" --new-bindir "/usr/pgsql-9.5/bin/" --link --jobs 4 --verbose
Example:
- one of the volumes with a tablespace with unlogged tables : pg_data2
$ sudo df -h /pg_land_data2/PG_9.4_201409291/16391
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_data2-lv_data2 9.8T 9.0T 789G 93% /pg_data2
$ sudo ls -h /pg_data2/PG_9.4_201409291/16391
>> returns bunch of files...
( pg_upgrade --link created hard links, however /pg_data2/9PG_9.5 directory is empty)
Interesting part is indexes that were associated with the unlogged tables have been converted to regular indexes, and did not experience this "doubling space" issue. I kicked off "vacuumdb --full" last night and was hoping that would do the trick - but did not help, still see same bunch of files in the PG_9.4 tablespaces/vols.
Also from PG viewpoint, there are no objects in these tables: ts_data2 is mapped to /pg_data2
psql$ select
c.oid, schemaname, relname , t.tablespace "curr ts" , relpersistence
from pg_class c, pg_tables t
where
t.tablespace in ('ts_data2', 'ts_data3')
>> 0 rows
I am running out of options, so if anyone has experienced something similar or have helpful suggestions please share. As a last resort, wondering if I should just drop these tablespaces ('ts_data2','ts_data3') outright to make PG release the space.
Thank you in advance, much appreciated.
Ganesh Kannan
