Обсуждение: VACUUM FULL vs backup/restore
We are copying a production database and then pairing it down dramatically (perhaps removing 99% of records, most of which are large objects). It seems my options are 1) VACUUM FULL with a reindex_db; or 2) backup, then restore. Is there anything one does better than the other? Our impression is that the backup and restore will run faster (perhaps 2 hours), whereas we have a currently running VACUUM FULL that's been running for 4.5 hours already. Anybody have any experience on this? Would a backup/restore essentially create a minimally sized database with all fresh indexes? Thanks, David
On Tue, Apr 27, 2010 at 4:50 PM, David Wall <d.wall@computer.org> wrote: > It seems my options are 1) VACUUM FULL with a reindex_db; or 2) backup, then > restore. > I'd go with the backup+restore. The other option is to to a trivial ALTER to one of your fields which causes the table to be rewritten. Basically, anything is faster than a VACUUM FULL in my experience.
On Tue, Apr 27, 2010 at 2:50 PM, David Wall <d.wall@computer.org> wrote: > We are copying a production database and then pairing it down dramatically > (perhaps removing 99% of records, most of which are large objects). > > It seems my options are 1) VACUUM FULL with a reindex_db; or 2) backup, then > restore. > > Is there anything one does better than the other? Our impression is that > the backup and restore will run faster (perhaps 2 hours), whereas we have a > currently running VACUUM FULL that's been running for 4.5 hours already. Vacuum Full was invented back in the days when drive space was not as cheap as it is today. It can shrink a table without having to have enough room free on the drive for a complete copy to be made. In pgsql 9.0 that behaviour is changing, making vacuum full much faster than it once was. So, the advantage of vacuum full is that it can (for now) operate in a space restricted environment if needed. Given how cheap drives are nowadays, the preferred method is either to cluster in place each table (which needs 2x file size drive space) or backup / restore the db.
David Wall wrote: > It seems my options are 1) VACUUM FULL with a reindex_db; or 2) > backup, then restore. > > Is there anything one does better than the other? Our impression is > that the backup and restore will run faster (perhaps 2 hours), whereas > we have a currently running VACUUM FULL that's been running for 4.5 > hours already. VACUUM FULL can easily run for 4.5 days. See http://wiki.postgresql.org/wiki/VACUUM_FULL for a discussion of the issues here and comments about what you should do instead (probably CLUSTER if you're running 8.3 or later). The wiki seems to be having issues right now so you might need to grab it from a cache somewhere else instead, i.e. http://webcache.googleusercontent.com/search?q=cache:fCJXjixyulMJ:wiki.postgresql.org/wiki/VACUUM_FULL&hl=en&strip=1 If you're using 8.2 or earlier, dump and reload is the way to go for you. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us