Database backup and restore
Colin Stearman
------=_NextPart_000_011D_01C29216.206AEF50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Am I missing something? There seems to be no way to dump all databases an=
d then restore them (as you'd have to do on a version change) if the databa=
ses contain blobs.
pg_dump supports dumping of blobs with the -b -Ft switch, but using pg_dump=
all 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) m=
anually pg_dumping and pg_restoring each database, how else could it be don=
e?
I would search the mailing lists but the site's search engine is down.
Thanks,
Colin
---------------------------------------------
Colin Stearman
President
Ashdown Technologies
119 E. Main St.
Milford, MA 01757 USA
Tel: +1 508-478-1234
Fax: +1 508-478-1244
www.ashdowntech.com
------=_NextPart_000_011D_01C29216.206AEF50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2719.2200" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Am I missing something? There =
seems to=20
be no way to dump all databases and then restore them (as you'd have to do =
on a=20
version change) if the databases contain blobs.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>pg_dump supports dumping of blobs with the=
-b -Ft=20
switch, but using pg_dumpall with those switches doesn't seem to work. =
; And=20
in any case, pg_restore seems to be missing its equivalent pg_restoreall.&n=
bsp;=20
Other than (painfully) manually pg_dumping and pg_restoring each database, =
how=20
else could it be done?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I would search the mailing lists but the s=
ite's=20
search engine is down.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Colin</FONT></DIV>
<DIV><FONT face=3DArial=20
size=3D2>---------------------------------------------<BR>Colin=20
Stearman<BR>President<BR>Ashdown Technologies<BR>119 E. Main St.<BR>Milford=
, MA=20
01757 USA<BR>Tel: +1 508-478-1234<BR>Fax: +1 508-478-1244<BR><A=20
href=3D"http://www.ashdowntech.com">www.ashdowntech.com</A></FONT></DIV></B=
ODY></HTML>
------=_NextPart_000_011D_01C29216.206AEF50--
dima
> databases contain blobs.
>
> pg_dump supports dumping of blobs with the -b -Ft switch, but using
> 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 can mail it to you if you wish
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Dan Langille
> i wrote a script in perl which dumps all the DBs but template*
> i can mail it to you if you wish
Why not post it to the list? That way it's available for everyone.
I'm guessing it'll be less than 2K or so...
--
Dan Langille : http://www.langille.org/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Murthy Kambhampaty
========== 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:_pp...@mail.ru]
Sent: Friday, November 22, 2002 11:07
To: Colin Stearman
Cc: pgsql...@postgresql.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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Colin Stearman
On first glance at dima's script it does not seem to deal with blobs,
although could easily me made to do so.
Also it does not address automated reloading. In light of the fact that
blobs must be output by -Ft or -Fc in pg_dump, which are tar and custom
respectively, the result of an entire db set dump would be one file of this
type per database. Maybe the name could be used in a similar
reverse-direction script to send each to pg_restore to reload things.
I'll be working on it some more and will share what I come up with.
It seems a glaring omission that it is impossible to upgrade PostgreSQL
across major versions if your database(s) contain blobs (at least, not
without a lot of work).
Colin
----- Original Message -----
From: "Dan Langille" <d...@langille.org>
To: "dima" <_pp...@mail.ru>
Cc: <pgsql...@postgresql.org>
Sent: Friday, November 22, 2002 2:05 PM
Subject: Re: [ADMIN] Database backup and restore
On 22 Nov 2002 at 19:07, dima wrote:
> i wrote a script in perl which dumps all the DBs but template*
> i can mail it to you if you wish
Why not post it to the list? That way it's available for everyone.
I'm guessing it'll be less than 2K or so...
--
Dan Langille : http://www.langille.org/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Dan Langille
> Dan,
>
> On first glance at dima's script it does not seem to deal with blobs,
> although could easily me made to do so.
I think that would be a very good addition to the PostgreSQL toolkit.
FWIW, I do not use blobs and consequently do not think of them when
backups are required.
> I'll be working on it some more and will share what I come up with.
I'm sure it will be added to the contrib directory.
--
Dan Langille : http://www.langille.org/
---------------------------(end of broadcast)---------------------------
dima
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
> On first glance at dima's script it does not seem to deal with blobs,
> although could easily me made to do so.
>
> Also it does not address automated reloading. In light of the fact that
> blobs must be output by -Ft or -Fc in pg_dump, which are tar and custom
> respectively, the result of an entire db set dump would be one file of this
> type per database. Maybe the name could be used in a similar
> reverse-direction script to send each to pg_restore to reload things.
You can easily add -F? option to the script. The result of pg_dump may
be piped with gzip for large DBs as well instead of calling the 2nd
system(). I don't think blobs are really the problem. The problem i
faced before writing the script was to pass the DBA password to pg_dump
since i call it from cron.
Thanks to Colin's suggestions I improved the script a bit. One can
provide the pg_dump options in the beginning of the script as the
$pg_dump_options variable (should I move the user name/password &
options to a config file?). I added piping as well. I'll add a
workaround for huge databases this week probably.
--------------050005040706010001060306
Content-Type: text/plain;
name="backup.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="backup.pl"
#!/usr/bin/perl -w
use DBI;
my ( $user ) = "user";
my ( $password ) = "password";
my ( $pg_dump_options ) = "-d -O -R";
my ( $path ) = $ARGV[0];
if( !$path || $path eq '' ) { $path = '.'; }
chdir( $path ) or die "Can't cd $path: " . $!;
my $dbh = DBI->connect( "DBI:Pg:dbname=template1", $user, $password ) ||
die "Can't connect to the database: " . DBI->errstr;
my $sth = $dbh->prepare( "SELECT datname FROM pg_database" ) ||
die "Can't prepare the query" . $dbh->errstr;
$sth->execute ||
die "Can't execute the query" . $sth->errstr;
my ( @data, @databases );
my $count = 0;
while( @data = $sth->fetchrow_array() ) {
if( !( $data[0] =~ m/template[0,1]/ ) ) {
$databases[$count++] = $data[0];
}
}
$sth->finish;
$dbh->disconnect;
foreach( @databases ) {
my $db = $_;
for( 1 .. 6 ) {
if( -e "$db.backup." . (7-$_) ) {
rename( "$db.backup." . (7-$_), "$db.backup." . (7-$_+1) );
}
}
if( -e "$db.backup" ) { rename( "$db.backup", "$db.backup.1" ); }
system( "export PGUSER=\"$user\"; export PGPASSWORD=\"$password\";
pg_dump $pg_dump_options $_ | gzip > $_.backup" );
}
--------------050005040706010001060306
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
--------------050005040706010001060306--
dima
>>although could easily me made to do so.
> FWIW, I do not use blobs and consequently do not think of them when
> backups are required.
>
>>I'll be working on it some more and will share what I come up with.
> I'm sure it will be added to the contrib directory.
I posted the script to the list as you asked. Well, restore_all stuff
Colin asked for needs some more work since we need to keep owner names
somewhere outside the dumps ( I'm not about to parse those dump files ;)
) to restore the DBs with the same owners. I don't think making backups
of template? DBs makes sense since they would be unportable between the
versions of PostgreSQL. Anyway, restore_all script will be work in
progress (I hope I'll have enough free time to contribute this week).
Porting dumps between versions of PostgreSQL seems to be a problem. Say,
I faced 'serial' type incompatibility moving my dumps from 7.1.3 to
7.2.2 (since they became 64-bit integers).
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Colin Stearman
(or without) blobs.
It will dump the globals also, like user names, but I don't know about
triggers, etc. As it uses pg_dump
and pg_restore to deal with the DBs and pg_dumpall to deal with the globals,
it should be all there.
You can get the scripts from www.infofind.com/postgreSQL.
Although I tested them, NO GUARANTEES OF ANY KIND ARE GIVEN. So test them
on something you don't care about first (especially the fullrestore script)!
Colin
PS. Mail list admin is welcome to add these scripts to the contribution
directory, if desired.
----- Original Message -----
From: "Dan Langille" <d...@langille.org>
To: "Colin Stearman" <cste...@infofind.com>
Cc: <pgsql...@postgresql.org>
Sent: Friday, November 22, 2002 2:46 PM
Subject: Re: [ADMIN] Database backup and restore
On 22 Nov 2002 at 14:41, Colin Stearman wrote:
> Dan,
>
> On first glance at dima's script it does not seem to deal with blobs,
> although could easily me made to do so.
I think that would be a very good addition to the PostgreSQL toolkit.
FWIW, I do not use blobs and consequently do not think of them when
backups are required.
> I'll be working on it some more and will share what I come up with.
I'm sure it will be added to the contrib directory.
--
Dan Langille : http://www.langille.org/
---------------------------(end of broadcast)---------------------------