Hello All,
Posting in this list, as I haven't received any responses in the pgsql-admi=
n list. I think this may be a bug.=20
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 tab=
les (total size exceeds 25 TB ) from "unlogged" to "regular" tables,
and as the process running realized that PG was not releasing the space occ=
upied by the "unlogged" version of the tables. So I stopped the job and sta=
rted running " alter table set tablespace, set logged" for all "unlogged" t=
ables - this way I can migrate all the tables to new tablespaces (mounted o=
n new vols), and also making them "regular" tables at the same time. How ca=
n I recover the space occupied by the original "unlogged" tables in the ori=
ginal 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" --ne=
w-datadir "/var/lib/pgsql/9.5/data" --old-bindir "/usr/pgsql-9.4/bin/" --n=
ew-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 director=
y is empty)
Interesting part is indexes that were associated with the unlogged tables =
have been converted to regular indexes, and did not experience this "doubli=
ng space" issue. I kicked off "vacuumdb --full" few days ago and was hopin=
g that would do the trick - but did not help, and I still see same bunch of=
files in the PG_9.4 tablespaces/vols still occupying multi TB of space.
Also from PG viewpoint, there are no objects in these tables: ts_data2 is m=
apped 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 ma=
ke PG release the space.
Thank you in advance, much appreciated.
Ganesh Kannan=
