Re: backing and restoring whole database server - how does this script look - Mailing list pgsql-admin

From P Kapat
Subject Re: backing and restoring whole database server - how does this script look
Date
Msg-id daef5be80904101201g2092b5bfr881ce2f21710b5d8@mail.gmail.com
Whole thread Raw
In response to backing and restoring whole database server - how does this script look  (Kevin Bailey <kbailey@freewayprojects.com>)
List pgsql-admin
i had a similar (though not as complicated) question:

On Fri, Apr 10, 2009 at 5:34 AM, Kevin Bailey
<kbailey@freewayprojects.com> wrote:
> We're trying to get a script to backup and restore a whole database server.
>
> So far we're looking at something like
>
> for db in `psql -U postgres -At -c 'select datname from pg_database where
> not datistemplate' -d template1`; do
>     pg_dump -U ${PGSQL_USER} -h localhost --blobs --format=c
> --file="/var/backups/pg_dump_${db}.pgd" -d template1;
>
>     psql -U statraining -h localhost --command "DROP DATABASE ${db};"
> template1;
>
>     psql -U aplus2admin -h localhost --command "CREATE DATABASE ${db} WITH
> OWNER ${db};" template1;
>
>     psql -U ${db} -h localhost -d ${db} -f
> /home/statraining/${db}_pg_backup.pgd;
>
> done
>
>
> which is obviously a rough-cut - and the main problem we have is that a
> client has set up a DB where the owner names do not match up with the DB
> names.
>
> Or - isn't there something along the lines of:
>
> $ pg_dumpall > all.dbs.out
>
> (transfer file and then on other server...)
>
> $ psql -f all.dbs.out postgres

the sysad on my end, does something like this (backs up the entire
directory) as a weekly cronjob:

/etc/init.d/postgresql stop > /dev/null
rsync -a -H --delete /var/lib/pgsql/ /scratch/pgsql
/etc/init.d/postgresql start > /dev/null

then /scratch/pgsql is backed up over to two different machines. the
DB that i run does not need to be live 24/7, so this stopping and
starting works with us. how safe is this method? in other words, when
things get screwed, will a rsync from /scratch/pgsql to /var/lib/pgsql
save a life? i know it DIDNOT when the server was upgraded from 32-bit
to 64-bit and i was forced to recreate the whole DB!!


--
Regards
PK
--------------------------------------
http://counter.li.org  #402424

pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Finding out on exactly what I am stuck
Next
From: Peter Koczan
Date:
Subject: Re: backing and restoring whole database server - how does this script look