agora inbox for postgres@postgres.berkeley.edu
help / color / mirror / Atom feedFrom: David Allan Finch <sarum@vger.demon.co.uk>
To: postgres@postgres.berkeley.edu
Subject: Dump/restore a postgres database
Date: Fri, 8 Oct 93 00:07:18 BST
Message-ID: <9310072315.AA00525@vger.demon.co.uk> (raw)
Version 0.3 of the ongoing pgperl script...
this version will dump all databases except template1 and
postgres. With a little more work it will store all the stuff
nead to recreate a full database. Tommorow I will hopeful have
a restore script. Which I will use to upgrade to V4.1, If anyone
can see any bugs please tell me. Also it would be nice to dump
indexes, function etc, anyone got any ideas about how to do this?
#!/usr/postgres/bin/pgperl
################################################################################
################################################################################
# Version 000.001 : 03/Aug/1993
# This code is non-copyright of David Allan Finch
# and does nothing. If it does then its your
# problem for running it, You have been warned.
################################################################################
# Version 000.002 : 05/Aug/1993 : 15:25 BST
# This version will look up all the classes in the
# pg_inherit and pg_class and then produce a pg-include file
# to re-create them. NOTE it will not work if
# a class has no data (don't know why, but giving
# a top level class some dummy data will fix the problem).
# Also this version assumes that a child class will
# have the same form a the parent. Also I do not
# know how to create a class for a specific user
# so this infomation will be lost.
################################################################################
# Version 000.003 : 07/Oct/1993 : 19:10 BST
# This version gets all the database names first and then does each database.
# The structure of this utility is f**kup will have to rewrite.
################################################################################
################################################################################
$version = shift || die( "Usage: $0 <version>\n" );
$PGH = "/usr/postgres";
$PGH = "/usr/postgres" if( $version eq "4.0.1" );
$PGH = "/usr/local/postgres" if( $version eq "4.1" );
if( ! -d "$PGH/dump" )
{
mkdir( "$PGH/dump", 0777 );
}
@database = &get_database_list();
foreach $d ( @database )
{
undef @inherit;
undef @class;
undef %done;
( $database ) = split( "#", $d );
next if ( ! -d "$PGH/data/base/$database" );
print "<<<<< $database >>>>>\n";
open( SCRIPT, ">$PGH/dump/SCRIPT_$database" );
# get all the system stuff
&get_types();
&get_classes();
&get_inherits();
# create the inherited classes and their parents
foreach $class ( @inherit )
{
print STDERR "do iherited and parent: $class\n";
&print_classes( split( ',', $class ) );
}
# create the rest of the classes
foreach $class ( @class )
{
next if( $class eq "" );
if( $done{$class} ne "y" )
{
print STDERR "do class: $class\n";
&print_class( $class );
$done{$class} = "y";
}
}
}
exit(0);
################################################################################
################################################################################
sub get_database_list
{
local( @LIST );
&PQsetdb( "template1" );
&PQexec( "begin" );
&PQexec( "retrieve portal tmp ( pg_database.all )" );
&PQexec( "fetch all in tmp" );
$p = &PQparray( "tmp" );
$g = &PQngroups( $p );
for( $k = 0; $k < $g; $k++ )
{
$n = &PQntuplesGroup( $p, $k );
$m = &PQnfieldsGroup( $p, $k );
for( $i = 0; $i < $n; $i++ )
{
$name = &PQgetvalue( $p, $i, 0 );
$uid = &PQgetvalue( $p, $i, 1 );
$path = &PQgetvalue( $p, $i, 2 );
next if( $name =~ /template1/ );
next if( $name =~ /postgres/ );
push( @LIST, join( "#", ( $name, $uid ) ) );
}
}
&PQexec( "close tmp" );
&PQexec( "end" );
&PQfinish();
return( @LIST );
}
################################################################################
################################################################################
sub get_inherits
{
&PQsetdb( "$database" );
&PQexec( "begin" );
&PQexec( "retrieve portal tmp ( pg_inherits.all )" );
&PQexec( "fetch all in tmp" );
$p = &PQparray( "tmp" );
$g = &PQngroups( $p );
$t = 0;
for( $k = 0; $k < $g; $k++ )
{
$n = &PQntuplesGroup( $p, $k );
$m = &PQnfieldsGroup( $p, $k );
for( $i = 0; $i < $n; $i++ )
{
$child = $class[&PQgetvalue( $p, $i, 0 )];
$parent = $class[&PQgetvalue( $p, $i, 1 )];
next if( $child eq "" );
push( @inherit, join( ',', $child, $parent ) );
}
}
&PQexec( "close tmp" );
&PQexec( "end" );
&PQfinish();
}
################################################################################
################################################################################
sub get_classes
{
&PQsetdb( "$database" );
&PQexec( "begin" );
&PQexec( "retrieve portal tmp ( pg_class.relname, pg_class.relowner, pg_class.oid ) where pg_class.relkind = 'r'" );
&PQexec( "fetch all in tmp" );
$p = &PQparray( "tmp" );
$g = &PQngroups( $p );
$t = 0;
for( $k = 0; $k < $g; $k++ )
{
$n = &PQntuplesGroup( $p, $k );
$m = &PQnfieldsGroup( $p, $k );
for( $j = 0; $j < $m; $j++ )
{
$field_name = &PQfnameGroup( $p, $k, $j );
$field_type = &PQftype( $p, $k, $j );
}
for( $i = 0; $i < $n; $i++ )
{
$name = &PQgetvalue( $p, $i, 0 );
$owner = &PQgetvalue( $p, $i, 1 );
$oid = &PQgetvalue( $p, $i, 2 );
next if( $name =~ /pg_/ );
next if( $name =~ // );
$class{$name} = $oid;
$class[$oid] = $name;
}
}
&PQexec( "close tmp" );
&PQexec( "end" );
&PQfinish();
}
################################################################################
################################################################################
sub get_types
{
&PQsetdb( "$database" );
&PQexec( "begin" );
&PQexec( "retrieve portal tmp ( pg_type.typname, pg_type.oid )" );
&PQexec( "fetch all in tmp" );
$p = &PQparray( "tmp" );
$g = &PQngroups( $p );
$t = 0;
for( $k = 0; $k < $g; $k++ )
{
$n = &PQntuplesGroup( $p, $k );
$m = &PQnfieldsGroup( $p, $k );
for( $i = 0; $i < $n; $i++ )
{
$name = &PQgetvalue( $p, $i, 0 );
$value = &PQgetvalue( $p, $i, 1 );
$types{$name} = $value;
$types[$value] = $name;
}
}
&PQexec( "close tmp" );
&PQexec( "end" );
&PQfinish();
}
################################################################################
################################################################################
sub print_classes
{
local( $class, $inherit ) = @_;
if( $done{$inherit} ne "y" )
{
&print_class( $inherit );
$done{$inherit} = "y";
}
print SCRIPT "create $class inherits $inherit ()\n";
print SCRIPT "copy $class from \"$PGH/dump/$database#$class\"\n";
&PQsetdb( "$database" );
&PQexec( "begin" );
&PQexec( "copy $class to \"$PGH/dump/$database#$class\"" );
&PQexec( "end" );
&PQfinish();
$done{$class} = "y";
}
################################################################################
################################################################################
sub print_class
{
local( $class ) = @_;
&PQsetdb( "$database" );
&PQexec( "begin" );
&PQexec( "retrieve portal tmp ( $class.all )" );
&PQexec( "fetch 1 in tmp" );
$p = &PQparray( "tmp" );
$g = &PQngroups( $p );
$t = 0;
print SCRIPT "create $class ( ";
for( $k = 0; $k < $g; $k++ )
{
$n = &PQntuplesGroup( $p, $k );
$m = &PQnfieldsGroup( $p, $k );
for( $i = 0; $i < $m; $i++ )
{
if( $i > 0 )
{
print SCRIPT ", ";
}
$field_name = &PQfnameGroup( $p, $k, $i );
$field_type = &PQftype( $p, $k, $i );
print SCRIPT "$field_name = $types[$field_type]";
}
}
print SCRIPT " )\n";
print SCRIPT "copy $class from \"$PGH/dump/$database#$class\"\n";
&PQexec( "copy $class to \"$PGH/dump/$database#$class\"" );
&PQexec( "close tmp" );
&PQexec( "end" );
&PQfinish();
}
################################################################################
################################################################################
--
/ Sarum Wizard
/\|/\ All Hail Discordia
| K | david.finch@vger.demon.co.uk
\___/ sarum@cix.compulink.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: postgres@postgres.berkeley.edu
Cc: sarum@vger.demon.co.uk
Subject: Re: Dump/restore a postgres database
In-Reply-To: <9310072315.AA00525@vger.demon.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox