Обсуждение: After upgrade to 9.5 space not being released
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=
Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> Upgraded a very large database from Pg 9.4.7 to 9.5.3 , using pg_upgrade --link option.
Did you ever remove the old data directory? If not, the table files are
still linked there.
regards, tom lane
Thanks for the response Tom.=20
Yes, after upgrade to 9.5, I had the hard links. But next step I migrated t=
he tables to new tablespaces , with logging. Wouldn't "alter tablespace set=
tablespace, set logged " supposed to physically recreate the tables in the=
new tablespace, and remove the space occupied by the original unlogged tab=
le?=20
=20
Example: I had an unlogged table: test_y1981, in tablespace: ts_data2, I=
executed statement like the following:
psql> alter table test_y1981 set tablespace ts_data5 , set logged;
I was expecting to see the table files in volume supporting tablespace: "ts=
_data5" ( which is what I see), but no space taken up in volume supporting =
tablespace "ts_data2". As per pg_class, there are no objects in tablespace =
"ts_land2".
Thanks
Ganesh
________________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, June 3, 2016 1:05 PM
To: Ganesh Kannan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] After upgrade to 9.5 space not being released
Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> Upgraded a very large database from Pg 9.4.7 to 9.5.3 , using pg_upgrade =
--link option.
Did you ever remove the old data directory? If not, the table files are
still linked there.
regards, tom lane
Tom,
Sorry I meant to say I did not remove the old data directory given they are=
hard links, and not soft links. I thought I should not remove those hard l=
inks when using "pg_upgrade --link" option.
________________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, June 3, 2016 1:05:57 PM
To: Ganesh Kannan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] After upgrade to 9.5 space not being released
Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> Upgraded a very large database from Pg 9.4.7 to 9.5.3 , using pg_upgrade =
--link option.
Did you ever remove the old data directory? If not, the table files are
still linked there.
regards, tom lane
Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> Sorry I meant to say I did not remove the old data directory given they are hard links, and not soft links. I thought
Ishould not remove those hard links when using "pg_upgrade --link" option.
As long as those old files still have hard links in the old data
directory, they are not going to go away.
Note that if you're using tablespaces, you need to clear out the
appropriate tablespace subdirectories, not only the old data
directory proper.
regards, tom lane
On Fri, Jun 3, 2016 at 01:40:38PM -0400, Tom Lane wrote:
> Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> > Sorry I meant to say I did not remove the old data directory given they are hard links, and not soft links. I
thoughtI should not remove those hard links when using "pg_upgrade --link" option.
>
> As long as those old files still have hard links in the old data
> directory, they are not going to go away.
>
> Note that if you're using tablespaces, you need to clear out the
> appropriate tablespace subdirectories, not only the old data
> directory proper.
Uh, after the upgrade, the output say:
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
That script is designed to do this cleanup.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Thank you Bruce and Tom.
I ran the delete_old_cluster.sh script, and it did the job. Appreciate all =
your work and timely response. That was my bad, I did not want to remove t=
he 9.4 directories until I validate the database upgrade fully, so made som=
e wrong assumptions about hard link behavior.
Thanks again,
Ganesh
________________________________________
From: Bruce Momjian <bruce@momjian.us>
Sent: Friday, June 3, 2016 1:48:53 PM
To: Tom Lane
Cc: Ganesh Kannan; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] After upgrade to 9.5 space not being released
On Fri, Jun 3, 2016 at 01:40:38PM -0400, Tom Lane wrote:
> Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> > Sorry I meant to say I did not remove the old data directory given they=
are hard links, and not soft links. I thought I should not remove those ha=
rd links when using "pg_upgrade --link" option.
>
> As long as those old files still have hard links in the old data
> directory, they are not going to go away.
>
> Note that if you're using tablespaces, you need to clear out the
> appropriate tablespace subdirectories, not only the old data
> directory proper.
Uh, after the upgrade, the output say:
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
That script is designed to do this cleanup.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
I have to ask this out of curiosity... not removing the 9.4 data directorie=
s explains why the space was not released. However, why did I not see the s=
ame issue with Index tablespace directories. I have about 4 TB of indexes a=
nd the vols were pretty much 90% utilized before the "alter table set logge=
d", and may be 95% or so after running the statement. Does the switch from =
"unlogged" to "logged" of tables has a different effect on indexes - may be=
indexes are not "really" rebuilt as were the tables.=20
Thanks
________________________________________
From: Ganesh Kannan
Sent: Friday, June 3, 2016 2:38 PM
To: Bruce Momjian; Tom Lane
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] After upgrade to 9.5 space not being released
Thank you Bruce and Tom.
I ran the delete_old_cluster.sh script, and it did the job. Appreciate all =
your work and timely response. That was my bad, I did not want to remove t=
he 9.4 directories until I validate the database upgrade fully, so made som=
e wrong assumptions about hard link behavior.
Thanks again,
Ganesh
________________________________________
From: Bruce Momjian <bruce@momjian.us>
Sent: Friday, June 3, 2016 1:48:53 PM
To: Tom Lane
Cc: Ganesh Kannan; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] After upgrade to 9.5 space not being released
On Fri, Jun 3, 2016 at 01:40:38PM -0400, Tom Lane wrote:
> Ganesh Kannan <ganesh.kannan@weatheranalytics.com> writes:
> > Sorry I meant to say I did not remove the old data directory given they=
are hard links, and not soft links. I thought I should not remove those ha=
rd links when using "pg_upgrade --link" option.
>
> As long as those old files still have hard links in the old data
> directory, they are not going to go away.
>
> Note that if you're using tablespaces, you need to clear out the
> appropriate tablespace subdirectories, not only the old data
> directory proper.
Uh, after the upgrade, the output say:
Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
That script is designed to do this cleanup.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
On Fri, Jun 3, 2016 at 1:17 PM, Ganesh Kannan <ganesh.kannan@weatheranalytics.com> wrote: > > I have to ask this out of curiosity... not removing the 9.4 data director= ies explains why the space was not released. However, why did I not see the= same issue with Index tablespace directories. I have about 4 TB of indexes= and the vols were pretty much 90% utilized before the "alter table set log= ged", and may be 95% or so after running the statement. Does the switch fro= m "unlogged" to "logged" of tables has a different effect on indexes - may = be indexes are not "really" rebuilt as were the tables. When a relation is removed, any datafiles after the first one are immediately unlinked. But that leaves the old directory with links to full-sized files. But the first datafile of each is treated differently, it is first truncated to zero size, and then not unlinked until the next checkpoint completes. Truncating via one hard link of course reduces the size as "seen" by the other hard link, too, so that space gets freed. So if most of your indexes are less than 1 gig, the space used by those indexes will get freed via truncation, while the larger tables will only have the first gig freed. That's my theory, anyway. Cheers, Jeff