Great change (size of data dir) upgrading postgresql

[Carlos Costa Portela]

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: cc****@servidores.net | home page: http://casa.ccp.servidores.net |
|_____Tódalas persoas maiores foron nenos antes, pero poucas se lembran______|
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Nigel J. Andrews]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Christopher Browne]

Quoth cc****@servidores.net (Carlos Costa Portela):

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.

Apparently your (Linux?) distribution did something automatic; you
might want to indicate what colour/flavour of Unix you might have been
using, as that might give some better indication of what should be
done...

The reduction in size seems consistent with the old database having
had a lot of updates/deletes, where tuples didn't get vacuumed out.
The result being that some tables and indices might bloat in size.

When the data was freshly loaded into the new database, it apparently
shrunk in size.

One suggestion: You may want to run VACUUM on the database once in a
while; that should keep the size down. Indeed, in 7.4, there's an
automatic vacuum daemon that should do a good job of preventing such
growth...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/rdbms.html
They are called computers simply because computation is the only
significant job that has so far been given to them. -- Louis Ridenour