Thread: Great change (size of data dir) upgrading postgresql
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______|
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______|
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
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
"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
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______|