Postgres Pro
Downloads
Home   >   mailing lists

Thread: After Upgrade to Postgres 9.5 space not being released

After Upgrade to Postgres 9.5 space not being released

From
Ganesh Kannan
Date:

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



Postgres v9.6

From
"Ferrell, Denise SDC"
Date:
I am currently running v9.3 and was planning to upgrade to v9.5 but noticed v9.6 is in testing.  Does anyone know the major differences in v9.5 - v9.6?  When will v9.6 be ready for release (use)?
 
Thank you in advance,
Denise Ferrell

Re: Postgres v9.6

From
"David G. Johnston"
Date:
​On Tue, Jul 5, 2016 at 9:23 AM, Ferrell, Denise SDC <denise.ferrell@sdc-world.com> wrote:
I am currently running v9.3 and was planning to upgrade to v9.5 but noticed v9.6 is in testing.  Does anyone know the major differences in v9.5 - v9.6?  When will v9.6 be ready for release (use)?
  
Beta docs - release notes page
https://www.postgresql.org/docs/9.6/static/release-9-6.html

Roadmap - I believe September is a reasonable estimate
https://www.postgresql.org/developer/roadmap/
​
David J.