Thread: vacuum and backup
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? (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) 2) Is it better to pg_dumpall than pg_dump to backup the database and why? 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. Thanks in advance. I did try searching the archives but didn't have a great deal of success finding answers. Regards, Colleen. Colleen Williams colleen@digital-arts.co.uk 0207 484 8825 Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR http://www.digital-arts.co.uk
Colleen Williams <colleen@digital-arts.co.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
> 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.
You would probably be better off writing your scripts in a
system language such as Python or Perl. Here is an excerpt from
a basic Python backup program I wrote (uses pg_dump &
postgresql-python) that does just that.
#!/usr/bin/python
import os, time
from pg import DB
# Backup database with the current date attached
def pg_backup():
now = time.localtime(time.time())
today = time.strftime("%m-%d-%Y", now)
os.system("pg_dump test > test-%s.sql" % today)
print "Backup completed"
# Run vacuum on database
def pg_clean():
cxn = DB('test')
cxn.query("VACUUME VERBOSE ANALYZE")
print "Database optimized & space recovered"
### This is the main program ###
if __name__ == "__main__":
pg_backup()
pg_clean()
Brent
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
"Brent R. Matzelle" <bmatzelle@yahoo.com> writes: > > 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. > > You would probably be better off writing your scripts in a > system language such as Python or Perl. Here is an excerpt from > a basic Python backup program I wrote (uses pg_dump & > postgresql-python) that does just that. I don't know; I think shell scripts are quite adequate for backup and VACUUM (especially since 7.x comes with the 'vacuumdb' program). I'm a big Perl hound, but for something that just kicks off a couple of existing programs I think Perl and Python are overkill. Just MHO, and not really on-topic... -Doug
> I don't know; I think shell scripts are quite adequate for backup and
> VACUUM (especially since 7.x comes with the 'vacuumdb' program). I'm
> a big Perl hound, but for something that just kicks off a couple of
> existing programs I think Perl and Python are overkill.
>
> Just MHO, and not really on-topic...
I agree 100% that a shell script is probably the best way to go.. All you're
doing is running a few utilities, there is hardly any processing on the part
of the script that calls the utilities so there isn't much of a need for
anything more than what sh (or what ever you like) has to offer..
Something quick :
#!/bin/sh
pgpath=/usr/local/pgsql/bin
homepath=/home/postgres
backup=/usr/local/pgsql/backup
today=`date "+%Y%m%d-%H%M%S"`
$pgpath/pg_dump databasename > $backup/database-${today}dump
/bin/gzip $backup/database-${today}dump
$pgpath/psql ipa databasename $homepath/database-daily.sql
As you can see it dumps to a date/time stamped file, compresses that file,
then performs what ever is in the database-daily.sql file (your vacuum and
anything else you'd care to do)..
This has served me very well for several years.. Good luck!
-Mitch
--- Doug McNaught <doug@wireboard.com> wrote: > "Brent R. Matzelle" <bmatzelle@yahoo.com> writes: > > > > 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. > > > > You would probably be better off writing your scripts in a > > system language such as Python or Perl. Here is an excerpt > from > > a basic Python backup program I wrote (uses pg_dump & > > postgresql-python) that does just that. > > I don't know; I think shell scripts are quite adequate for > backup and > VACUUM (especially since 7.x comes with the 'vacuumdb' > program). I'm > a big Perl hound, but for something that just kicks off a > couple of > existing programs I think Perl and Python are overkill. I was merely suggesting a more scalable method for database administration. It seems that whenever I create a simple shell script to do something I quickly outgrow it in favor of something more manageable. With Perl and Python I am in total control. Forgive me, but I am a developer at heart ;) Brent __________________________________________________ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
At 01:52 PM 27-02-2001 -0500, Mitch Vincent wrote: > >I agree 100% that a shell script is probably the best way to go.. All you're >doing is running a few utilities, there is hardly any processing on the part >of the script that calls the utilities so there isn't much of a need for >anything more than what sh (or what ever you like) has to offer.. > How would I automate backup if I use password authentication? Cheerio, Link.
Lincoln Yeoh wrote: > > At 01:52 PM 27-02-2001 -0500, Mitch Vincent wrote: > > > >I agree 100% that a shell script is probably the best way to go.. All you're > >doing is running a few utilities, there is hardly any processing on the part > >of the script that calls the utilities so there isn't much of a need for > >anything more than what sh (or what ever you like) has to offer.. > > > > How would I automate backup if I use password authentication? We solve that problem here by running the backup under the postgres user, which has superuser priveledges. Other than that I'm not sure what can be done... -- Occam's Principle of Limited Imagination Martijn van Oosterhout <kleptog@cupid.suninternet.com> http://cupid.suninternet.com/~kleptog/
Lincoln Yeoh wrote: > At 01:52 PM 27-02-2001 -0500, Mitch Vincent wrote: > > > >I agree 100% that a shell script is probably the best way to go.. All you're > >doing is running a few utilities, there is hardly any processing on the part > >of the script that calls the utilities so there isn't much of a need for > >anything more than what sh (or what ever you like) has to offer.. > > > > How would I automate backup if I use password authentication? > > Cheerio, > Link. Expect!