[OSM-dev] [Maposmatic-dev] Re: Growth of on-disk size of OSM database

Jan-Benedict Glaw jbglaw at lug-owl.de
Wed Jul 14 12:14:09 BST 2010


On Wed, 2010-07-14 12:56:22 +0200, Thomas Petazzoni <thomas.petazzoni at enix.org> wrote:
> On Wed, 14 Jul 2010 12:26:57 +0200 Frederik Ramm <frederik at remote.org> wrote:
> > Depends on what you define as "same result". If you make a full dump
> > of your PostgreSQL tables and then re-import that, you will be
> > somewhere at 122 GB or so. You could also do a "vacuum full" to
> > achieve the same result, or do a fresh import. But if you make a
> > comparison of the files in your /var/lib/postgresql directory, these
> > will not be the same as if you were to do a new import. The normal
> > vacuuming that PostgreSQL does is insufficient to release some
> > allocated space and later freed space in the data files, that's why
> > your database is larger that it would have to be. Your select results
> > will look the same.
> 
> Unfortunately, as far as I understand, doing a "vacuum full" is going
> to block accesses to the database for a fairly long amount of time
> (days ?). So the best solution is probably to regularly (every few
> months) do a new full import in a separate database, and switch to this
> new database when the full import is completed.

VACUUM FULL will block. If you have enough disk space, you'd
alternativel start a hugh transaction doing a

	CREATE TABLE xxxxx_new AS SELECT * FROM xxxxx;

for every existing table. (Maybe you need to handle some more objects
manually, have a look at the schema definition dump beforehand). When
all that is done, DROP the old tables and rename the new ones to the
old names. That might be faster than re-importing everything.

MfG, JBG

-- 
      Jan-Benedict Glaw      jbglaw at lug-owl.de              +49-172-7608481
  Signature of:                        Lauf nicht vor Deinem Glück davon:
  the second  :                             Es könnte hinter Dir stehen!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20100714/2f68a402/attachment.pgp>


More information about the dev mailing list