JT Kirkpatrick wrote:
> 1) we have a second hd in the system that can be used as a backup device,
> in addition to a tape. i had planned to simply copy the directory via a
> tar command run through cron. is that the best way, or should i consider a
> pg_dump (which i know nearly nothing about)? also, i'd like to make a
> backup during the day. what happens when the backup (tar or cp or pg_dump)
> encounters a file in use? does it back it up, or skip it?
1.) Use mirroring onto this HD for the PGDATA directory tree (which can
abd should be on its own filesystem, IMO). Then, to back up the system,
you break the mirror (which will wait on write activity to stop), back
up the offline disk, then reestablish the mirror.
2.) Use the command: pg_dumpall. Run this as postgres nightly. As an
example, here is my crontab entry (substitute your PGDATA for
"/var/lib/pgsql"):
30 1 * * * pg_dumpall -o >/var/lib/pgsql/db.bak
45 1 * * * cp /var/lib/pgsql/db.bak /var/lib/pgsql/db.bak.old
This does a backup, then, 15 minutes later (which has always been enough
time), it makes another copy of the file.
To restore such a backup, you perform a fresh initdb, making sure your
PGDATA tree is clear first, then "psql -e template1 <db.bak" does the
trick. This is only recommended for later versions (6.3 and later). This
is the same thing you do when upgrading -- upgrading is good
dump/restore exercise (I did it again today, upgrading from 6.3.2 to 6.5
on my production server)!
The LAST thing you want to do is pull a [tar,cpio,dump,bru]-style backup
while the database is active -- you will end up with a totally corrupted
database on the backup media. Also, for concurrency control and backup
integrity, I highly recommend upgrading to 6.5 -- it has already shown
significant performance improvements for our usage here.
>
> 2) how do i schedule a regular vacuum through cron? do you not have to be
> in psql to do a vacuum? or can you do it from the bash prompt? (we are
> using linux rh 5.2, kernel 2.2.2, postgres 6.4.2)
Again, my crontab (sub your database name for "intranet"):
0 1 * * * psql -q -c "vacuum analyze" intranet
Under 6.5, you can issue the vacuumdb command from the shell.
6.5 test rpms are available at ftp://ftp.postgresql.org/pub/RPMS.beta --
just note that the rpms are beta test rpms, built on RH 5.2 by Thomas.
To add a crontab entry like mine above, as user postgres execute
"crontab -e" -- you will be dropped into a vi session, from where you
can enter crontab entries, hit the ZZ in the usual way, and watch it
work.
Lamar Owen
WGCR Internet Radio
