Postgres Pro
Downloads
Home   >   mailing lists

Thread: HELP? Postgres filling up disk

HELP? Postgres filling up disk

From
HT Levine
Date:
I posted over the weekend about this issue under the subject of "VACUUM"  but the replies I got did not help.  We have several large tables which seem to be taking 4 times as much disk space as they should?
 
Let me give a little background:
 
Every night, we do a pg_dump of our production DB and a pg_restore of this dumb into a standby DB.  What I notice is the size of the standby DB is MORE THAN HALF the size of production. 
 
For example, here is the oid2name dump of a table called users on PRODUCTION:
$ oid2name -d EBPRD1 -t users
Oid of table users from database "EBPRD1":
_______________________________
17260  = users
$ ls -l 17260*
-rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260
-rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.1
-rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.2
-rw-------   1 postgres postgres 165445632 Dec 18 17:51 17260.3
 
but the same table on the standby DB yields this:
 
$ oid2name -d EBPRDS1 -t users
Oid of table users from database "EBPRDS1":
_______________________________
3828262123 = users
$ ls -l 3828262123
-rw-------   1 postgres postgres 1073741824 Dec 18 16:55 3828262123
I tried a full VACUUM of the users table on production, but I didn't get any disk space back.  I also looked thru all oids and there are no pg_toast files of significance.    I am on postgres 7.2   How do I reclaim this disk space?
 
Any help would be GREATLY Appreciated!
 
thanks in advance

Re: HELP? Postgres filling up disk

From
Tom Lane
Date:
HT Levine <htlevine@ebates.com> writes:
> For example, here is the oid2name dump of a table called users on
> PRODUCTION:
> $ oid2name -d EBPRD1 -t users
> Oid of table users from database "EBPRD1":
> _______________________________
> 17260  = users
> $ ls -l 17260*
> -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260
> -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.1
> -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.2
> -rw-------   1 postgres postgres 165445632 Dec 18 17:51 17260.3

> but the same table on the standby DB yields this:

> $ oid2name -d EBPRDS1 -t users
> Oid of table users from database "EBPRDS1":
> _______________________________
> 3828262123 = users
> $ ls -l 3828262123
> -rw-------   1 postgres postgres 1073741824 Dec 18 16:55 3828262123

And?  Your transcript does not prove that there's not 3828262123.1 and
so forth ... given that 3828262123 is a full 1Gb, I'd be willing to bet
lunch that 3828262123.1 exists too ...

            regards, tom lane

Re: HELP? Postgres filling up disk

From
"HT"
Date:
Sorry, I've spent much time examining this in detail, so if I neglected the
* in the transcript I pasted you, it still doesn't matter.   There is a  .1
version (see my transcript below),  BUT   THAT'S IT .   So my original
argument is still unanswered:  there are still extra files on production
that aren't coming over in the standby :  it's the .2 and .3    that I don't
want in the production DB.  I have this pattern acros several large tables
and it's costing us disk space...

$ oid2name -d EBPRDS1 -t users
Oid of table users from database "EBPRDS1":
_______________________________
3828262123 = users
$ ls -l 3828262123*
-rw-------   1 postgres postgres 1073741824 Dec 18 23:08 3828262123
-rw-------   1 postgres postgres 558899200 Dec 18 23:16 3828262123.1





"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:15552.1040274982@sss.pgh.pa.us...
> HT Levine <htlevine@ebates.com> writes:
> > For example, here is the oid2name dump of a table called users on
> > PRODUCTION:
> > $ oid2name -d EBPRD1 -t users
> > Oid of table users from database "EBPRD1":
> > _______________________________
> > 17260  = users
> > $ ls -l 17260*
> > -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260
> > -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.1
> > -rw-------   1 postgres postgres 1073741824 Dec 18 17:48 17260.2
> > -rw-------   1 postgres postgres 165445632 Dec 18 17:51 17260.3
>
> > but the same table on the standby DB yields this:
>
> > $ oid2name -d EBPRDS1 -t users
> > Oid of table users from database "EBPRDS1":
> > _______________________________
> > 3828262123 = users
> > $ ls -l 3828262123
> > -rw-------   1 postgres postgres 1073741824 Dec 18 16:55 3828262123
>
> And?  Your transcript does not prove that there's not 3828262123.1 and
> so forth ... given that 3828262123 is a full 1Gb, I'd be willing to bet
> lunch that 3828262123.1 exists too ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: HELP? Postgres filling up disk

From
Tom Lane
Date:
"HT" <htlevine@ebates.com> writes:
> $ ls -l 3828262123*
> -rw-------   1 postgres postgres 1073741824 Dec 18 23:08 3828262123
> -rw-------   1 postgres postgres 558899200 Dec 18 23:16 3828262123.1

Okay, that looks more plausible.

I think you need a VACUUM FULL to recover the wasted space in the master
table, followed by more frequent plain VACUUMs to keep it from bloating.
It's also almost certain that you need to increase the FSM parameters in
postgresql.conf.  See recent discussions in the archives about sizing
FSM properly.

            regards, tom lane

Re: HELP? Postgres filling up disk

From
"HT"
Date:
will do!  I'll let you know how it goes but it will be after xmas  cause we
can have no down-time right now.

thanks!

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:7309.1040338410@sss.pgh.pa.us...
> "HT" <htlevine@ebates.com> writes:
> > $ ls -l 3828262123*
> > -rw-------   1 postgres postgres 1073741824 Dec 18 23:08 3828262123
> > -rw-------   1 postgres postgres 558899200 Dec 18 23:16 3828262123.1
>
> Okay, that looks more plausible.
>
> I think you need a VACUUM FULL to recover the wasted space in the master
> table, followed by more frequent plain VACUUMs to keep it from bloating.
> It's also almost certain that you need to increase the FSM parameters in
> postgresql.conf.  See recent discussions in the archives about sizing
> FSM properly.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster