[Bucardo-general] fsm and vm files generated under $PGDATA/base

Greg Sabino Mullane greg at endpoint.com
Fri Mar 5 19:10:52 UTC 2010

>                    I have turned on the auto vacuum on the slave still
>    for some reason the db is getting bloated up. The master size is only
>    1G and the slave is at 9.2GB now. I did cluster on couple of tables
>    also. did any one run into this situation? Can you please help?

This is a little out of the purview of this list, but...

* Existing bloat:

You can use check_postgres.pl --action=bloat to see which tables are the 
most bloated, or simply start suspecting the largest ones by running:

SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

Once you find a suspect table, you can pg_relation_size('tablename') 
and pg_relation_size('indexname') to see how big the main table and 
indexes are. Try vacuuming them (or cluster) and see if they shrink. 
If the indexes are large, try REINDEX on them.

* Future bloat:

If you are doing fullcopy syncs, try the truncate mode versus the 
delete one for some long-term bloat savings.

If the tables are filling up too quick, you can tell autovacuum to be 
more aggressive. The defaults in postgresql.conf are very conservative. 
In addition, you can adjust them per table with pg_autovacuum < 8.4, 
and ALTER TABLE .. SET in 8.4 and up. See:


Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
Url : https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20100305/7fadbbfe/attachment.bin 

More information about the Bucardo-general mailing list