After upgrade to 9.5 space not being released

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

Responses

Browse pgsql-bugs by date

  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