Thread: Administration tips

Administration tips

From
Camm Maguire
Date:
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

Re: Administration tips

From
Dmitry Morozovsky
Date:
On 26 Jun 2000, Camm Maguire wrote:

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

You could define $PGPASSWORD to authenticate with passwords; also, you can
restrict the backup host with no passwords (restricting IP traffic by
ipfw, e.g) but with ident... Ways are vary... Surely in latter case you
should make sure that no others connections to your db are possible from
that (hopefully the most restricted host in the network -- this possibly
should apply to any kind of backup host).

Just my $.02 ;-)

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------