Re: vacuum and backup

Colleen Williams <colleen(at)digital-arts(dot)co(dot)uk> writes:

> Hi,
>
> I have some questions regarding vacuum and backup...
> 1) Do we need to stop the postmaster process before we do a nightly
> vacuum analyze or pg_dump in version 7.0.3 of PostgreSQL?

No, not at all.

> (I read somewhere that we will end up with a totally corrupted backup
> if we try to dump a live database and it made sense but I have had no
> problems using pg_dump and then using the dump files to create new
> databases)

I don't know where you read that but it's completely wrong. pg_dump
and VACUUM are totally safe on a live database.

> 2) Is it better to pg_dumpall than pg_dump to backup the database and why?

pg_dumpall saves out the system tables (including your user lists,
globally defined languages, and other useful metadata) in addition to
all databases. It's probably worth doing, at least once a week or so.
It's also convenient if you have a lot of databases.

> 3) Has anyone written some shell scripts to do vacuum and backup?
> I will have to write some but am not conversant with shell scripts and
> would greatly appreciate any help.

My vacuum script is a simple entry in the crontab for 'postgres':

0 4 * * * /usr/bin/vacuumdb -z -a

This runs the 'vacuumdb' script (which comes with Postgres) every
morning at 4AM. The '-a' means vacuum all databases, and the '-z'
means to do VACUUM ANALYZE to collect statistics for the query
planner. Note that under most versions of 'cron', the results will be
mailed to 'postgres' every night, so you'll want to set up a forward
for that account.

Here's a fragment of my backup script (which runs as root):

BACKUPDIR=/u1/backups

# Phase 1 -- back up the PostgreSQL databases
cd $BACKUPDIR/databases
su postgres -c "pg_dumpall" > postgres.dump

Good luck--be sure to post again if you run into trouble.

-Doug