Re: Backing Up a Postgres database...

Hi Jon,

Here's briefly what I do:

1. A scheduled cronjob on the production machine that dumps all databases.

(on linux RH)

/usr/local/pgsql/bin/pg_dumpall -c > /pg_backup/prod_all.pgdump_c

2. A cronjob on the backup machine that retrieves the dump file ( and what
ever you like ) over ssh-ftp from the production machine.

Please see http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html ( this is a
good one ! )

3. A cronjob that first drops and then creates all db's ( the -c switch in
pg_dumpall command ) on the backup machine.

psql -e template1 < /backup_down/prod_all.pgdump_c

And there's no need to shut down either postmaster, you can do this 'on the
fly'.

Hope this helps.

BR,

Aarni



----- Original Message -----
From: "Jonathan Telep" <jon2@autoweb.net>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, September 24, 2003 1:51 PM
Subject: [NOVICE] Backing Up a Postgres database...


> Hello, I'm not sure who I should be posing this question to but I'm a
> System Administrator who has inherited several Debian Linux servers one
> of which apparently hosts a rather large Postgres database.  One of my
> responsibilites is to back that database up each night and be able to
> recover it "on the fly" in the event of a problem.  I have no idea how
> to even check which version of Postgres is loaded on this server, let
> alone, be able to back it up.
>
> Is there anyone who can give me just some basic commands on how to shut
> it down each night, back it up, verify that the back up is good and what
> to do in the event that I ever have to restore it?  I know it seems like
> a lot but I'm swamped with a million things to do and would rather not
> have to read a couple hundred pages of material if I can avoid it.
>
> Thanks in advance,
>
> Jon
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
Aarni Ruuhimäki
Megative Tmi
KYMI.com

Pääsintie 26
45100 Kouvola
FINLAND

info@kymi.com / aarni.ruuhimaki@kymi.com

+358-5-3755 035 / +358-50-4910 037
------------------------------------------------
Linux RedHat / KDE