From: | Ganesh Kannan <ganesh(dot)kannan(at)weatheranalytics(dot)com> |
---|---|
To: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | After upgrade to 9.5 space not being released |
Date: | 2016-06-03 15:03:55 |
Message-ID: | BL2PR07MB2356120BBEFDC5B1E93EBFDC86590@BL2PR07MB2356.namprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello All,
Posting in this list, as I haven't received any responses in the pgsql-admin list. I think this may be a bug.
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" few days ago and was hoping 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | furstenheim | 2016-06-03 16:14:58 | BUG #14176: Re: BUG #14173: Not using partitions with ANY(ARRAY[...]) |
Previous Message | Tom Lane | 2016-06-03 14:25:20 | Re: [BUGS] BUG #14155: bloom index error with unlogged table |