Administration tips

Greetings!  Being new to this list, and moderately new to postgresql,
I would like to ask those with experience about the best ways to
maintain postgresql databases.  What I am principally concerned with
are

1) the ease of (nightly) backups and replications
2) ease of upgrades to future postgresql releases.

Both of these basically depend on 'pg_dump | psql'.  From what I
understand now, the pg_dump has to be executed on the db host unless
user checking across the network is disabled with a 'trust' entry in
hba.conf, as pg_dump needs to connect as postgres.  It would be great
if the replication/backup procedure could be run on an arbitrary host
without disabling all such user checking.

I've experimented with a few features of postgresql, such as user
defined types (compiled C code), large objects, etc., and found that my
use of these made the above two administrative tasks more difficult.
I've also had problems with users entries and rights being correctly
backed up.

My current strategy is reflected in the following cron entries on the
main db host:

00 00 1,3,5 * * postgres ( destroydb -h backup b1; \
               createdb -h backup b1 ; \
                       pg_dump db | psql -h backup b1 )
00 00 2,4,6 * * postgres ( destroydb -h backup b2; \
               createdb -h backup b2 ; \
                       pg_dump db | psql -h backup b2 )
00 01 * * *     postgres pg_dump db | gzip -9 > pg_dump.out.gz


Any thoughts most appreciated!

Take care,


--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah