Return-Path: postman 
Delivery-Date: Thu, 07 Oct 93 16:27:10 PDT
Return-Path: postman
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA10362; Thu, 7 Oct 93 16:20:46 -0700
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9310072320.AA10362@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
X-Return-Path: sarum@vger.demon.co.uk
Received: from gate.demon.co.uk by postgres.Berkeley.EDU (5.61/1.29)
	id AA10353; Thu, 7 Oct 93 16:20:35 -0700
Received: from vger.demon.co.uk by gate.demon.co.uk id aa19328;
          8 Oct 93 0:16 GMT-60:00
Received: by vger.demon.co.uk (4.1/SMI-4.1)
	id AA00525; Fri, 8 Oct 93 00:15:36 BST
From: David Allan Finch <sarum@vger.demon.co.uk>
Message-Id: <9310072315.AA00525@vger.demon.co.uk>
To: postgres@postgres.berkeley.edu
Subject: Dump/restore a postgres database
Date: Fri, 8 Oct 93 00:07:18 BST
Content-Transfer-Encoding: Quoted-Printable
Content-Type: text/plain; charset=ISO-8859-1
Resent-To: postgres-dist
Resent-Date: Thu, 07 Oct 93 16:20:45 PDT

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 datab=
ase.
#   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