Re: Database backup and restore

In bash, I use:

========== script fragment ==========
~!/bin/bash
$BackupDir=<whereever>
$LogFile=<whichever>
for db_name in $(/usr/local/pgsql/bin/psql -U postgres -d template1 -n -t -c
"select datname from pg_database where datistemplate='f';");
do
/usr/local/pgsql/bin/pg_dump -U postgres -Fc -Z1 -b
"$db_name" -f "$BackupDir/$db_name.pgdump" 2>> $LogFile.err && \
echo "$(date +%c): Successfully dumped database
$db_name" >> $LogFile
done
========== script fragment ==========

Cheers,
Murthy

-----Original Message-----
From: dima [mailto:_pppp(at)mail(dot)ru]
Sent: Friday, November 22, 2002 11:07
To: Colin Stearman
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Database backup and restore

> Am I missing something? There seems to be no way to dump all databases
> and then restore them (as you'd have to do on a version change) if the
> databases contain blobs.
>
> pg_dump supports dumping of blobs with the -b -Ft switch, but using
> pg_dumpall with those switches doesn't seem to work. And in any case,
> pg_restore seems to be missing its equivalent pg_restoreall. Other than
> (painfully) manually pg_dumping and pg_restoring each database, how else
> could it be done?
>
> I would search the mailing lists but the site's search engine is down.
i wrote a script in perl which dumps all the DBs but template*
i can mail it to you if you wish

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org