Re: [GENERAL] VACUUM FULL vs backup/restore

2010-04-27 Thread Greg Smith

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
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] VACUUM FULL vs backup/restore

2010-04-27 Thread Scott Marlowe
On Tue, Apr 27, 2010 at 2:50 PM, David Wall  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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: [GENERAL] VACUUM FULL vs backup/restore

2010-04-27 Thread Vick Khera
On Tue, Apr 27, 2010 at 4:50 PM, David Wall  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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[GENERAL] VACUUM FULL vs backup/restore

2010-04-27 Thread David Wall
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general