Table space grow big - PostgreSQL - Mailing list pgsql-admin

Hi all

Please assist on this scenario, I am a junior DBA, perhaps the question I have is too simple please bear with me.

I have a server with one PostgreSQL database and the data reside in /usr/local/pgsql/data/ running on Redhat 9 O/S.

File system looks as follows:

Filesystem            Size  Used Avail Use% Mounted on

/dev/hda3              73G   61G  8.7G  88% /

/dev/hda1              99M   63M   32M  67% /boot

none                  441M     0  441M   0% /dev/shm

Disc space utilization looks as follows where the data/ directory seems to be taking a lot of space compared to any other directories:

From / directory on user root/:

Size        directory

4.0K    ./lost+found

58M     ./boot

436K    ./dev

900M    ./proc

1.1G    ./var

263M    ./tmp

25M     ./etc

26M     ./root

57G     ./usr

5.3M    ./bin

2.0G    ./home

4.0K    ./initrd

68M     ./lib

32K     ./mnt

4.0K    ./opt

15M     ./sbin

4.0K    ./misc

4.0K    ./.automount

48K     ./tftpboot

44K     ./backup

211M    ./u

8.0K    ./backups

62G     .                      - Size

/usr/local/pgsql/data/base  directory shows the following where 95186722/ takes a lot of space  :

3.6M    ./1

3.6M    ./16975

51G     ./95186722

4.8M    ./4830693

51G     .

Some files (which are called table space names, please correct me if I am wrong?) listed under 95186722/ directory shows different sizes where the biggest of all is 440M :

-rw-------    1 postgres postgres      16K May  5 12:50 219436402

-rw-------    1 postgres postgres      16K May  5 12:50 219436401

-rw-------    1 postgres postgres      16K May  5 12:50 219436400

-rw-------    1 postgres postgres      16K May  5 12:50 219436399

-rw-------    1 postgres postgres      34M May  5 12:50 219436274

-rw-------    1 postgres postgres      42M May  5 12:50 219436273

-rw-------    1 postgres postgres      43M May  5 12:50 219436272

-rw-------    1 postgres postgres      42M May  5 12:50 219436271

-rw-------    1 postgres postgres      34M May  5 12:50 219436270

-rw-------    1 postgres postgres      42M May  5 12:50 219436269

-rw-------    1 postgres postgres      42M May  5 12:50 219436268

-rw-------    1 postgres postgres      51M May  5 12:50 219436267

-rw-------    1 postgres postgres      34M May  5 12:50 219436266

-rw-------    1 postgres postgres      51M May  5 12:50 219436265

-rw-------    1 postgres postgres      15M May  5 12:50 218478745

-rw-------    1 postgres postgres      11M May  5 12:50 218478744

-rw-------    1 postgres postgres      10M May  5 12:50 218478743

-rw-------    1 postgres postgres      13M May  5 12:50 218478742

-rw-------    1 postgres postgres     440M May  5 12:50 216081969.1

Now the dump file of the very same database created by using command : pg_dump –U user –O dbname > /tmp/filename is 2.8G big. :

-rw-r--r--  1 root root 2.8G May  5 12:47 db.dump

The main question I have is: What makes the /usr/local/pgsql/data/base/95186722/  grow so big while the actual data with its schema is only 2.8G and that is there a way to reduce the table space sizes or anything related to that?

Your response will be truly appreciated

Thanks

Khangelani Gama

Confidentiality Notice:http://ucs.co.za/conf.html