Huge size of Data directory - Mailing list pgsql-novice

From Anthony Presley
Subject Huge size of Data directory
Date
Msg-id 3575.192.168.5.4.1137621747.squirrel@216.63.139.110
Whole thread Raw
Responses Re: Huge size of Data directory  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Huge size of Data directory  (Michael Fuhr <mike@fuhr.org>)
List pgsql-novice
Hi all,

Our postgresql database has been churning along just fine, albeit a little
slower than I'd like.

However, more surprising to me is that it has (over the past few years)
become HUGE.  It's currently 606MB.  Almost all of this space is sitting
in data/base/16556 and data/pg_xlog.

The problem I have with this space ... is that I only have about 8MB of
data stored in the database (according to pg_dump and pg_dumpall).

I'm pasting my postgresql.conf, and the dir listing, to see if you can
help me reduce the size.

tcpip_socket = true

max_connections = 128
port = 5432

shared_buffers = 8192
wal_buffers = 64
vacuum_mem = 32768
wal_files = 16

checkpoint_segments = 3   # in logfile segments (16MB each), min 1
checkpoint_timeout = 300  # in seconds, range 30-3600

syslog = 1 # range 0-2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

Dir listing:

1.7M    ./base/1
1.7M    ./base/16555
4.0k    ./base/16556/pgsql_tmp
238M    ./base/16556
2.2M    ./base/666607
243M    ./base
120k    ./global
353M    ./pg_xlog
9.5M    ./pg_clog
606M    .

Inside base/16556 is:
1247   16398  16419  16432  16445  16458  16475  16577   76744
1249   16400  16420  16435  16446  16460  16477  16584   76762
1255   16402  16421  16436  16447  16462  16557  16586   76763
1259   16404  16422  16437  16448  16463  16559  16599   76771
16384  16406  16423  16438  16449  16465  16561  16601   pg_internal.init
16386  16408  16424  16439  16452  16466  16562  16614   pgsql_tmp
16388  16410  16425  16440  16453  16468  16563  16616   PG_VERSION
16390  16412  16426  16441  16454  16469  16565  448685
16392  16414  16427  16442  16455  16471  16566  76733
16394  16416  16428  16443  16456  16472  16568  76735
16396  16418  16429  16444  16457  16474  16575  76743

Inside pg_xlog is:
-rw-------    1 postgres postgres 16777216 Jan 18 14:09 0000000600000081
-rw-------    1 postgres postgres 16777216 Jan 16 15:41 0000000600000082
-rw-------    1 postgres postgres 16777216 Jan 16 16:22 0000000600000083
-rw-------    1 postgres postgres 16777216 Jan 16 17:23 0000000600000084
-rw-------    1 postgres postgres 16777216 Jan 16 19:05 0000000600000085
-rw-------    1 postgres postgres 16777216 Jan 16 21:28 0000000600000086
-rw-------    1 postgres postgres 16777216 Jan 17 05:53 0000000600000087
-rw-------    1 postgres postgres 16777216 Jan 17 09:27 0000000600000088
-rw-------    1 postgres postgres 16777216 Jan 17 10:41 0000000600000089
-rw-------    1 postgres postgres 16777216 Jan 17 11:26 000000060000008A
-rw-------    1 postgres postgres 16777216 Jan 17 12:27 000000060000008B
-rw-------    1 postgres postgres 16777216 Jan 17 13:25 000000060000008C
-rw-------    1 postgres postgres 16777216 Jan 17 14:19 000000060000008D
-rw-------    1 postgres postgres 16777216 Jan 17 16:25 000000060000008E
-rw-------    1 postgres postgres 16777216 Jan 17 18:19 000000060000008F
-rw-------    1 postgres postgres 16777216 Jan 17 20:59 0000000600000090
-rw-------    1 postgres postgres 16777216 Jan 17 22:01 0000000600000091
-rw-------    1 postgres postgres 16777216 Jan 17 23:54 0000000600000092
-rw-------    1 postgres postgres 16777216 Jan 18 09:42 0000000600000093
-rw-------    1 postgres postgres 16777216 Jan 18 10:48 0000000600000094
-rw-------    1 postgres postgres 16777216 Jan 18 11:40 0000000600000095
-rw-------    1 postgres postgres 16777216 Jan 18 13:29 0000000600000096

Any ideas?

--Anthony

pgsql-novice by date:

Previous
From: s anwar
Date:
Subject: Re: alter table set tablespace over nfs getting stuck
Next
From: Greg Lindstrom
Date:
Subject: Swappng Filds