Thread: Great change (size of data dir) upgrading postgresql

Great change (size of data dir) upgrading postgresql

From
Carlos Costa Portela
Date:
    Hello all!

    I've upgraded postgresql from 7.2 to 7.4, and my data dir has
changed a lot:

S.ficheros          Tamaño Usado  Disp Uso% Montado en
/dev/hda1             1.9G  1.8G   64M  97% /usr/local/pgsql/data.old
/dev/hdb2             4.5G  357M  3.9G   9% /usr/local/pgsql/data

    Is this normal? I've made some queries to the system and all the
data seems be there. Of course, the database goes faster than before
upgrading it, but I want know if I can delete this data.old directory.

    Thanks in advance,
        Carlos.


       [ http://www.biologia.org/ :: portal especializado en Biología ]
 _______Carlos Costa Portela_________________________________________________
| e-mail:  ccosta@servidores.net | home page: http://casa.ccp.servidores.net |
|_____Tódalas persoas maiores foron nenos antes, pero poucas se lembran______|


Re: Great change (size of data dir) upgrading postgresql

From
Carlos Costa Portela
Date:
On Sun, 18 Jan 2004, Nigel J. Andrews wrote:
> On Sun, 18 Jan 2004, Carlos Costa Portela wrote:
> > S.ficheros          Tamaño Usado  Disp Uso% Montado en
> > /dev/hda1             1.9G  1.8G   64M  97% /usr/local/pgsql/data.old
> > /dev/hdb2             4.5G  357M  3.9G   9% /usr/local/pgsql/data
> >
> Well, to be safe...back it up.

I've backed up it to cd. Thanks.

> However, I would suggest that you haven't 'vacuum'-ed you 7.3 database for some
> time. To give some level of comfort before destroying the original data
> directory you could fire up the 7.2 server and run vacuum full against it's

I have a vacuum command each day on my crontab :-?



       [ http://www.biologia.org/ :: portal especializado en Biología ]
 _______Carlos Costa Portela_________________________________________________
| e-mail:  ccosta@servidores.net | home page: http://casa.ccp.servidores.net |
|_____Tódalas persoas maiores foron nenos antes, pero poucas se lembran______|


Re: Great change (size of data dir) upgrading postgresql

From
"Nigel J. Andrews"
Date:
On Sun, 18 Jan 2004, Carlos Costa Portela wrote:

>     Hello all!
>
>     I've upgraded postgresql from 7.2 to 7.4, and my data dir has
> changed a lot:
>
> S.ficheros          Tamaño Usado  Disp Uso% Montado en
> /dev/hda1             1.9G  1.8G   64M  97% /usr/local/pgsql/data.old
> /dev/hdb2             4.5G  357M  3.9G   9% /usr/local/pgsql/data
>
>     Is this normal? I've made some queries to the system and all the
> data seems be there. Of course, the database goes faster than before
> upgrading it, but I want know if I can delete this data.old directory.

Well, to be safe...back it up.

However, I would suggest that you haven't 'vacuum'-ed you 7.3 database for some
time. To give some level of comfort before destroying the original data
directory you could fire up the 7.2 server and run vacuum full against it's
databases. Then check the disk usage and hopefully you would see similar disk
usage to the new installation. You may also need to reindex the 7.2 databases
to get the old installation to similar disk usage as the new one.


---
Nigel Andrews


Re: Great change (size of data dir) upgrading postgresql

From
"Nigel J. Andrews"
Date:
On Sun, 18 Jan 2004, Carlos Costa Portela wrote:

> On Sun, 18 Jan 2004, Nigel J. Andrews wrote:
> > On Sun, 18 Jan 2004, Carlos Costa Portela wrote:
> > > S.ficheros          Tamaño Usado  Disp Uso% Montado en
> > > /dev/hda1             1.9G  1.8G   64M  97% /usr/local/pgsql/data.old
> > > /dev/hdb2             4.5G  357M  3.9G   9% /usr/local/pgsql/data
> > >
> > Well, to be safe...back it up.
>
> I've backed up it to cd. Thanks.
>
> > However, I would suggest that you haven't 'vacuum'-ed you 7.3 database for some
> > time. To give some level of comfort before destroying the original data
> > directory you could fire up the 7.2 server and run vacuum full against it's
>
> I have a vacuum command each day on my crontab :-?

Is that a vacuum full? It may make a difference if the free space map hasn't
been able to track all the deletions so that new data could reuse that
space. The vacuum full does what could be described as data compaction. it
moves tuples aroud to fill holes in the tuple store.

The other thing is the reindex. Btree indexes were well known for increasing in
size as new records were added if those records were expanding the range of
keys covered by an index. For example indexing a serial where older values were
deleted and not reused didn't free the space in the index until 7.4. Check that
out by picking a much updated table with an index on a field with
values obtained from a sequence, drop that index then recreate it. Hopefully
you would see a noteworthy decrease in disk usage.

Of course you could look at the files within the data/base directory tree and
see which of those are consuming alot of disk space. These files are named
after the oid, in pg_class, of the entity they contain.


--
Nigel Andrews


Re: Great change (size of data dir) upgrading postgresql

From
Tom Lane
Date:
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> The other thing is the reindex.

I favor the index-bloat idea as well, but it'd be a good idea to verify.
An easy check to make is to see which rows in pg_class have the largest
relpages values.

> Of course you could look at the files within the data/base directory tree and
> see which of those are consuming alot of disk space. These files are named
> after the oid, in pg_class, of the entity they contain.

Correction: data files are named per the relfilenode column of pg_class.
Database directories, however, are named per pg_database.oid.

            regards, tom lane

Re: Great change (size of data dir) upgrading postgresql

From
Carlos Costa Portela
Date:
On Sun, 18 Jan 2004, Nigel J. Andrews wrote:
> > I have a vacuum command each day on my crontab :-?
>
> Is that a vacuum full? It may make a difference if the free space map hasn't
> been able to track all the deletions so that new data could reuse that
> space. The vacuum full does what could be described as data compaction. it
> moves tuples aroud to fill holes in the tuple store.

/usr/local/pgsql/bin/vacuumdb -U biologia --analyze biologia

> The other thing is the reindex. Btree indexes were well known for
> increasing in size as new records were added if those records were
> expanding the range of keys covered by an index. For example indexing
> a serial where older values were deleted and not reused didn't free
> the space in the index until 7.4. Check that out by picking a much
> updated table with an index on a field with values obtained from a
> sequence, drop that index then recreate it. Hopefully you would see a
> noteworthy decrease in disk usage.

I've not reindexed my databases.

> Of course you could look at the files within the data/base directory
> tree and see which of those are consuming alot of disk space. These
> files are named after the oid, in pg_class, of the entity they
> contain.

Where can I find this info?
data.old:
652M    351253
727M    351342
88M     351513
102M    351540

in data there is no dirs so big.

    Thank you very much for your help,
        Carlos.


       [ http://www.biologia.org/ :: portal especializado en Biología ]
 _______Carlos Costa Portela_________________________________________________
| e-mail:  ccosta@servidores.net | home page: http://casa.ccp.servidores.net |
|_____Tódalas persoas maiores foron nenos antes, pero poucas se lembran______|