Re: [ADMIN] Database backup and restore
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.
#!/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 );
}
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] Database backup and restore
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. Heya, Dan! 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? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Database backup and restore
I have completed the development of a BASH script to dump and load DBs with (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 [EMAIL PROTECTED] To: Colin Stearman [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[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. Thanks, Colin -Colin StearmanPresidentAshdown Technologies119 E. Main St.Milford, MA 01757 USATel: +1 508-478-1234Fax: +1 508-478-1244www.ashdowntech.com
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
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 [EMAIL PROTECTED]
Re: [ADMIN] 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:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 11:07 To: Colin Stearman Cc: [EMAIL PROTECTED] 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
Re: [ADMIN] Database backup and restore
Dan, 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 [EMAIL PROTECTED] To: dima [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
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)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
